Explore ways to always read from the slot

[iskakaushik]

Anecdotally what we have noticed is that restarting START_REPLICATION for each SyncFlow or even having multiple sync flows isn't ideal. We should always be reading from the slot if at all possible. There are a couple of approaches that come to my mind:

Option 1: ACK channel

One of the main reasons we have multiple sync flows is that after each syncflow we update the metadata on the destination marking the last committed source LSN, this makes it durable for us to restart sync flows from this point, and to them flush this via standby message. We do this once per batch.

One alternative approach could be to:

1. once the destination gets max_batch_size number of rows, we update the metadata.
2. send the committed lsn to the PullRecords via a channel, and issue a flush.
3. we just have 1 sync flow this way.

Option 2: Landing Zone

This is a more drastic shift in how we currently do things. Have some kind of a durable store (maybe kafka or redpanda). Always read from the slot and push it to this landing zone. This way the slot is always being read from. Periodically push from the landing zone to the destination. This idea needs to be fleshed out in more detail.

@serprex for thoughts.

cc: @saisrirampur

[saisrirampur]

Just make sure that START_REPLICATION doesn't incur a long running transaction and blocks autovaccum. I am not sure if START_REPLICATION is like a regular postgres transaction.

@serprex @iskakaushik

[serprex]

#1211

https://www.youtube.com/watch?v=8xIXmlwLs3g