PostgreSQL VACUUM, Autovacuum, and Bloat Cheat Sheet

VACUUM (ANALYZE, VERBOSE) public.orders;

-- For a read-only preview of command defaults and options:
SELECT name, setting
FROM pg_settings
WHERE name IN (
  'vacuum_cost_delay',
  'vacuum_cost_limit',
  'maintenance_work_mem'
)
ORDER BY name;

SELECT schemaname,
       relname,
       n_live_tup,
       n_dead_tup,
       n_ins_since_vacuum,
       n_mod_since_analyze,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC, n_mod_since_analyze DESC;

SELECT p.pid,
       p.relid::regclass AS relation,
       p.phase,
       p.heap_blks_scanned,
       p.heap_blks_total,
       p.index_vacuum_count,
       a.wait_event_type,
       a.wait_event
FROM pg_stat_progress_vacuum AS p
JOIN pg_stat_activity AS a USING (pid)
ORDER BY p.pid;

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
  'autovacuum',
  'autovacuum_max_workers',
  'autovacuum_naptime',
  'autovacuum_vacuum_threshold',
  'autovacuum_vacuum_scale_factor',
  'autovacuum_vacuum_max_threshold'
)
ORDER BY name;

ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 5000
);

SELECT relname, reloptions
FROM pg_class
WHERE oid = 'public.orders'::regclass;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

SELECT n.nspname AS schema_name,
       c.relname,
       age(c.relfrozenxid) AS xid_age
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 25;

SHOW autovacuum_freeze_max_age;

SELECT pid, usename, application_name, state,
       xact_start, backend_xmin, age(backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

SELECT gid, prepared, owner, database,
       age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY xid_age DESC;

SELECT slot_name, slot_type, active, xmin, catalog_xmin,
       restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
ORDER BY slot_name;

CREATE TABLE account_state (
  account_id bigint PRIMARY KEY,
  status text NOT NULL,
  balance numeric(14,2) NOT NULL,
  updated_at timestamptz NOT NULL
) WITH (fillfactor = 80);

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       n_tup_newpage_upd
FROM pg_stat_user_tables
WHERE relname = 'account_state';

SELECT c.oid::regclass AS relation,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
       pg_size_pretty(pg_indexes_size(c.oid)) AS index_size,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       s.n_live_tup,
       s.n_dead_tup,
       s.last_autovacuum
FROM pg_class AS c
JOIN pg_stat_user_tables AS s ON s.relid = c.oid
WHERE c.oid = 'public.orders'::regclass;