Postgres: EXPLAIN ANALYZE, VACUUM, and Why BI Must Leave the Primary

Disk on the orders primary hit 91% on a Tuesday afternoon. Table sizes looked normal. pg_wal was 240 GB. pg_stat_activity showed a Looker extract that had been seq-scanning orders for 70 minutes in a still-open transaction.

Autovacuum could not clean the dead tuples that transaction still needed. The Debezium slot was healthy but the WAL volume from the stuck snapshot plus ordinary writes outran the disk we had sized for “OLTP plus a little CDC.” We killed the BI session, the vacuum caught up, and I spent the evening explaining why a dashboard is not a reason to scan the primary.

Wrong number, then disk. Same week a different extract used SELECT * through a foreign table and double-counted refunds. The plan would have said seq scan. Nobody had opened it.

EXPLAIN ANALYZE first

I do not tune a query I have not timed. EXPLAIN is a guess. EXPLAIN ANALYZE is what ran, with actual rows and time. If the estimate and the actual differ by an order of magnitude, your statistics are stale or your predicate hides a function on the column.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT order_id, status, amount_cents
FROM orders
WHERE created_at >= now() - interval '7 days'
  AND status = 'shipped';

A seq scan on a 200 million row table for a week of data means created_at is not doing the job — wrong index, wrapping the column in date_trunc so the index cannot be used, or a stats target that still thinks the table is small. Fix the predicate and the index before you buy a larger instance.

VACUUM (ANALYZE) orders; after a bulk load is not folklore. The planner is only as honest as the last analyze.

flowchart TD
  q["Query or extract"] --> plan["EXPLAIN ANALYZE"]
  plan --> idx["Index + tight predicate"]
  plan --> seq["Seq scan on primary"]
  seq --> lock["Long snapshot"]
  lock --> bloat["VACUUM cannot reclaim"]
  bloat --> disk["Disk and WAL grow"]
  idx --> replica["Run it on a replica"]

A long read on the primary is a vacuum problem. Put BI on a replica before it becomes a disk problem.

Bloat and the slot share the disk

Dead tuples stay until no snapshot still needs them. A BI session, a forgotten BEGIN, or a CDC snapshot held too long will pin that horizon. The table file grows. You pay for it in cache miss and in backup size.

SELECT
    relname,
    n_dead_tup,
    last_autovacuum,
    pg_size_pretty(pg_relation_size(relid)) AS rel
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT
    slot_name,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS retained_wal
FROM pg_replication_slots;

I page on retained WAL and on active = false, the same alarms as in the Debezium on Postgres guide. max_slot_wal_keep_size is the circuit breaker so a dead connector cannot take writes with it.

VACUUM is not optional maintenance. It is how Postgres stays the same size as your live data. If you need to be loud, pg_repack or a controlled rewrite, but only after you fix the session that prevented the regular vacuum.

Never BI-scan the primary

Warehouse extracts, Looker PDTs, “just one export,” and ad-hoc SELECT * FROM orders belong on a replica or on CDC into the warehouse. The primary’s job is the application write path. A seq scan steals I/O, holds a snapshot, and competes with the slot for disk.

CDC is the extract I want for tables that change and delete. Batch dumps are fine for small reference data. Both should leave the primary out of the scan. If you are already running Connect, the apply side of that pipe is in Kafka Connect in production.

Indexes that match the predicate

The week-of-shipped query wants a btree that the planner can use without wrapping the column.

CREATE INDEX CONCURRENTLY orders_shipped_created_at
    ON orders (created_at)
    WHERE status = 'shipped';

A partial index is smaller and honest about the predicate. date_trunc('day', created_at) in the WHERE and a plain btree on created_at will still seq-scan. Either store a created_date or express the range as >= / < timestamps.

CREATE INDEX without CONCURRENTLY on a busy primary is a write lock. I do not do that in business hours to “help” a dashboard.

Replica, not writer

App writes  →  primary  →  replica  →  BI / ad-hoc
                 ↓
              logical slot → Debezium → warehouse

The replica can be minutes behind. That lag is an SLA you print on the extract, not a reason to move Looker back to the writer. CDC is how the warehouse stays close without a 70-minute snapshot on orders.

Operator checklist I keep next to the Debezium guide:

• Slot active, retained WAL under a cap
• No idle in transaction older than N minutes
• Autovacuum last run on the hot tables
• BI roles have no CONNECT on the writer

Hub: PostgreSQL.

FAQ

EXPLAIN vs EXPLAIN ANALYZE? EXPLAIN is the planner’s story. ANALYZE is what ran. Use ANALYZE on a replica or a sampled predicate if you are scared of the primary. Do not ship a rewrite from EXPLAIN alone.

Is VACUUM ANALYZE enough after a bulk load? Usually. If the table is a wreck of bloat, fix the long transaction first, then vacuum. FULL is a rewrite with a lock.

Can I CDC from a replica? Logical decoding is typically on the primary (or a promoted node). Replica queries are for BI. Do not confuse the two.

Why is the slot inactive? Connector down, wrong publication, or a slot left behind after a rename. Inactive plus growing retained WAL is a disk page.

When is a batch dump OK? Small, insert-only reference data. Orders, balances, and anything with deletes want CDC or you will lie about removals.

Pitfalls

Tuning work_mem before reading the plan. You will hide a seq scan behind a larger sort and call it a win.

VACUUM FULL as a habit. It locks the table. Fix the bloat source. Use FULL as a last resort on a quiet window.

A slot left after a deleted connector. Postgres keeps the WAL. Drop the slot in the teardown.

Logical decoding on the primary with no max_slot_wal_keep_size. The first failed connector becomes a disk incident.

Pointing Metabase at the writer because the replica was “a few seconds behind.” That lag is cheaper than a locked vacuum.

What this means for your pipelines

Postgres is the source of truth until someone treats it like a warehouse. I start with EXPLAIN ANALYZE, I watch dead tuples and slot retained WAL on the same dashboard, and I refuse extracts on the primary.

CDC earns its keep when you need deletes and low lag. It also makes the primary a participant in your pipeline. Respect that: heartbeats, slot caps, and no BI session that can pin a snapshot for an hour. The 91% disk was not a storage mis-order. It was a read that should never have landed on the writer.