In PostgreSQL, regular maintenance tasks like vacuuming and analyzing tables are essential for maintaining optimal database performance. Vacuuming removes dead rows and prevents transaction ID wraparound, while analyzing updates statistics for query optimization.
Vacuuming in PostgreSQL is crucial for reclaiming storage occupied by dead tuples (rows) and for preventing transaction ID wraparound. Dead tuples are rows that are no longer visible to active transactions due to updates or deletes. Vacuuming can be performed manually or automatically by PostgreSQL's autovacuum process.
The pg_stat_user_tables view provides information about vacuuming:
pg_stat_user_tables
select relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count, last_analyze, last_autoanalyze, analyze_count, autoanalyze_count, n_mod_since_analyze, n_live_tup, n_dead_tup, case when n_live_tup > 0 then round(n_dead_tup*1.0/n_live_tup*100, 2) else 0 end as pct_dead_tup from pg_stat_user_tables order by relname;
last vacuum times, vacuum counts, live
dead tuples
Analyzing in PostgreSQL updates the optimizer's statistics on tables, enabling the query planner to generate efficient execution plans. It collects information about the distribution of data in tables, including the number of rows, distinct values, and correlation between columns.
The pg_stat_user_tables view also includes information about analyzing:
SELECT relname, last_analyze, last_autoanalyze, analyze_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY relname;
CREATE OR REPLACE FUNCTION public.vacuum_tables() RETURNS void AS $$ BEGIN EXECUTE 'VACUUM VERBOSE ANALYZE ' || string_agg(quote_ident(schemaname) || '.' || quote_ident(tablename), ', ') FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); END; $$ LANGUAGE plpgsql; -- Schedule the function using an alternative method (e.g., using pgAgent, cron job on the OS level, or other scheduling tools) -- Example using pgAgent (assuming it's installed and configured) SELECT * FROM pgagent.pga_schedule_job( 'Vacuum Tables', '0 0 * * 0', false, 'SELECT public.vacuum_tables()' );
ALTER TABLE my_table SET (autovacuum_enabled = true, autovacuum_vacuum_threshold = 1000);
SELECT relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count, n_mod_since_analyze FROM pg_stat_user_tables ORDER BY relname;
vacuum_cost_delay
vacuum_cost_limit
autovacuum_vacuum_cost_delay
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 20; SELECT pg_reload_conf();
VACUUM VERBOSE my_table;
ANALYZE my_table;
Analyzing and vacuuming tables in PostgreSQL is essential for maintaining optimal performance and preventing issues related to dead tuples and outdated statistics. By understanding these maintenance tasks and implementing best practices, you can ensure that your PostgreSQL database operates efficiently and reliably.