How a Zero-Downtime Table Swap Silently Dropped Orders From Postgres Logical Replication for 91 Hours

09:14 UTC, Thursday. #finance-eng: "Why has daily revenue been flat at $118,402 for four days straight? We ran a 20%-off promo Tuesday, this should have spiked, not flatlined." No PagerDuty alert fired. No panel on the production dashboard is red. Checkout is taking orders normally. Whatever broke didn't trip a single automated check, because nothing in production is actually broken.

the scramble

First theory: the promo's attribution tracking is broken, not revenue itself. Marketing's UTM tagging has flaked before. Someone pulls the Stripe payouts dashboard directly, bypassing the internal reporting pipeline entirely. Payouts for the last four days are up, in line with the promo. Money is moving. The number everyone is staring at is wrong, not the business.

Second theory: the BI tool is serving a stale cached query. The finance dashboard sits on top of an internal analytics Postgres instance, fed by logical replication from the primary rather than queried against production directly, a deliberate choice made a year ago so heavy reporting queries can never compete with checkout for connections or I/O. Someone forces a hard refresh on the dashboard, bypassing the BI tool's own cache layer. Same flat number.

Third theory, and the one that finally moves: query the analytics replica directly instead of through the dashboard.

SELECT count(*), max(created_at) FROM orders;

   count   |        max
-----------+----------------------------
 9,214,880 | 2026-10-02 14:02:41+00

-- same query against production, same moment
SELECT count(*), max(created_at) FROM orders;

   count   |        max
-----------+----------------------------
 9,222,563 | 2026-10-06 09:40:12+00

Production has 7,683 more rows than the replica, and the replica's newest row is timestamped almost exactly four days in the past. That timestamp isn't a random stale point. It's 12 minutes after the rename-swap migration that shipped Tuesday afternoon. The dashboard was never broken. The data feeding it stopped arriving.

the hunt

Tuesday's deploy converted the orders table's primary key from int4 to int8 using the standard zero-downtime shadow-table pattern: build orders_new with the wider key, backfill it in batches, dual-write via trigger while the backfill catches up, then an atomic rename swap inside one transaction:

BEGIN;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

Clean, fast, no lock held longer than the two renames. The on-call engineer who ran it watched checkout error rates for twenty minutes afterward and saw nothing. By every metric anyone was watching, the migration worked.

First check on the replication path: is the slot even still alive?

SELECT slot_name, active, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name = 'analytics_sub';

  slot_name    | active | confirmed_flush_lsn
----------------+--------+----------------------
 analytics_sub | t      | 4A2/8F1C3D00

-- run again two minutes later
 analytics_sub | t      | 4A2/8F22A188

Active, and the LSN is climbing between queries. That reads as healthy replication by every normal heuristic, and it's the dead end that costs the most time: the publication backing this subscription also covers customers, invoices, and three other tables that are still being written to constantly. Those writes keep the logical decoding worker busy and the confirmed LSN advancing regardless of what's happening with orders specifically. A slot-level health check can look perfectly fine while one table inside it has gone completely silent.

The next check is narrower, not "is the slot alive" but "what tables is this publication actually sending":

SELECT pubname, tablename FROM pg_publication_tables
WHERE pubname = 'orders_pub';

  pubname    | tablename
--------------+------------
 orders_pub  | orders_old
 orders_pub  | customers
 orders_pub  | invoices

orders_old. Not orders. The publication has been faithfully, correctly, silently replicating a table that nothing has written to since 14:02:17 UTC on Tuesday.

the find

ALTER PUBLICATION ... ADD TABLE resolves the table name to its object ID at the moment the command runs and stores that OID in pg_publication_rel. It does not store the name. From that point forward, the publication tracks whatever object owns that OID, not whatever object currently answers to the name orders.

The rename-swap pattern is specifically designed to be atomic and cheap, which it achieves by never actually copying data at cutover time. It just reassigns two names to two existing relations. The relation that was orders for the three years before Tuesday did not stop existing. It got renamed to orders_old and kept its OID, and the publication kept following that OID exactly as configured. The relation that's been named orders since 14:02:17 UTC is a different object with a different OID, one the publication was never told about, because the migration added a new table under a temporary name and the publication definition was never revisited after the swap.

Nothing in this chain is a bug. The publication did precisely what ALTER PUBLICATION ADD TABLE orders asked it to do, a year ago, for the table that was orders at the time. A rename is not a schema change the replication system has any reason to treat as suspicious, since tables get renamed for all kinds of reasons that have nothing to do with swapping in a different relation underneath a stable name. There was no constraint violation, no decoding error, no WAL gap, nothing for any alert to fire on. The failure mode is a correctly configured system doing exactly what it was told, applied to an assumption, "the table named orders is the table I meant," that a swap-based migration quietly invalidated.

the fix

Point the publication at the table that's actually live, and drop the dead one once its replicated history is no longer needed on the subscriber:

ALTER PUBLICATION orders_pub DROP TABLE orders_old;
ALTER PUBLICATION orders_pub ADD TABLE orders;

Adding a table to an existing publication doesn't push any of its current rows to subscribers by itself. It only starts streaming changes from that point forward. The subscriber still needs the 91 hours of orders it missed, plus confirmation that its local orders table matches the live one row for row, not just going forward from here:

ALTER SUBSCRIPTION analytics_sub REFRESH PUBLICATION WITH (copy_data = true);
-- 11 minutes to copy and index 9.2M rows

REFRESH PUBLICATION diffs the subscription's known relations against the publication's current membership, and for anything newly added it runs a full initial copy for that relation only, while customers and invoices keep streaming without interruption the entire time. Eleven minutes later the replica's orders table matches production exactly, and the finance dashboard's next scheduled refresh picks up four days of orders in one jump.

The longer-term fix is a check that doesn't depend on anyone remembering to update the publication by hand during a migration that was never framed as a replication change in the first place:

-- runs against every table/subscriber pair, alerts on the table name, not the slot
SELECT p.tablename,
       now() - max(o.created_at) AS staleness
FROM pg_publication_tables p
JOIN orders o ON true
WHERE p.pubname = 'orders_pub' AND p.tablename = 'orders'
GROUP BY p.tablename
HAVING now() - max(o.created_at) > interval '2 hours';

It also became a standing step in the shadow-table migration runbook: any rename-swap that touches a table covered by logical replication requires a publication membership check in the same change window as the cutover, not as a follow-up ticket filed whenever someone happens to notice.

the aftermath

The migration itself worked exactly as designed, start to finish, with zero downtime and zero checkout errors. The thing it broke wasn't checkout. It was a dependency three systems away that nobody thought to list as a stakeholder for a primary-key type change, and the only signal it ever produced was a dashboard that quietly stopped changing.