4 ms·

> Instead of having an "observer" process that updates a table with the LSN you can just ask Postgres directly: The `pg_stat_replication` view has the last LSN

> Instead of having an "observer" process that updates a table with the LSN you can just ask Postgres directly: The `pg_stat_replication` view has the last LSN replayed on each replica available: Note that that'll lag behind reality a bit, we don't continually send the feedback messages that contain the replay progress. > Another option is to enable synchronous replication for transactions that need to be visible on all replicas: https://www.postgresql.org/docs/10/static/warm-standby.html#.. https://www.postgresql.org/docs/10/static/warm-standby.html#.... Note that's only behaving correctly if synchronous_commit is set to remote_apply, which has been added in 9.6. Before that syncrep could only guarantee that the remote side has safely received the necessary WAL, not that it has been applied.

MartinMond 9y ago
Thanks! What's your recommendation how to solve the problem described in the article? Would you compare LSNs of replicas in application logic at all?
- anarazel 9y ago
  I've only skimmed it so far, so I might be missing parts. I think it depends a lot on what you're trying to achieve. In a lot of cases all the guarantee you need is read-your-own-write, and small delays for an individual connection aren't that bad. In that case it can be a reasonable approach to inquire the LSN of the commit you just made, and just have your reads, to whichever replica they go, wait for that LSN to be applied. For a system that's very latency sensitive that'd not work, and you'd fall back to querying the master pretty much immediately. Or you'd use something like described in the article, although I'd probably implement it somewhat differently.
macdice 9y ago
With synchronous_commit = remote apply there are complications though. You either have to make it wait for ALL your standbys (and then deal with the fact that a failing standby can hold up commits indefinitely), or you can have it wait for N of M standbys to apply, but then how can a reader know which standby nodes have applied the commit they're interested in? I have proposed synchronous_replay = on to solve these problems. It's building on the same technology: the remote_apply patch was a stepping stone, and replay_lag was another patch in that series. The third patch add read leases so you can fail gracefully while retaining certainty for readers about which nodes have fresh data. See https://commitfest.postgresql.org/15/951/ https://commitfest.postgresql.org/15/951/ . There is also a proposal to add a 'wait for LSN' command, which the author of this article should check out. See https://commitfest.postgresql.org/15/772/ https://commitfest.postgresql.org/15/772/ . The two proposals achieve some form of causal consistency from different ends: one makes writers wait but doesn't require extra communication of LSN inside (and possibly between) client apps, and the other makes readers wait but requires more work from clients. I hope we can get both of these things in (in some form)!

MartinMond 9y ago

Thanks! What's your recommendation how to solve the problem described in the article? Would you compare LSNs of replicas in application logic at all?

anarazel 9y ago
I've only skimmed it so far, so I might be missing parts. I think it depends a lot on what you're trying to achieve. In a lot of cases all the guarantee you need is read-your-own-write, and small delays for an individual connection aren't that bad. In that case it can be a reasonable approach to inquire the LSN of the commit you just made, and just have your reads, to whichever replica they go, wait for that LSN to be applied. For a system that's very latency sensitive that'd not work, and you'd fall back to querying the master pretty much immediately. Or you'd use something like described in the article, although I'd probably implement it somewhat differently.

macdice 9y ago

With synchronous_commit = remote apply there are complications though. You either have to make it wait for ALL your standbys (and then deal with the fact that a failing standby can hold up commits indefinitely), or you can have it wait for N of M standbys to apply, but then how can a reader know which standby nodes have applied the commit they're interested in? I have proposed synchronous_replay = on to solve these problems. It's building on the same technology: the remote_apply patch was a stepping stone, and replay_lag was another patch in that series. The third patch add read leases so you can fail gracefully while retaining certainty for readers about which nodes have fresh data. See https://commitfest.postgresql.org/15/951/ https://commitfest.postgresql.org/15/951/ . There is also a proposal to add a 'wait for LSN' command, which the author of this article should check out. See https://commitfest.postgresql.org/15/772/ https://commitfest.postgresql.org/15/772/ . The two proposals achieve some form of causal consistency from different ends: one makes writers wait but doesn't require extra communication of LSN inside (and possibly between) client apps, and the other makes readers wait but requires more work from clients. I hope we can get both of these things in (in some form)!