Differences

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

--- postgresqlvacuum [2021/09/23 14:14] – 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.\\
@@ Line 16: / Line 25: @@
 order by 5 desc limit 10;
 </code>
-<code>
-autovacuum_analyze_scale_factor       | Number of DML prior to analyze as a fraction of reltuples.
+==== Vacuum Progress ====
-autovacuum_analyze_threshold          | Minimum number of DML prior to analyze.
+Will not display the progress of VACUUM FULL commands.
-autovacuum_vacuum_scale_factor        | Number of DML prior to vacuum as a fraction of reltuples.
+<code SQL>
-autovacuum_vacuum_threshold           | Minimum number of tuple DML prior to vacuum.
+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,