Ask HN: How to distribute/sync a postgresdb geographically?

12 points by Jonnax 6y ago ↗ HN

So I'm wondering, if I create a Django application that uses a Postgres DB, and I want to be able to deploy it in multiple locations to reduce latency.

How would I ensure that an update in user data against one dB is synced with the other DBs.

Assuming there are DBs in America, Asia and Europe?

Are there any solutions to do syncing on the database level?

Does Django provide any of this functionality?

Should the problem be approached from the database level or the application level?

6 comments

[ 2.8 ms ] story [ 20.7 ms ] thread

nuggien 6y ago ↗

I don't know of any good native postgres solutions, but you could try https://www.cockroachlabs.com/ which may or may not be a drop in replacement for postgres, depending on your use cases.

Jonnax 6y ago ↗

Interesting, I've heard of the DB before but haven't given it much research. I'll check it out.

ddorian43 6y ago ↗

Also https://www.yugabyte.com/ which uses Postgresql code and apache2 license.

ThePhysicist 6y ago ↗

If you’re fine with a single master that you write to and geographically distributed slaves for reading it’s not so difficult, you can use WAL streaming or log shipping to keep your slaves in sync with your master. You can use some additional tooling (e.g. haproxy and heartbeat) to do an automated failover in case your master goes down. Building a fully distributed multi-master setup is not possible with native Postgres as far as I know, there are commercial offerings like CitusDB that might help you with that though.

Jonnax 6y ago ↗

Hmm, well the scenario I was thinking of was having database writes sped up, in the sense of reducing network latency between the user and the server.

I guess the syncing aspect is going to be most complicated part.

I was thinking that I could have some logic that stores where the user's nearest DB is as part of their user info, and just store their data there. But still not sure if that's the best way.

Thanks for mentioning Citrus DB, I looks like they have an open source version that I'll try checking out.

atsaloli 6y ago ↗

This is called master-master replication or multi-master replication. Postgres has been working toward it for years, and making progress. Not an easy problem to solve and get right. Postgres is not there yet but closer than before.