How to Perform Full VACUUM in PostgreSQL

December 5, 2023

Their are two types of vacuum in PostgreSQL – ordinary vacuum and vacuum full. In PostgreSQL dead tuple is created when you delete or update any records in a table. Normal vacuum simply reclaims space and makes it available for re-use. However, In most cases, extra space is not returned back to the operating system.

Whereas, Vacuum Full release the free space occupied by dead tuple or obsolete records to the disk. This method require extra disk space because it write new copy of the table and doesn’t release the old copy until the operation is completed. This should use only if dead tuple is huge or a significant amount of space needs to be reclaimed from within the table. Please note that it acquire lock on the table and will be released once the operation is completed. So it’s better to bring down the application or processes accessing the table and then execute it.

Full VACUUM query in PostgreSQL to remove dead tuple and release disk space.

Sometimes, you will observe database performance has improved drastically after full vacuum. You can use below query to check dead tuple and for full vacuum activity.

SELECT 'VACUUM(FULL, ANALYZE,VERBOSE)  ' || table_oid::regclass || ';' ,
  /*current_database(), schemaname, */table_oid::regclass, /*reltuples::bigint, relpages::bigint, otta,*/
 -- ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes
 FROM (
  SELECT
    cc.reltuples, cc.relpages, bs, cc.oid as table_oid,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta
  FROM (
    SELECT
      ma,bs,starelid,
      (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
      (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        starelid, hdr, ma, bs,
        SUM((1-stanullfrac)*stawidth) AS datawidth,
        MAX(stanullfrac) AS maxfracsum,
        hdr+(
          SELECT 1+COUNT(*)/8
          FROM pg_statistic s2
          WHERE stanullfrac<>0 AND s2.starelid = s.starelid
        ) AS nullhdr
      FROM pg_statistic s, (
        SELECT
          (SELECT current_setting('block_size')::NUMERIC) AS bs,
          CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.oid = rs.starelid
  JOIN pg_namespace ns ON cc.relnamespace = ns.oid AND ns.nspname <> 'information_schema'
  WHERE cc.relkind IN('r','m')
) AS sml
JOIN pg_stat_all_tables sat ON sml.table_oid = sat.relid
--WHERE sat.schemaname like '%_DAT' 
--WHERE (CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END) > 1.1 and sat.schemaname like '%_ADM'
ORDER BY wastedbytes DESC;

Must Read: