Differences

This shows you the differences between two versions of the page.

--- postgresqlvacuum [2021/09/18 20:22] – created z0hpvk
+++ postgresqlvacuum [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+===== Vacuuming =====
+==== Parameters ====
 <code>
+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.
+</code>
+==== Dead Rows ====
+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.\\
+<code SQL>
 select relname, n_live_tup, n_dead_tup,
        floor(current_setting('autovacuum_vacuum_threshold')::integer +
-	        (current_setting('autovacuum_vacuum_scale_factor')::numeric *
+	    (current_setting('autovacuum_vacuum_scale_factor')::numeric *
-			(n_live_tup))) as "Autovacuum Threshold",
+	    (n_live_tup))) as "Autovacuum Threshold",
        round(n_dead_tup / floor(current_setting('autovacuum_vacuum_threshold')::integer +
-	                     (current_setting('autovacuum_vacuum_scale_factor')::numeric *
+	                       (current_setting('autovacuum_vacuum_scale_factor')::numeric *
-			             (n_live_tup)))*100,2) as "Percentage",
+		               (n_live_tup)))*100,2) as "Percentage",
        last_vacuum, last_autovacuum
 from pg_stat_user_tables
@@ Line 12: / Line 25: @@
 order by 5 desc limit 10;
 </code>
+==== Vacuum Progress ====
+Will not display the progress of VACUUM FULL commands.
+<code SQL>
+SELECT p.pid, now() - a.xact_start AS duration,
+       coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
+       CASE
+         WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
+         WHEN a.query ~*'^vacuum' THEN 'user'
+         ELSE 'regular'
+       END AS mode,
+       p.relid::regclass AS table, p.phase,
+       pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
+       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
+       pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
+       pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
+       round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
+       round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
+       round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
+  FROM pg_stat_progress_vacuum p
+  JOIN pg_stat_activity a using (pid)
+ORDER BY now() - a.xact_start DESC;
+</code>
+==== Table Bloat ====
+Table bloat can be fixed by performing a "VACUUM FULL" on the table.\\
+This will create a new unfragmented table and rebuild all associated indexes.\\
+However this will cause a full lock on the table, even SELECT queries will have to wait for the process to complete.\\
+Instead you can use the extension [[https://dbwiki.co.uk/postgresqlextensions#pg_repack|pg_repack]]. \\
+The basis of the below SQL query was taken from: [[https://github.com/ioguix/pgsql-bloat-estimation]]\\
+<code SQL>
+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,
+AS page_hdr,
++ 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;
+</code>