How would I implement separate databases for reading and writing operations?

Question

I am interested in implementing an architecture that has two databases one for read operations and the other for writes. I have never implemented something like this and have always built single database, highly normalised systems so I am not quite sure where to begin. I have a few parts to this question.

1. What would be a good resource to find out more about this architecture?
2. Is it just a question of replicating between two identical schemas, or would your schemas differ depending on the operations, would normalisation vary too?
3. How do you insure that data written to one database is immediately available for reading from the second?

Any further help, tips, resources would be appreciated. Thanks.

EDIT
After some research I have found this article which I found very informative for those interested..

http://www.codefutures.com/database-sharding/

I found this highscalability article very informative

Answer 1

In regards to questions 2:

It really depends on what you are trying to achieve by having two databases. If it is for performance reasons (which i suspect it may be) i would suggest you look into denormalizing the read-only database as needed for performance. If performance isn't an issue then I wouldn't mess with the read-only schema.

I've worked on similar systems where there would be a read/write database that was only lightly used by administrative users. That database would then be replicated to the read only database during a nightly process.

Question 3: How immediate are we talking here? Less than a second? 10 seconds? Minutes?

Answer 2

I'm not a specialist but the read/write master database and read-only slaves pattern is a "common" pattern, especially for big applications doing mostly read accesses or data warehouses:

• it allows to scale (you add more read-only slaves if required)
• it allows to tune the databases differently (for either efficient reads or efficient writes)

What would be a good resource to find out more about this architecture?

There are good resources available on the Internet. For example:

• Highscalability.com has good examples (e.g. Wikimedia architecture, the master-slave category,...)
• Handling Data in Mega Scale Systems (starting from slide 29)
• MySQL Scale-Out approach for better performance and scalability as a key factor for Wikipedia’s growth
• Chapter 24. High Availability and Load Balancing in PostgreSQL documentation
• Chapter 16. Replication in MySQL documentation
• http://www.google.com/search?q=read%2Fwrite+master+database+and+read-only+slaves

Is it just a question of replicating between two identical schemas, or would your schemas differ depending on the operations, would normalisation vary too?

I'm not sure - I'm eager to read answers from experts - but I think the schemas are identical in traditional replication scenari (the tuning may be different though). Maybe people are doing more exotic things but I wonder if they rely on database replication in that case, it sounds more like "real-time ETL".

How do you insure that data written to one database is immediately available for reading from the second?

I guess you would need synchronous replication for that (which is of course slower than asynchronous). While some databases do support this mode, not all do AFAIK. But have a look at this answer or this one for SQL Server.

Answer 3

You might look up data warehouses. These serve as 'normalized for reporting' type databases, while you can keep a normalized OLTP style instance for the data maintenance.

I don't think the idea of 'immediate' equivalence will be a reality. There will be some delay while the new data and changes are migrated in to the other system. The schedule and scope will be your big decisions here.