Hazards of logical decoding in PostgreSQL

Transcript

1. Hazards of logical decoding Polina Bungina, PGConf.EU 2025
2. Agenda 1. Intro a. CDC, approaches for PostgreSQL 2. Logical decoding - concepts, hazards and examples a. Decoding process b. Logical replication slot c. Replication protocol 2
3. • Set of design patterns used to track when and what changes occur in data then notify other systems and services that must respond to those changes. • Maintain consistency across all systems that rely on data Intro CDC - Change Data Capture 3
4. • Queries on Timestamp Column • Triggers • Logical decoding Intro CDC in PostgreSQL 4
5. Process of extracting all persistent changes to a database tables into a coherent, easy to understand format which can be interpreted without detailed knowledge of the database’s internal state. Intro Logical decoding 5
6. • The technology is complex and can be dangerous • Users need to understand the implementation details Intro IMHO 6
7. Logical replication slot Decoding process Replication protocol Logical decoding 7
8. 8 Logical replication slot • Slot – state of a consumer • LSN (log sequence number) and xmin positions define all the resources required • Resource is not removed if needed by at least one consumer • WAL • System catalog rows • Crash-safe - state is persisted to disk • independently of the connection using it
9. 9 Logical replication slot pg_replication_slots
10. 10 Logical replication slot
11. 11 Logical replication slot – the hazard pg_wal size
12. ➜ Slot should be deleted once not needed! Logical replication slot – examples Unused slots 12
13. • do not produce writes for my publication ➜ single WAL for the whole cluster, not per-database/publication • do not produce write activity in the db ➜ scheduled vacuum/reindex/… ? ➜ autovacuum ➜ WAL segment switches (archive_timeout) on any database activity Segment file size is always the same (16MB by default) Logical replication slot – examples But wait, I 13
14. • Failing/not performant consumer => slot is moved slower than WAL is generated => WAL size grows • Make use of publications (filtering): • FOR TABLE / FOR TABLES IN SCHEMA • with (publish = 'insert, update, delete, truncate') ➜ unrelated events are not sent to the consumer Logical replication slot – examples Consumer performance 14
15. • [PG17+] • sync_replication_slots GUC (per standby) • pg_create_logical_replication_slot(name, plugin, .., failover) CREATE_REPLICATION_SLOT slot_name ( FAILOVER[, ...] ) ] • synchronized_standby_slots GUC • slot on standby might be not ready/invalidated • < PG17 • Patroni logical slots failover (pg_replication_slot_advance()) • pg_failover_slots extension • File system replication Logical replication slot Failover 15
16. Logical replication slot Decoding process Replication protocol Logical decoding 16
17. • Single WAL for the whole cluster, not per-database/table • Decoded transactions are sent on commit • [PG14+] streaming of in-progress transactions • Requires client-side implementation Decoding process 17
18. Decoding process 18
19. Decoding process 19
20. Decoding process 20
21. Decoding process 21
22. 22 Decoding process pg_stat_replication_slots [PG14+] docs
23. pg_repack 23 Decoding process – the hazard
24. Decoding process – the hazard 24
25. 15:45 ~/pgdata/pgroot/data$ du -sh pg_replslot/* 70GB pg_replslot/my_slot 15:53 ~/pgdata/pgroot/data$ du -sh pg_replslot/* 152GB pg_replslot/my_slot 16:03 ~/pgdata/pgroot/data$ du -sh pg_replslot/* 185GB pg_replslot/my_slot 16:09 ~/pgdata/pgroot/data$ du -sh pg_replslot/* 8.0K pg_replslot/my_slot 25 Decoding process – example 12:19 - start pg_repack 13:20 - copy to temp table finished 13:48 - pg_repack cancelled 16:08 - delete replication slot
26. Decoding process performance 26 Decoding process – the hazard
27. Decoding process Logical replication slot Replication protocol Logical decoding 27
28. 28 Replication protocol
29. 29 Replication protocol
30. 30 Replication protocol PgJDBC oversight
31. 31 Replication protocol PgJDBC oversight
32. ➜ Slot is not advanced unless there is a write produced for the respective publication ➜ Problem of endless shutdown • a clean shutdown == all changes are received by consumers • Imporved in pgjdbc v42.7.0 Replication protocol – example 32
33. • pgjdbc is used by Debezium • Improved in pgjdbc v42.7.0, is disabled in Debezium 3.2.0 • heartbeat.action.query ¯\_(ツ)_/¯ 33 Replication protocol – example
34. Conclusion • Use cautiously! Beware of the hazards • Monitor: • Slot is active and advancing • Free disk space • Delete unused slots • Use the proper protocol implementation • Know your workload, evaluate other approaches 34
35. Alternative: pgq 35
36. 36 Alternative - pgq
37. Alternative: pgq 37 Alternative - pgq
38. Alternative: pgq + No maintenance overhead + Minimal bloat + Equal to logical decoding (e.g., outbox table) - Not modern, not fancy - No good documentation - Might stop working one day 38
39. 39 Thank you!