We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design Masterclass · Data Replication Distribution
Free preview: the first 2 parts of this 7-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Data Replication Distribution
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
You are running Postgres 14 in production. Postgres 16 is out with features you desperately want: logical decoding improvements, better query planning, new JSON operators. But your database has 2TB of data and serves 10,000 requests per second. You cannot afford downtime.
Physical (which we cover next) will not help. It requires matching major versions. You cannot stream the WAL from a Postgres 14 primary to a Postgres 16 replica. Different binary formats.
Logical replication solves this. Instead of copying raw bytes (physical), it copies the meaning of the changes. "INSERT this row into the users table" or "UPDATE the email column where id = 42." The replica interprets these logical instructions and applies them, regardless of its internal storage format or even its major version.
This is how companies do zero-downtime major version upgrades. Set up a Postgres 16 replica, enable logical replication from your Postgres 14 primary, wait for it to catch up, switch traffic. Zero downtime. Shopify migrated their entire MySQL fleet across major versions using the equivalent approach (binlog-based logical replication). GitLab documented their Postgres upgrade from 12 to 14 using logical replication with less than 1 minute of read-only mode for cutover.
Physical sends raw disk blocks, the actual bytes on disk. It is a bit-for-bit copy. Fast, efficient, but inflexible. Both sides must run the same database version, same platform, same architecture.
Logical replication sends decoded change events, -level operations. "A row was inserted into table X with these column values." The receiving side interprets these events and applies them however it wants.
| Characteristic | Physical | Logical |
|---|---|---|
| What is sent | Raw WAL bytes (disk blocks) | Decoded row-level changes |
| Version compatibility | Must match major version | Can differ across versions |
| Table selection | All or nothing (entire database) | Per-table, per-schema filtering |
| Cross-platform | Must match OS/architecture | Platform-independent |
| Schema changes (DDL) | Replicated automatically | NOT replicated (must apply manually) |
| Performance overhead | Lower (raw bytes) | Higher (must decode WAL) |
| Replica writability | Read-only | Read-write |
| Use cases | HA failover, read replicas | Migrations, selective sync, CDC |
Logical replication in Postgres uses a publish/subscribe model. The primary defines a "publication" (which tables to replicate), and the replica creates a "subscription" to that publication. Only the specified tables are replicated.
This selectivity is powerful. You might replicate only the orders and customers tables to your analytics database, ignoring session logs, audit trails, and temporary data. Physical replication forces you to replicate everything.
It is easy and good
“It is really nice and good Please join the course”