4 ms·

There’s a huge footgun with replication that the article didn’t mention, and it’s the reason why I don’t use it. Postgres is VERY committed to making sure a re

There’s a huge footgun with replication that the article didn’t mention, and it’s the reason why I don’t use it. Postgres is VERY committed to making sure a replication slot’s consumer doesn’t miss any data. This means that if a consumer stops consuming data from the slot, Postgres will helpfully store all that missed data… right up until the disk fills up and the database falls over. Had this happen during prototyping with two different SaaS DBs, and the only way to get it back up was to file a support ticket. (I can’t remember if metrics warned that the disk was about to fill up or not). Basically, if your replication slot consumer stops reading, that should trigger some kind of alert. The other reason I don’t use it: the code path to get the initial snapshot of a table is totally different from the code path to read changes. Initializing the read from the replication slot so you never miss any changes is nontrivial. It’s too bad, because replication is obviously the least hacky solution for change capture. I use polling, but storing the txid instead of updated_at.

_acco 3y ago
I've run into this footgun before! It's very subtle – you de-provision a consumer, and that feels like it should have no impact on the primary. But it creates a ticking time bomb. Mind expanding on how you use txid instead of updated_at?
- physicles 3y ago
  See my other comment over here: https://news.ycombinator.com/item?id=37610899#37619993 https://news.ycombinator.com/item?id=37610899#37619993
klysm 3y ago
One trick to deal with the first problem is sending logical decoding messages to yourself. That keeps the retained WAL low. Another potentially useful thing is temporary replication slots which clean themselves up on connection loss. I use those when I don’t need all of the changes. There is also configuration for setting the maximum WAL that’s retained so you don’t murder the server.
anarazel 3y ago
> Postgres is VERY committed to making sure a replication slot’s consumer doesn’t miss any data. This means that if a consumer stops consuming data from the slot, Postgres will helpfully store all that missed data… right up until the disk fills up and the database falls over. You can configure a size limit after which the slot gets marked as invalid, instead of continuing to retain space. See https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE https://www.postgresql.org/docs/current/runtime-config-repli... What other behaviour would you like? > The other reason I don’t use it: the code path to get the initial snapshot of a table is totally different from the code path to read changes. Hm. For anything dealing with larger data volumes you IME want to handle those things differently (so you can initialize in parallel, initialize from physical backups and similar things). But I can see why it could be useful to optionally stream out existing data out data after slot creation. > Initializing the read from the replication slot so you never miss any changes is nontrivial. That part shouldn't be hard - what gave you difficulty?

_acco 3y ago

I've run into this footgun before! It's very subtle – you de-provision a consumer, and that feels like it should have no impact on the primary. But it creates a ticking time bomb. Mind expanding on how you use txid instead of updated_at?

physicles 3y ago
See my other comment over here: https://news.ycombinator.com/item?id=37610899#37619993 https://news.ycombinator.com/item?id=37610899#37619993

klysm 3y ago

One trick to deal with the first problem is sending logical decoding messages to yourself. That keeps the retained WAL low. Another potentially useful thing is temporary replication slots which clean themselves up on connection loss. I use those when I don’t need all of the changes. There is also configuration for setting the maximum WAL that’s retained so you don’t murder the server.

anarazel 3y ago

> Postgres is VERY committed to making sure a replication slot’s consumer doesn’t miss any data. This means that if a consumer stops consuming data from the slot, Postgres will helpfully store all that missed data… right up until the disk fills up and the database falls over. You can configure a size limit after which the slot gets marked as invalid, instead of continuing to retain space. See https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE https://www.postgresql.org/docs/current/runtime-config-repli... What other behaviour would you like? > The other reason I don’t use it: the code path to get the initial snapshot of a table is totally different from the code path to read changes. Hm. For anything dealing with larger data volumes you IME want to handle those things differently (so you can initialize in parallel, initialize from physical backups and similar things). But I can see why it could be useful to optionally stream out existing data out data after slot creation. > Initializing the read from the replication slot so you never miss any changes is nontrivial. That part shouldn't be hard - what gave you difficulty?