PostgreSQL Consulting: Proven 24×7 Support, Remote DBA & Performance Engineering

The baseline is captured over a window long enough to include month-end, batch and reporting peaks: pg_stat_statements totals per queryid, wait events sampled from pg_stat_activity every second, pg_stat_io on PostgreSQL 16 and later, dead-tuple ratios from pg_stat_user_tables, checkpoint and background-writer counters, and replication lag from pg_stat_replication. Attribution names the constraint from that evidence and the exact query that reproduces it.

Changes are made one at a time and are reversible by construction: an index built CONCURRENTLY, a per-table autovacuum setting, a statistics target, a parameter with its reload-versus-restart requirement stated, a pooling-mode change. The blast radius and rollback path are written before execution, and the change is rehearsed on a production-size copy.

-- Where the time goes: top statements by total time with I/O split
-- (PostgreSQL 17+ column names; use blk_read_time on 16 and earlier)
SELECT
    queryid,
    calls,
    ROUND(total_exec_time::numeric, 1)        AS total_ms,
    ROUND(mean_exec_time::numeric, 2)         AS mean_ms,
    ROUND(shared_blk_read_time::numeric, 1)   AS read_ms,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_written,
    LEFT(query, 70)                           AS query_head
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- What everyone is waiting on right now
SELECT wait_event_type, wait_event, state, COUNT(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Validation re-runs the same baseline after the change and reports p95 and p99 per queryid, buffers hit versus read, dead-tuple ratio and lag side by side. Improvements are reported as measured; anything not yet measured is labelled an estimate.

PostgreSQL consulting engagements almost always find default autovacuum thresholds wrong for large tables: at the default scale factor a billion-row table accumulates two hundred million dead tuples before autovacuum starts. We set per-table autovacuum_vacuum_scale_factor and autovacuum_vacuum_insert_scale_factor, raise vacuum_cost_limit so vacuum keeps up with the write rate, and size autovacuum_max_workers against the number of hot tables rather than the number of cores.

Wraparound is alerted on age(datfrozenxid) per database at half of autovacuum_freeze_max_age, well before the forced vacuum, and the three things that block freezing are reported daily: long-running transactions, prepared transactions and inactive replication slots.

-- Tables closest to a forced anti-wraparound vacuum
SELECT c.oid::regclass                              AS table_name,
       age(c.relfrozenxid)                          AS xid_age,
       ROUND(100.0 * age(c.relfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::int, 1)
                                                    AS pct_of_max,
       s.n_dead_tup,
       s.last_autovacuum
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 15;

Bloat is measured with pgstattuple rather than estimated, and reclaimed with REINDEX CONCURRENTLY or pg_repack in a change window with a rollback, never with VACUUM FULL on a live table.