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.