Differences

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

--- postgresqlvacuum [2022/02/13 22:59] – z0hpvk
+++ postgresqlvacuum [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 27: / Line 27: @@
 ==== Vacuum Progress ====
+Will not display the progress of VACUUM FULL commands.
 <code SQL>
-SELECT
+SELECT p.pid, now() - a.xact_start AS duration,
-p.pid,
+       coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
-now() - a.xact_start AS duration,
+       CASE
-coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
+         WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
-CASE
+         WHEN a.query ~*'^vacuum' THEN 'user'
-WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
+         ELSE 'regular'
-WHEN a.query ~*'^vacuum' THEN 'user'
+       END AS mode,
-ELSE 'regular'
+       p.relid::regclass AS table, p.phase,
-END AS mode,
+       pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
-p.datname AS database,
+       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
-p.relid::regclass AS table,
+       pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
-p.phase,
+       pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
-pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
+       round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
-pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
+       round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
-pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
+       round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
-pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
+  FROM pg_stat_progress_vacuum p
-round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
+  JOIN pg_stat_activity a using (pid)
-round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
-p.index_vacuum_count,
-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>
@@ Line 57: / Line 53: @@
 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]]\\