Skip to content

PostgreSQL Footguns

Mistakes that cause data loss, table locks, replication splits, or hours of unexplained slowness.

1. VACUUM FULL on a Live Table

You notice a table has massive bloat and run VACUUM FULL mytable during business hours. VACUUM FULL acquires an ACCESS EXCLUSIVE lock — the most restrictive lock PostgreSQL has. Every read and write to that table queues behind it. For a large table, this can take hours. Your application appears hung.

Fix: Use pg_repack instead (apt install postgresql-<ver>-repack). It rebuilds the table online with minimal locking. Only run VACUUM FULL in a maintenance window with confirmed application downtime. For routine bloat management, tune autovacuum aggressiveness: ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0.01).

Under the hood: PostgreSQL's MVCC means dead rows (from UPDATE/DELETE) remain on disk until VACUUM reclaims them. Regular VACUUM marks dead rows as reusable but doesn't shrink the table file. VACUUM FULL rewrites the entire table to a new file, reclaiming disk space — but requires an ACCESS EXCLUSIVE lock for the entire duration. For a 100GB table, this can mean hours of downtime.

2. Adding a Column with a DEFAULT that Rewrites the Whole Table

You run ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'pending'. On PostgreSQL < 11, this rewrites the entire table — all 500M rows — while holding an ACCESS EXCLUSIVE lock for hours. On PG 11+, volatile defaults (like now()) still cause rewrites.

Fix: On PostgreSQL 11+, adding a column with a constant default is instant (metadata-only). For volatile defaults or older versions: add the column with DEFAULT NULL first (instant), backfill in batches, then add the constraint/default. Use pg_repack or a tool like pgroll or reshape for zero-downtime schema changes on large tables.

3. Missing Index on Foreign Key Causes Lock Escalation

You delete from a parent table. PostgreSQL must verify no child rows exist, so it scans the child table. Without an index on the foreign key column, this is a sequential scan that holds a lock for the duration. In a busy system, this cascades into lock queue buildup that blocks the entire application.

Fix: Always create an index on foreign key columns in child tables. Query for missing FK indexes:

SELECT c.conname, c.conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

4. idle in transaction Connections Blocking VACUUM

An application opens a transaction, does a read, then holds the connection open waiting for user input or doing application work outside the transaction. PostgreSQL can't vacuum rows older than the oldest active transaction. After enough time, table bloat explodes and transaction ID wraparound risk climbs.

Fix: Set idle_in_transaction_session_timeout = '10min' in postgresql.conf to kill connections that hold open transactions without activity. Fix the application to close transactions promptly. Monitor with: SELECT count(*), max(now() - state_change) FROM pg_stat_activity WHERE state = 'idle in transaction'.

5. Overloading pg_dump on the Primary During Peak Traffic

You run pg_dump on the primary database during business hours to generate a backup. pg_dump runs a full sequential scan of every table — it saturates I/O, increases page cache pressure, and spikes CPU. Your application slows down. The pg_dump may take 3x longer than expected due to lock contention.

Fix: Run pg_dump against a replica instead: pg_dump -h replica-host -U postgres mydb -Fc > backup.dump. Or use continuous backup tools (WAL archiving with Barman, pgBackRest, or Litestream). Schedule pg_dump during off-peak hours with ionice -c3 and nice -n 19 to lower its I/O and CPU priority.

6. max_connections Set Too High Causes Memory Exhaustion

You increase max_connections = 1000 to stop "too many clients" errors. PostgreSQL pre-allocates per-connection memory structures. With work_mem = 64MB and 1000 connections, a worst-case query sort can allocate 64GB of memory. Your server OOM-kills the PostgreSQL process under load.

Fix: max_connections should be low (100-300 for most servers). Deploy PgBouncer in front of PostgreSQL to multiplex thousands of application connections into a small pool. Formula: work_mem * max_connections * sort_operations_per_query must fit in RAM with headroom. Use transaction pooling mode in PgBouncer for maximum efficiency.

Remember: PostgreSQL uses a process-per-connection model (not threads). Each connection is a forked backend process consuming ~5-10MB of RAM just for the process overhead, plus work_mem for sorts, plus temp_buffers for temp tables. 1000 connections = 1000 processes = context switching overhead that degrades performance even before memory is exhausted. PgBouncer in transaction mode can serve 10,000 application connections with 50 backend connections.

7. Replication Slot Accumulating WAL Forever

You create a replication slot for a replica or logical replication consumer. The consumer falls behind or is decommissioned without dropping the slot. PostgreSQL must retain all WAL since the slot's restart_lsn — your WAL directory fills to 100%, crashes PostgreSQL, and the server won't restart because /var is full.

Fix: Monitor replication slot lag: SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag FROM pg_replication_slots. Drop unused slots immediately: SELECT pg_drop_replication_slot('slot_name'). Set max_slot_wal_keep_size = 10GB (PG 13+) to limit WAL retained per slot.

8. EXPLAIN Without ANALYZE Misleads You

You run EXPLAIN SELECT ... to debug a slow query. The output shows an Index Scan and you assume the query is fast. But the planner's row estimates are wildly wrong (table statistics are stale), and the query actually does a 50M row scan at runtime. You optimize the wrong thing.

Fix: Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to see actual row counts, actual vs estimated rows, and buffer hits/misses. Run ANALYZE mytable first if statistics might be stale. The gap between estimated and actual rows is the most important signal: a large discrepancy means the planner is making bad decisions.

Debug clue: In EXPLAIN ANALYZE output, look for rows=X (estimated) vs actual rows=Y. If X and Y differ by more than 10x, the planner is flying blind. Common causes: stale statistics (run ANALYZE), correlated columns (increase default_statistics_target or create extended statistics), or parameterized queries where the planner uses generic estimates. auto_explain module logs slow queries with their plans automatically.

9. Point-in-Time Recovery Test Never Run

You set up WAL archiving and pg_basebackup for PITR. You trust the backup but have never tested a restore. During an actual incident, you discover: the WAL archive is missing 3 days of files (archiving silently failed), the restore command has a typo, and the replica you thought was current is 2 hours behind.

Fix: Test your restore procedure monthly in a non-production environment. Automate restore testing with a job that: restores the latest backup to a test instance, runs pg_basebackup --check-md5, verifies SELECT count(*) FROM critical_table, and alerts on failure. Untested backups are not backups.

10. Transaction ID Wraparound (XID Exhaustion)

PostgreSQL uses 32-bit transaction IDs. After ~2 billion transactions, IDs wrap around. PostgreSQL goes into emergency shutdown mode to prevent data corruption, refusing all writes and showing "database is not accepting commands to avoid wraparound data loss." This is a production emergency that requires manual intervention.

Fix: Monitor age(datfrozenxid) across all databases. Alert when it exceeds 1.5 billion. SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC. Ensure autovacuum runs regularly (it advances datfrozenxid). For emergency recovery: VACUUM FREEZE on the most critical tables, then run aggressive autovacuum. Never ignore WARNING: database ... must be vacuumed in PostgreSQL logs.

War story: Sentry experienced a near-miss XID wraparound in 2015 that they publicly documented. Their busiest table's age(relfrozenxid) reached 1.8 billion — dangerously close to the 2.1 billion limit where PostgreSQL shuts down. The root cause was idle in transaction connections preventing autovacuum from advancing the freeze horizon. They had to run an emergency multi-hour VACUUM FREEZE on a 1TB table during production.