Differences

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

--- postgresqlvacuum [2021/09/23 14:15] – z0hpvk
+++ postgresqlvacuum [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 24: / Line 24: @@
 where n_live_tup > 0
 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,