Postgres replication slots expose two LSN-related attributes: confirmed_flush_lsn and restart_lsn. The confirmed_flush_lsn tracks the latest position acknowledged by a consumer, while restart_lsn marks the oldest WAL position still needed due to concurrent in-flight transactions. Because transactions are published in commit order, a transaction that started earlier but commits later forces the slot to retain WAL back to its start point. This can cause excessive WAL retention with large or long-running transactions. A special edge case occurs when confirmed_flush_lsn points mid-transaction: on recovery, the entire transaction is replayed from BEGIN. Best practice is to confirm commit LSNs rather than individual event LSNs. Postgres 14+ supports streaming in-progress transactions as an alternative approach, though it adds complexity to CDC tools like Debezium.
Sort: