Bertrand Drouvot: Postgres 16 highlight: Logical decoding on standby (PGConf.EU 2023)

Discover the benefits and challenges of logical decoding on standby in Postgres 16, including handling snapshot conflicts, catalog tables, and more, and learn how it can improve failover and replica usage.

Key takeaways
  • Logical decoding on standby is now possible in Postgres 16, but it’s not without challenges.
  • When creating a logical replication slot on a standby, it’s waiting for a specific type of world record from the primary.
  • A special function is needed to handle this distinction between logical and physical world centers.
  • The main challenges faced were dealing with snapshot conflict origin, catalog tables, and logical decoding on standby.
  • Logical decoding on standby can help with failover and allows standby to be used as a replica even when primary is down.
  • Adding a new flag to WAL records allows for better handling of catalog tables during logical decoding.
  • The process of synchronizing logical application slots between primary and standby is still being refined.
  • Testing was done to validate performance and functionality, but more testing is needed for edge cases.
  • Logical decoding on standby can be used with physical application slots, but requires manual intervention to handle conflicts.
  • Catalog tables can be handled during logical decoding by checking if the relation is a catalog or non-catalog.
  • Handling promotion of standby to primary can be tricky and requires careful planning to ensure minimal downtime.
  • There are still some open questions about storing reasons for slot conflicts in the catalog and how to handle logical decoding on standby with multiple layers of replication.