Overcoming Common Pitfalls of Postgres Logical Decoding
Presented by:
Sai Srirampur
I was the CEO and co-founder of PeerDB, where we focused on building the world's fastest replication tool for Postgres. Recently, ClickHouse acquired PeerDB to offer a native Postgres CDC integration within ClickHouse. I now work on the product team at ClickHouse, leading all things Postgres, including Postgres CDC integration.
Before PeerDB, I was an early employee at Citus Data and saw it through the Microsoft acquisition. For the past decade, I have been an active member of the Postgres community, helping customers implement Postgres, Citus and PeerDB.
No video of the event yet, sorry!
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