Overcoming Common Pitfalls of Postgres Logical Decoding
Presented by:

Sai Srirampur
Sai leads all the Postgres and Database integration efforts at ClickHouse. He was the CEO and Co-founder of PeerDB, which he sold to ClickHouse. Sai is a Postgres enthusiast who's helped hundreds of companies get the most out of their databases. He built Postgres tools at Microsoft and was an early Citus Data employee (acquired by Microsoft).
PeerDB provides a fast and simple way to replicate data from Postgres to ClickHouse. We implement Postgres Change Data Capture (CDC) to reliably replicate changes from Postgres to ClickHouse. Postgres Logical Decoding is a building block of Postgres CDC. It enables users to stream changes on Postgres as a sequence of logical operations like INSERTs, UPDATEs, and DELETEs. Logical Decoding has evolved quite a bit in the past few years in Postgres. However, there are a few quirks that users need to overcome. In this talk, we will summarize common issues and learnings from many customers replicating more than a petabyte with logical decoding.
Beware of replication slot growth – how to monitor it?
Tips for keeping replication slot growth in check
- Always consume the replication slot.
- Beware of long-running transactions; they can lead to replication slot growth.
- Use logical replication protocols for better decoding throughput and reduced slot growth.
- No activity can lead to replication slot growth.
- Tuning Postgres configs such as max_slot_wal_keep_size and logical_decoding_work_mem.
Logical decoding doesn’t capture DDL changes. How to workaround through Relation messages?
TOAST columns need REPLICA IDENTITY FULL.
- Understanding REPLICA IDENTITY FULL: when to use it and when not to.
Logical replication doesn’t support generated columns: how to work around it?
Logical Replication Slots Don't Persist on Postgres Upgrades until Postgres 17.
- Date:
- 2024 November 7 14:30 PST
- Duration:
- 20 min
- Room:
- Dev: 422
- Conference:
- Seattle 2024
- Language:
- Track:
- Dev
- Difficulty:
- Medium