If you've read the Postgres docs on logical replication, you've seen the happy path: CREATE PUBLICATION, CREATE SUBSCRIPTION, wait a minute, query your replica. It looks so clean that you start to wonder why anyone would bother with anything else. I fell for that cleanliness too. Then I put it in front of a real reporting workload, and it bit me in four distinct, very avoidable ways. This is the field report I wish someone had handed me first.
Why Logical Replication for a Reporting Replica (and Not Physical)
Physical replication ships raw WAL — the byte-for-byte changes to disk blocks. It's simple and rock solid, but it's all-or-nothing: you replicate the entire cluster, same major version, same everything. Great for failover. Terrible for a reporting replica where you want to add extra indexes for analytical queries, run a different Postgres minor version, or replicate only three tables out of two hundred because analysts don't need your audit_log table clogging up disk.
Logical replication decodes the WAL into an actual logical representation — "row X in table Y got this UPDATE" — and replays those as SQL-like operations on the subscriber. That decoupling is the whole appeal: selective tables, extra indexes on the replica, even a different schema layout downstream. It reads simple. It is not simple once you're the one operating it. That's the tradeoff, and it's the reason every gotcha below exists in the first place — you gave up physical fidelity for logical flexibility, and flexibility always has an operational cost hiding in it somewhere.
Gotcha #1: DDL Isn't Replicated — Schema Drift Sneaks Up on You
Here's the one that got me first, and it's honestly the most important thing to internalize about how logical decoding works. Logical replication decodes row changes out of the WAL — inserts, updates, deletes. It does not decode schema changes. ALTER TABLE ADD COLUMN, CREATE INDEX, DROP TABLE — none of that crosses the wire. Ever.
So what happens when someone on your team adds a column to orders in production? Nothing breaks immediately, which is exactly the trap. The publisher writes rows with the new column. The subscriber's version of orders doesn't have it, and replication just quietly starts erroring on the next UPDATE/INSERT touching that column, or worse — silently drops the new field if you didn't add it as NOT NULL. Depending on your Postgres version, an unrelated column addition might not error at all until someone runs a query expecting the new field on the replica and gets nothing.
The fix isn't a Postgres feature — it's discipline. Any DDL change on the publisher needs to be applied to the subscriber in the same maintenance step, before or in lockstep with the app deploy that starts writing the new shape. I now treat schema migrations as two-phase: migrate publisher, migrate subscriber, then deploy code. Skipping that middle step is how schema drift sneaks in, and it always sneaks in through the "small, harmless" migration nobody thought to coordinate.
Gotcha #2: Replication Slots That Never Advance and Silently Eat Your Disk
This one is scarier because it eats disk, not rows. A logical replication slot on the publisher is a promise: "I will retain WAL until this subscriber confirms it consumed it." If the subscriber disconnects — network blip, replica restart, someone drops the subscription without dropping the slot — the publisher keeps every WAL segment since the last confirmed position. Forever. Or at least until your disk fills up and your primary database, the one actually serving production traffic, grinds to a halt.
This is the sharpest edge in the whole system because the failure mode is delayed and invisible until it's catastrophic. A slot sitting idle for three days doesn't show up as an error — it shows up as pg_wal slowly climbing. Watch pg_replication_slots.active and restart_lsn lag in bytes, not just "is it connected." I now alert on WAL retained-by-slot size crossing a threshold, not on subscriber connectivity, because connectivity alone tells you nothing about the disk bomb quietly ticking on the publisher.
Gotcha #3: Sequences, Large Objects, and Other Things Pub/Sub Quietly Skips
Logical replication only replicates table data through publications you explicitly define — and even within that, it has blind spots by design. Sequences are the classic one: if your reporting replica is meant to be a faithful copy for read queries that use nextval() logic downstream, surprise — sequence values aren't replicated at all. Your replica's sequence state drifts from the publisher's the moment either side calls nextval. Large objects (pg_largeobject) aren't replicated either; logical decoding just doesn't touch them. Same story for TRUNCATE unless you explicitly enable it per-publication, and for unlogged tables, which never had WAL to decode from in the first place.
None of this is a bug. It's the tradeoff for decoding at the logical row level instead of the physical block level. But it means "logical replication" quietly does not mean "a complete copy of the database," and treating it that way is how you get a reporting replica that's subtly wrong on exactly the kinds of derived state — IDs, blobs — that someone eventually builds a dashboard on top of.
Gotcha #4: Initial Sync and Table Locking on Large Production Tables
When a subscription first comes up, Postgres has to get the subscriber to a consistent starting point before it can start applying streamed changes. That means a full table copy of every table in the publication, and that copy takes a ACCESS SHARE lock plus, more painfully, blocks on anything holding stronger locks — and on genuinely large tables, that initial COPY can run for hours. I hit this standing up a reporting replica against a 200GB events table: the sync didn't fail, it just sat there, and meanwhile the publisher's connection pool had one more long-running snapshot to account for.
The practical mitigation is boring but works: bring subscriptions up during low-traffic windows, or stage the initial copy per-table if your workload allows disabling copy_data on the subscription and backfilling manually with a controlled COPY you can pause. It's more manual work up front for less risk of contention later. As always, no free lunch.
Monitoring Checklist and Lessons
If I were starting this over, here's what I'd wire up before day one instead of after the first incident: WAL retained per replication slot (not just connection state), a scheduled diff of publisher/subscriber schema, a job to periodically resync sequence values, and an alert on initial-sync duration for any new subscription. Logical replication is a genuinely elegant piece of engineering — decoding a transaction log into row-level intent is a neat trick, and I still enjoy watching it work. But "elegant" and "hands-off" are not the same thing. Every bit of flexibility it gives you over physical replication is flexibility you now have to operate yourself.
Top comments (0)