Logical Replication

ELI5 - The Vibe Check

Physical replication copies a database byte for byte, same version, same everything. Logical replication is smarter and messier: it replicates actual row changes (insert this, update that) instead of raw bytes, which means you can replicate between different Postgres versions, replicate just one table, or feed those changes into something that isn't even Postgres at all.

Real Talk

Logical replication streams row-level data changes (inserts, updates, deletes) decoded from the write-ahead log, rather than copying physical disk blocks. Unlike physical replication, it allows replicating a subset of tables and replicating across major database versions, and it's the mechanism tools like Debezium and Postgres's built-in publication and subscription model use to feed changes into other systems.

Show Me The Code

-- Postgres logical replication
CREATE PUBLICATION my_pub FOR TABLE orders;
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=other-host dbname=app'
  PUBLICATION my_pub;

When You'll Hear This

"We use logical replication to stream just the orders table into the analytics database." / "Logical replication let us upgrade Postgres versions with near-zero downtime."