[simonw]

There's another, more sophisticated trick that works for some databases: tracking a global transaction counter of some sort, persisting that in a cookie when a user makes a write and redirecting the user to the lead database if the replica they are talking to hasn't made it to that point yet.

Chris McCord describes how Elixir does that with PostgreSQL here: https://news.ycombinator.com/item?id=31434094

Wikipedia implements this trick on top of PHP and MySQL global transaction IDs (GTIDs) so it definitely scales!

[simonw]

Actually the way Wikipedia works is slightly different: they don't redirect to a lead database, they instead call this MySQL function to wait on the replica for it to catch up:

    SELECT WAIT_FOR_EXECUTED_GTID_SET($gtidArg, $timeout)

https://github.com/wikimedia/mediawiki/blob/434c333d9b2be817...

I wonder if there's a PostgreSQL equivalent of this?

[simonw]

Looks like someone proposed a WAIT FOR feature for PostgreSQL a couple of years ago: https://www.postgresql.org/message-id/flat/69a363498b76cd079...

[randito]

(Disclaimer: Not an expert.. just sharing something I read somewhere)

I think FoundationDB does something really interesting with this problem. When you make changes, you do it via a transaction. But all the client reads are using the previous version, until the transaction changes have propagated across the nodes, then the new value is returned.