How can I seperate reads and writes with a multi-db setup with django & postgresql?

Question

as some of you are aware Django has multi-db support. This can be achieved by writing a dbrouter to send writes to the master database and all the reads to the slave, but as stated on the Django Docs For Master/Slave Configuration

The master/slave configuration described is also flawed – it doesn’t provide any solution for handling replication lag (i.e., query inconsistencies introduced because of the time taken for a write to propagate to the slaves). It also doesn’t consider the interaction of transactions with the database utilization strategy.

How can I account for replication lag and the query inconsistencies due to the time it takes for the write to propagate to the slaves? Is there any sort of code I can implement for this?

@karthikr Hey, I'm just trying to make all the writes go the Master and all the reads to the slave. By doing this, I lighten the load because instead of having one database doing all the reads and writes, I have two databases that are identical sharing the work allowing everything to run smoothly and efficiently:) — deadlock
– deadlock, Commented May 24, 2013 at 15:33

perrygeo · Accepted Answer · 2013-05-24 19:54:00Z

4

If you're writing to MASTER and immediately reading from SLAVE, you will always run the risk of inconsistencies. You can mitigate the risk but you can never avoid it.

Doing everything possible to profile, tune and minimize the replication lag will help. Delaying the read query until the last possible moment will help. But it can't be avoided entirely if you're intent on never reading from MASTER.

If you time the replication lag under typical usage, you could configure something like django-multidb-router to read from MASTER for a specified period of time after write. Still is not 100% safe but you can configure it to be 99.9% safe for your setup.

answered May 24, 2013 at 19:54

perrygeo

3851 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

deadlock Over a year ago

Thank you perrygeo, this is exactly what I was looking for, at least this way, I have a safeguard if the user decides to quickly check the recently posted item. Your help is much appreciated!

Steven · Accepted Answer · 2014-08-06 07:06:33Z

1

Note that in MySQL 5.6 they have introduced a "Semi-Sync" mode (http://dev.mysql.com/doc/refman/5.6/en/replication-semisync.html), which guarantees a synchronous slave when used in a simple Master-slave (and only one slave) setup. This avoids that very specific problem, but what you gain in consistency you lose in some added transaction time.

answered Aug 6, 2014 at 7:06

Steven

111 bronze badge

Collectives™ on Stack Overflow

How can I seperate reads and writes with a multi-db setup with django & postgresql?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related