0

I'm trying to learn about the different types of database architectures and how their differences influence which one or combination of should be used in software system.

I understand that OLTPs typically consist of row-oriented DBs due to their faster write speed and OLAPs typically consist of column-oriented DBs as certain types of queries such as aggregation are faster.

In smaller systems, a row-oriented relational DB such as MySQL or PostgreSQL is usually enough for both transaction processing and querying. From my understanding, as systems start to scale up, it is important to start considering more complex DB architecture such as replication principles. Possibly, it might be wise to have a MySQL (Master) for transaction processing, and have this data extracted to several read replicas (Slaves), which may not necessarily be MySQL or row-oriented, could possibly be NoSQL or column-oriented.

These principles make sense, but how would one actually go about choosing how to implement this system?

How would you choose which/what type of DBs should comprise of your OLAP/read systems? I'm assuming for most cases, column-oriented would be the natural choice. At what point do document-oriented databases become a consideration? How do you decide between using relational DBs or non-relational DBs?

How difficult would it be to, say, do your transaction processing to a MySQL DB, and then replicate that data for querying to read replicas? Would there need to be application code written to support this?

I would like to learn as much as possible, so I wouldn't be afraid (and would actually appreciate) if your answers are long/complex.

Improve this question

1 Answer 1

Reset to default
3

how would one actually go about choosing how to implement this system?

Not upfront!

In my experience, the most successful software engineering strategy is to start with a first version with as few technologies as possible (for example: just one relational DB), then collect data by profiling how this scales for some typical use cases, and when that really is not sufficient, you try out and evaluate additional technologies.

Yes, it might become necessary in this process to throw away some work in between, that's why it is important to identify some problematic use cases first and do the research with them. Once you collected enough experience, you can settle your technology decisions and scale-out to more use cases.

Improve this answer
5
  • Thank you Doc Brown! I imagine designing software systems would require a lot of planning a "fleshing out" of the different nuances of different DBs. Would you mind sharing any past experiences you've had with implementing the DB component of software architectures, what decisions you had to make, and how you came to said decisions?
    – bananastonk
    Mar 28 at 13:47
  • 2
    @bananastonk: to my own experience, picking a DBMS was rarely independent from the organizational environment where I worked. For large scale systems. often the decision was heavily influenced by the target OS, license costs, vendor preference ("Oracle vs Microsoft vs. Open Source"), available knowledge, and risk mitigation for securing invest. And I was rarely free to make my own choice, often we inherited legacy systems. Still my answer about picking an OLAP system architecture stands: when you are free to pick your technology, ...
    – Doc Brown
    Mar 28 at 15:52
  • .. start with the OLTP technology you are using now, see how far it brings you and when you can proof by example that you will hit the limits soon, then start introducing other technologies. This may become a complex decision, and several technologies can be used, there is no easy checklist for this - hence my best recommendation is to try something out and collect information what works in your particular case, and what not.
    – Doc Brown
    Mar 28 at 15:58
  • 1
    ... Interestingly, I found this article right after I wrote the comment above.
    – Doc Brown
    Mar 28 at 16:04
  • Thanks very much Doc Brown! I appreciate the time and effort you put into sharing your knowledge & experiences.
    – bananastonk
    Mar 29 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.