Running VACUUM FULL in Non-blocking Mode

OK, I got your attention. You’re probably thinking I must actually be referring to other tools like pg_repack. No I am not. I really am referring to running the VACUUM FULL command in non-blocking mode. WHAT!?#$%@!
Here we go:

1. Setup 2 psql session windows on the same screen.
2. The 1st window shows the state of active connections every few seconds (use \watch command) by running a query against the pg_stat_activity table to see if any of the active connections are waiting (wait_event and wait_event_type).
3. The 2nd window does the actual VACUUM FULL VERBOSE command for bloated tables, one at a time.
4. If waiting conditions show up in the 1st window, immediately cancel the vacuum in the second window (Ctrl-C), unless of course you know for sure that the waiting condition(s) have nothing to do with the vacuum full.
5. Proceed to vacuuming the next table.

OK, you’re right: vacuum full really does lock a table and can cause blocking conditions for other active transactions, but it can be minimized to the point of not really causing harm by just adding a couple extra seconds of wait for some other active transactions some of the time! Obviously this approach should be done with active monitoring as shown here, although perhaps you could do that programmatically, but I would not risk it. It does require that special PG DBA that can, without interruption, do a mundane, repetitive task over and over again without getting impatient.

Of course some tables won’t qualify for being defragmented this way due long vacuum full durations (very large tables) or conflicts. Those cases require special  tools like pg_repack that can be run programmatically/scheduling without taking an outage.

The major advantage with VACUUM FULL over other, true, non-blocking tools is time and cost. It is much faster and uses less resources to defragment with VACUUM FULL due to the nature of how other non-blocking defragmenters work to circumvent the locking problem.

The bloat candidates are determined by running one of the many bloat detection queries that are available for PG. Your choice on that. I personally like this one, pgsql-bloat-estimation by Jehan-Guillaume De Rorthais.

The monitoring window does the query once, following by the \watch command:


select coalesce(datname,'N/A') as datname, pid, coalesce(usename, 'N/A') as usename, substring(coalesce(application_name, 'N/A'),1,15) as app, wait_event, wait_event_type as we_type, CASE WHEN state = 'idle in transaction' THEN 'idle in trans' ELSE state END as state, (case when backend_type = 'logical replication launcher' then 'logical rep launcher' when backend_type = 'autovacuum launcher' then 'autovac launcher' when backend_type = 'autovacuum worker' then 'autovac wrkr' else backend_type end) as backend_type, to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS') as backend_start, coalesce(to_char(query_start, 'YYYY-MM-DD HH24:MI:SS'), 'N/A') as query_start, (case when state in ('active','idle in transaction') then cast(EXTRACT(EPOCH FROM (now() - query_start)) as integer) else -1 end) as seconds, regexp_replace(replace(regexp_replace(substring(query,1,65), E'[\\n\\r]+', ' ', 'g' ),' ',''), '[^\x20-\x7f\x0d\x1b]', '', 'g') as query from pg_stat_activity where state in ('active','idle in transaction') order by seconds;

\watch 3

— Michael Vitale,  Postgres DBA on Team Elephas