If you've read a Postgres logical replication tutorial, you know the drill. CREATE PUBLICATION on the primary, CREATE SUBSCRIPTION on the replica, wait a few seconds, and boom — your data is flowing. It looks so simple that you start to wonder why anyone bothers with the more complicated physical replication setups.
I fell for that simplicity too. Then I built a real reporting replica — the kind analysts hammer with ad-hoc queries all day, running a different Postgres major version than the source, with its own indexes tuned for reads instead of writes — and I hit four gotchas that nobody mentions in the getting-started guide. Let me walk you through them, because they will bite you if you don't plan for them upfront.
Why Logical Replication (Not Physical Standbys) for Reporting Workloads
First, why logical at all? A physical standby replicates the entire cluster byte-for-byte at the WAL block level. It's fast and battle-tested, but it's all-or-nothing — you get every database, every table, and the replica has to run the exact same major version as the primary. You can't add an extra reporting index without also adding it on the primary. You can't run heavy analytical queries without them showing up as replay lag that can starve your standby.
Logical replication works differently. Instead of shipping raw data pages, Postgres decodes the WAL into logical changes — "row X was updated to this value" — and replays those as actual SQL-like operations on the subscriber. That decoupling is the whole point: you can replicate just the tables reporting actually needs, add reporting-only indexes on the replica, and even run a newer Postgres version there. It's a fundamentally different tool for a fundamentally different job — not a faster physical replica, a selective one.
That flexibility is not free, though. You're now decoding and replaying, not just streaming bytes, and you've traded the simplicity of "the replica is the primary" for a handful of edges that require your attention.
Gotcha #1: DDL Isn't Replicated — Schema Drift Sneaks Up on You
Here's the first surprise: logical replication only replicates DML — inserts, updates, deletes. It does not replicate DDL. Add a column on the primary, and your subscription doesn't know it exists. Depending on the replica identity and column defaults, you'll either get silent gaps in reporting data or the replication worker will just stop with an error about a missing column, and nobody notices until a dashboard is empty.
The fix isn't automatic — it's discipline. Every schema migration touching a published table needs a corresponding, deliberately ordered step on the subscriber, applied before or in lockstep with the primary's migration, depending on direction of change. Some teams script this into their migration tooling explicitly rather than trusting it to happen by accident. Treat DDL against replicated tables as a two-database transaction, even though Postgres will never enforce that for you.
Gotcha #2: Sequences Don't Sync — Your Serial Columns Lag Behind
This one is sneaky because it doesn't fail loudly. Sequences backing your SERIAL/IDENTITY columns are not replicated by logical replication at all. The row data flows through fine — id 4821, id 4822 — but the sequence object itself on the subscriber sits wherever it started.
Why does this matter for a read-only reporting replica? It usually doesn't, until someone decides to write to the replica for a one-off fix, or you promote that replica during a failover and suddenly nextval() hands back an id that already exists. I've seen this cause duplicate key errors weeks after a "read-only" replica quietly became load-bearing for something else. If there's any chance your reporting replica could ever take writes, sync sequence values manually as part of your setup and any resync process — Postgres won't do it for you.
Gotcha #3: Initial Sync and Large Tables — The COPY Phase Can Lock Up Your Source
When you first attach a subscription, Postgres has to get the subscriber caught up to a consistent starting point. It does this with an initial data copy — a COPY of the entire table — before it starts applying incremental changes. For a small table, this is instant and forgettable. For a 400GB fact table, it's a long-running transaction on the source holding a snapshot, competing for I/O, and potentially extending vacuum-related bloat while it runs.
If you're doing this against a live production primary during business hours, you can absolutely cause visible slowdowns — the kind that get you paged. The pragmatic move is to schedule initial syncs for your largest tables during low-traffic windows, publish tables incrementally rather than the whole schema at once, and monitor replication lag and source-side load during that copy phase, not after.
Gotcha #4: Orphaned Replication Slots Silently Eating Your WAL Disk Space
This is the one that will actually take your primary down. A replication slot is Postgres's promise to the subscriber: "I will not discard WAL you haven't consumed yet." That's exactly what makes logical replication reliable — but it's also a landmine. If a subscriber disconnects, gets decommissioned, or you tear down a reporting replica without dropping its slot, Postgres keeps that promise forever. WAL just accumulates on the primary, disk fills up, and eventually writes fail cluster-wide.
I've seen this happen from something as mundane as a test subscriber spun up once and forgotten. The lesson: monitor pg_replication_slots for inactive slots and their restart_lsn distance from current WAL, and make dropping the slot an explicit, mandatory step whenever you decommission a subscriber. Don't let "the replica is gone" and "the slot is gone" be two separate facts.
A Pragmatic Checklist for Running Logical Replication Reporting Replicas in Production
None of this makes logical replication a bad choice — it's genuinely the right tool for selective, cross-version, read-optimized reporting replicas. But go in with eyes open:
- Version-control DDL changes to published tables and apply them to subscribers in lockstep, never after the fact.
- Sync sequence values explicitly after initial load and after any resync — don't assume they matched, because they didn't.
- Schedule initial syncs for large tables during low-traffic windows and consider publishing tables in batches instead of all at once.
- Alert on inactive replication slots and their WAL retention distance — treat an orphaned slot as a production incident waiting to happen, not a cleanup task.
- Monitor replication lag continuously, not just at setup time — a reporting replica that's silently hours behind is worse than one that's clearly broken.
The tutorials show you the two commands that make replication start. What they don't show you is everything Postgres is quietly not doing for you in between. Know that list, and logical replication is a genuinely excellent tool. Ignore it, and it's a slow-motion incident with your name on it.
Top comments (0)