DEV Community: Mugendi Njue

Postgres Logical Replication for Reporting Replicas: The Gotchas Nobody Puts in the Tutorial

Mugendi Njue — Thu, 24 Sep 2026 20:24:17 +0000

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.

Postgres Logical Replication for Reporting Replicas: The Gotchas the Tutorials Skip

Mugendi Njue — Thu, 24 Sep 2026 20:05:23 +0000

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.

First Class Functions

Mugendi Njue — Sun, 13 Dec 2020 22:42:16 +0000

Today i learned that JavaScript is a lightweight, interpreted,or JIT(Just In Time) compiled language with first class functions.
What are first class functions? Well a programming language is said to have first class functions when functions in that programming language are treated like any other variable.Example

// Assigning a variable to a function
const foo = () => {
  console.log("FooBar");
}
// Invoking the function
foo();

You can also have a function that returns another function, for example

const sayHello = () => {
   return () => {
      console.log("Hello!");
   }
}
// Method 1 of function invoking
const myFunc = sayHello();
myFunc();
// Method 2 of function invoking
sayHello()();

Functions that return other functions are called Higher-Order Functions.