5 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 re

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: https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW https://www.postgresql.org/docs/10/static/monitoring-stats.h... Also, instead of updating the `users` table with the LSN of the commit - which creates extra write load - why not store it in the session cookie, then you can route based on that. 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#SYNCHRONOUS-REPLICATION https://www.postgresql.org/docs/10/static/warm-standby.html#... Since this can be enabled/disabled for each transaction it's really powerful.

anarazel 9y ago
> 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)!
brandur 9y ago
(I wrote this.) > Also, instead of updating the `users` table with the LSN of the commit - which creates extra write load - why not store it in the session cookie, then you can route based on that. The article's a little long, but at one point I do mention that we're putting this information in Postgres in the demo for convenience, but that in a real implementation it might be worthwhile moving it elsewhere as an optimization. This addresses the first point as well: you're right in that this information can be procured from Postgres, but the point of putting in the observer is to demonstrate a system that could be implemented in a way that's agnostic of storage. It's very plausible that you might want to put `min_lsn` and replication statuses in something very fast like Redis, and even have each `api` worker caching its own version of the latter so that a replica can be selected without even making a network call.
- Cieplak 9y ago
  How do you generate your diagrams? They're really beautiful. Zooming in on the svg I see that it's all just unicode, but was wondering if you're manually typesetting it.
  - lfittl 9y ago
    AFAIK its Monodraw: https://monodraw.helftone.com/ https://monodraw.helftone.com/ (I've asked Brandur this in the past, so assuming he hasn't changed it this should still be what he uses)
    - CaliforniaKarl 9y ago
      Oh, wow. My internal ikiwiki pages are filled with ASCII art diagrams. This tool looks awesome; I’ll have to try it out!
    - goliatone 9y ago
      There was another article from Brandur recently about redis streams- which was great. The diagrams really called my attention but I shied away from asking the source since it felt like a bit disrespectful. Thanks for the link.

anarazel 9y ago

> 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)!

brandur 9y ago

(I wrote this.) > Also, instead of updating the `users` table with the LSN of the commit - which creates extra write load - why not store it in the session cookie, then you can route based on that. The article's a little long, but at one point I do mention that we're putting this information in Postgres in the demo for convenience, but that in a real implementation it might be worthwhile moving it elsewhere as an optimization. This addresses the first point as well: you're right in that this information can be procured from Postgres, but the point of putting in the observer is to demonstrate a system that could be implemented in a way that's agnostic of storage. It's very plausible that you might want to put `min_lsn` and replication statuses in something very fast like Redis, and even have each `api` worker caching its own version of the latter so that a replica can be selected without even making a network call.

Cieplak 9y ago
How do you generate your diagrams? They're really beautiful. Zooming in on the svg I see that it's all just unicode, but was wondering if you're manually typesetting it.
- lfittl 9y ago
  AFAIK its Monodraw: https://monodraw.helftone.com/ https://monodraw.helftone.com/ (I've asked Brandur this in the past, so assuming he hasn't changed it this should still be what he uses)
  - CaliforniaKarl 9y ago
    Oh, wow. My internal ikiwiki pages are filled with ASCII art diagrams. This tool looks awesome; I’ll have to try it out!
  - goliatone 9y ago
    There was another article from Brandur recently about redis streams- which was great. The diagrams really called my attention but I shied away from asking the source since it felt like a bit disrespectful. Thanks for the link.