PostgresDB Vacuum Issues

What is PostgresDB Vacuum Issues

Check for Long-Running Transactions:Query to find long-running transactions:SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active') ORDER BY query_start;If any transactions are running longer than expected, consider terminating them (if safe) using:SELECT pg_terminate_backend(pid);Analyze Table Bloat:Use the following query to identify table and index bloat:SELECT schemaname, tablename, pg_size_pretty(real_size) AS real_size, pg_size_pretty(extra_size) AS extra_size, extra_size::float / real_size::float * 100 AS extra_pct, fillfactor, (real_size - otta) AS wastedbytesFROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4)) / (bs-20::float)) AS otta, COALESCE(ceil(relpages/((bs-20::float)/((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))::numeric), 0) AS expectedpages, relpages, fillfactor, (bs*(cc.relpages-otta)) AS extra_size, (ceil(relpages/((bs-20::float)/((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))::numeric)-otta)::int AS unusedpages, CASE WHEN relpages > otta THEN bs*(relpages-otta)::bigint ELSE 0 END AS real_size FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END))/4)*(CASE WHEN hdr%ma=0 THEN 1 ELSE 2 END) AS datahdr, (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))/4) AS nullhdr2, hdr, fillfactor, reltuples, relpages, pg_class.oid FROM ( SELECT ns.nspname AS schemaname, tbl.relname AS tablename, tbl.reltuples, tbl.relpages, tbl.reltoastrelid, tbl.reloptions, tbl.oid, coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN tbl.relhasoids THEN 24 ELSE 8 END AS hdr, sum((1-coalesce(s.null_frac,0)) * coalesce(s.avg_width, 2048) + (CASE WHEN s.data_type='numeric' THEN 12 ELSE 0 END)) AS datawidth, max(coalesce(s.null_frac,0)) AS maxfracsum, hdr+( CASE WHEN max(coalesce(s.null_frac,0)) > 0 THEN coalesce(nullhdr, 23) ELSE 23 END ) AS nullhdr, count(s.*) AS ma FROM pg_attribute s JOIN pg_class tbl ON s.attrelid = tbl.oid JOIN pg_namespace ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats y ON s.attrelid=y.tableoid AND s.attnum=y.attnum LEFT JOIN ( SELECT CASE WHEN count(*) > 1 THEN 32 ELSE 16 END AS nullhdr, CASE WHEN count(*) = 0 THEN 0 ELSE sum((1-coalesce(s.null_frac,0))*coalesce(s.avg_width, 2048))::int END AS datahdr, max(attrelid) AS attrelid FROM pg_attribute s JOIN pg_stats y ON s.attrelid=y.tableoid AND s.attnum=y.attnum WHERE s.attnum > 0 GROUP BY s.attrelid ) AS g ON s.attrelid=g.attrelid WHERE s.attnum > 0 GROUP BY 1,2,3,4,5,6,7,8,9,10 ) AS foo ) AS rs JOIN pg_class cc ON cc.oid=rs.oid JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema') AS smlORDER BY wastedbytes DESC;This query will highlight tables that may benefit from vacuuming or reindexing due to bloat.Manual Vacuum and Analyze:If specific tables are identified as bloated, consider manually vacuuming or analyzing them:VACUUM (VERBOSE, ANALYZE) table_name;For critical production systems, use VACUUM without FULL to avoid exclusive locks.Monitor Vacuum Processes:To monitor ongoing vacuum processes:SELECT pid, phase, relation::regclass, query, age(clocktimestamp(), querystart) AS runningforFROM pgstat_activityWHERE query ~* '^autovacuum:' AND state = 'active';

These actions, taken immediately upon noticing vacuum issues, can help in identifying and mitigating the root causes of the problems.