DB Wiki

The following SQL code displays the thresholds for when Autovacuum will clean up dead rows.
Dead rows (or tuples) occur in postgres when records are updated or deleted.

SELECT relname, n_live_tup, n_dead_tup, 
       FLOOR(current_setting('autovacuum_vacuum_threshold')::INTEGER + 
	    (current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * 
	    (n_live_tup))) AS "Autovacuum Threshold",
       round(n_dead_tup / FLOOR(current_setting('autovacuum_vacuum_threshold')::INTEGER + 
	                       (current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * 
		               (n_live_tup)))*100,2) AS "Percentage",
       last_vacuum, last_autovacuum 
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY 5 DESC LIMIT 10;

autovacuum_analyze_scale_factor       | Number of DML prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold          | Minimum number of DML prior to analyze.
autovacuum_vacuum_scale_factor        | Number of DML prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold           | Minimum number of tuple DML prior to vacuum.

SELECT current_database(), tblname, 
       round(bs*tblpages/1024/1024,0) AS Table_size_mb,
       round(CAST((tblpages - est_tblpages)*bs/1024/1024 AS NUMERIC),0) AS bloat_size_mb,
  CASE WHEN tblpages - est_tblpages > 0
    THEN round(CAST(100 * (tblpages - est_tblpages)/tblpages AS NUMERIC),0)
    ELSE 0
  END AS extra_pct
FROM (
  SELECT CEIL( reltuples / ( (bs-page_hdr)/tpl_size ) ) + CEIL( toasttuples / 4 ) AS est_tblpages,
         tblpages, bs, schemaname, tblname, heappages, toastpages
  FROM (
    SELECT
      ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
        - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
        - CASE WHEN CEIL(tpl_data_size)::int%ma = 0 THEN ma ELSE CEIL(tpl_data_size)::int%ma END
      ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
      toastpages, reltuples, toasttuples, bs, page_hdr, schemaname, tblname
    FROM (
      SELECT
        ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
        tbl.relpages AS heappages, COALESCE(toast.relpages, 0) AS toastpages,
        COALESCE(toast.reltuples, 0) AS toasttuples,
        current_setting('block_size')::NUMERIC AS bs,
        CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
        24 AS page_hdr,
        23 + CASE WHEN MAX(COALESCE(s.null_frac,0)) > 0 THEN ( 7 + COUNT(s.attname) ) / 8 ELSE 0::INT END
           + CASE WHEN bool_or(att.attname = 'oid' AND att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
        SUM( (1-COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 0) ) AS tpl_data_size
      FROM pg_attribute AS att
        JOIN pg_class AS tbl ON att.attrelid = tbl.oid
        JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
        LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
          AND s.tablename = tbl.relname AND s.inherited=FALSE AND s.attname=att.attname
        LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
      WHERE NOT att.attisdropped
        AND tbl.relkind IN ('r','m')
      GROUP BY 1,2,3,4,5,6,7,8,9
    ) AS s
  ) AS s2
) AS s3
WHERE schemaname IN ('public')
  AND (tblpages - est_tblpages)*bs > 1024000
ORDER BY (tblpages - est_tblpages)*bs DESC;