Missing from this article but worth considering: if your read-replicas have any lag at all (very likely) how can you ensure users see their updated data after they perform a write?
On a site like Lobste.rs for example it’s a nasty bug if a user posts or edits a comment and then can’t see their update on the subsequent page, because it was served from a read-replica that didn’t have the change yet.
The simplest solution I’ve seen for this problem involves cookies or sessions. Any time a user performs a write action (posting a comment for example) you set a cookie with a very short expiry - maybe 5 seconds or so.
Any user with that cookie is “pinned” to the read-write connection, which guarantees they will see the results of their write unaffected by replication lag - provided that lag stays below the 5s value you picked!
There’s a slightly more complex version of this which I’ve seen implemented for Wikipedia: maintain a global transaction counter that increments with every transaction. When a user performs a write, record that counter to their cookies or session. On subsequent reads, check the value of that counter in the read replicas and only serve that user from a replica that has caught up with that point in time - or route to the lead if no replica is there yet.
That second approach sounds like a bespoke vector clock, enforcing a sort of causal consistency. Some database products have this concept built into their clustering approach. Each write operation returns the vector clock value so long as you pass it back into the next query the cluster will re-route the query or delay execution until the replica catches up. I’d like to see broader support for this built directly into Postgres libraries.
I did this in perl in the bad old days. It was surprisingly resilient and improved performance enough that the janky implementation I hacked up wasn’t worth fixing for a much longer time than I expected.
Missing from this article but worth considering: if your read-replicas have any lag at all (very likely) how can you ensure users see their updated data after they perform a write?
On a site like Lobste.rs for example it’s a nasty bug if a user posts or edits a comment and then can’t see their update on the subsequent page, because it was served from a read-replica that didn’t have the change yet.
The simplest solution I’ve seen for this problem involves cookies or sessions. Any time a user performs a write action (posting a comment for example) you set a cookie with a very short expiry - maybe 5 seconds or so.
Any user with that cookie is “pinned” to the read-write connection, which guarantees they will see the results of their write unaffected by replication lag - provided that lag stays below the 5s value you picked!
There’s a slightly more complex version of this which I’ve seen implemented for Wikipedia: maintain a global transaction counter that increments with every transaction. When a user performs a write, record that counter to their cookies or session. On subsequent reads, check the value of that counter in the read replicas and only serve that user from a replica that has caught up with that point in time - or route to the lead if no replica is there yet.
That second approach sounds like a bespoke vector clock, enforcing a sort of causal consistency. Some database products have this concept built into their clustering approach. Each write operation returns the vector clock value so long as you pass it back into the next query the cluster will re-route the query or delay execution until the replica catches up. I’d like to see broader support for this built directly into Postgres libraries.
For context, we are using AlloyDB and lag is non-issue. That being said, I realize how this might be an issue for others.
I did this in perl in the bad old days. It was surprisingly resilient and improved performance enough that the janky implementation I hacked up wasn’t worth fixing for a much longer time than I expected.
Option 2 kind of all gets blown out of the water once you try and do anything real (with transactions) huh?
If by blow-up you mean fall back to using the main instance, then yes.
But that is perfectly reasonable. Most of your average application load is going to be from non transactional queries.