-- Basic VACUUM on a table
VACUUM table_name;

-- VACUUM with ANALYZE to refresh planner stats
VACUUM ANALYZE table_name;

-- VACUUM all tables in the current DB
VACUUM;

-- Reclaims maximum space by rewriting the table; requires exclusive lock
VACUUM FULL table_name;

-- Enable/disable autovacuum launcher (default: on)
autovacuum = on

-- Max concurrent autovacuum workers (default: 3)
autovacuum_max_workers = 3

-- Sleep time between autovacuum cycles (default: 60s)
autovacuum_naptime = '60s'

-- Trigger when (threshold + scale_factor * reltuples) dead tuples appear
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

-- ANALYZE triggers
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

-- Since PG13: insert-triggered autovacuum thresholds (for insert-heavy tables)
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2

-- Maintenance memory (default ~64MB); safe to raise for faster vacuum
maintenance_work_mem = '256MB'

-- Per autovacuum worker memory (default -1 = use maintenance_work_mem)
autovacuum_work_mem = '-1'

-- Cost-based vacuum (manual vacuum): default delay 0ms, limit 200
vacuum_cost_delay = '0ms'
vacuum_cost_limit = 200

-- Cost-based vacuum (autovacuum): default delay 2ms (PG12+), limit -1 (=vacuum_cost_limit)
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_vacuum_cost_limit = -1

SELECT
  schemaname, tablename,
  n_tup_ins, n_tup_upd, n_tup_del,
  n_dead_tup,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

SELECT
  pid,
  datname,
  relid::regclass AS table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM pg_stat_progress_vacuum;

SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  ROUND((n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2) AS dead_tuple_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_tuple_pct DESC;

autovacuum_naptime = '30s'
autovacuum_vacuum_scale_factor = 0.10
autovacuum_analyze_scale_factor = 0.05
autovacuum_max_workers = 6
maintenance_work_mem = '1GB'   -- be mindful of total memory with many workers

ALTER TABLE large_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay = '10ms'
);

ALTER TABLE events SET (
  autovacuum_vacuum_insert_threshold = 1000,
  autovacuum_vacuum_insert_scale_factor = 0.02,  -- smaller to keep visibility map fresh
  autovacuum_analyze_scale_factor = 0.05
);

autovacuum_naptime = '300s'
autovacuum_vacuum_scale_factor = 0.40
autovacuum_analyze_scale_factor = 0.20

maintenance_work_mem = '64MB'
autovacuum_work_mem = '128MB'
autovacuum_max_workers = 2

-- Give VACUUM more memory (cluster-wide)
ALTER SYSTEM SET maintenance_work_mem = '1GB';
SELECT pg_reload_conf();

-- Make autovacuum pause less per cycle (cluster-wide)
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';  -- or 0–2ms if you can handle I/O
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;   -- higher budget

-- Per-table overrides for hotspots
ALTER TABLE problematic_table SET (autovacuum_vacuum_cost_delay = '0ms');

ALTER TABLE small_table SET (
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_threshold = 500
);

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

-- Emergency action (on affected tables)
VACUUM (FREEZE, VERBOSE) table_name;  -- or VACUUM FREEZE; cautiously

-- Cluster-wide maintenance window
VACUUM (FREEZE);

-- Find low-usage or suspicious indexes
SELECT schemaname, relname AS tablename, indexrelname AS indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild
REINDEX INDEX index_name;      -- or: REINDEX TABLE table_name;

-- Persist configuration safely
ALTER SYSTEM SET autovacuum_naptime = '30s';
SELECT pg_reload_conf();

-- High-churn OLTP table
ALTER TABLE transactions SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_naptime = '15s'
);

-- Append-only log table (only if you schedule manual VACUUM/ANALYZE to prevent wraparound)
ALTER TABLE logs SET (autovacuum_enabled = off);

# Example nightly job
vacuumdb -d mydb --analyze-in-stages --all --jobs=4

CREATE OR REPLACE VIEW jusdb_vacuum_monitor AS
SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup + n_dead_tup > 0
       THEN ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2)
       ELSE 0 END AS dead_tuple_pct,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 100
ORDER BY dead_tuple_pct DESC;