Resolving PostgreSQL VACUUM FULL Table Locks Blocking SELECT Queries on Ubuntu 20.04 LTS

Diagnose and resolve critical PostgreSQL performance bottlenecks caused by VACUUM FULL acquiring ACCESS EXCLUSIVE locks, blocking your SELECT queries on Ubuntu 20.04 LTS.

A VACUUM FULL operation in PostgreSQL is a powerful maintenance command designed to reclaim disk space and defragment tables. However, unlike a regular VACUUM, VACUUM FULL acquires an ACCESS EXCLUSIVE lock on the target table, making it inaccessible for any other operations, including simple SELECT queries, until it completes. This guide details how to identify, resolve, and prevent situations where a VACUUM FULL operation causes critical service outages by blocking your application's read queries on Ubuntu 20.04 LTS.

Symptom & Error Signature

When a VACUUM FULL locks a critical table, your applications will experience delays, timeouts, or outright failures when attempting to query that table. This often manifests as:

1. Application Errors:

   # Example application log error (e.g., Python psycopg2)
   psycopg2.errors.QueryCanceled: canceling statement due to user request
   FATAL:  terminating connection because of a database system shutdown
   psycopg2.errors.DeadlockDetected: deadlock detected
   

   Or a generic query timeout error:

   # Example application log error
   ERROR:  canceling statement due to statement timeout
   SQLSTATE: 57014
   DETAIL:  A long-running query was automatically canceled by the database.
   

2. PostgreSQL Log Entries (if query cancellation/termination occurs):

   LOG:  process 12345 still waiting for ShareLock on transaction 67890 after 1000.000 ms
   DETAIL:  Process 12345: SELECT * FROM public.my_locked_table;
   CONTEXT:  while checking for transaction 67890
   LOG:  could not send data to client: Broken pipe
   FATAL:  terminating connection because of a database system shutdown
   

   Or, more commonly, just slow queries/timeouts with no explicit lock messages if the application layer times out first.

3. Direct Observation via pg_stat_activity: You'll see a process with wait_event_type = 'Lock' and wait_event = 'relation' or similar, often with state = 'active' for both the VACUUM FULL and the blocked SELECT queries.

   -- Connect to PostgreSQL using psql:
   -- psql -U your_user -d your_database
   
   SELECT
       pid,
       usename,
       application_name,
       client_addr,
       backend_start,
       state,
       state_change,
       wait_event_type,
       wait_event,
       query_start,
       xact_start,
       query,
       backend_type
   FROM pg_stat_activity
   WHERE state = 'active'
   ORDER BY query_start;
   

   Output will show something like:

   pid   | usename | application_name | client_addr | backend_start             | state  | state_change              | wait_event_type | wait_event | query_start               | xact_start                | query                           | backend_type
   -------+---------+------------------+-------------+---------------------------+--------+---------------------------+-----------------+------------+---------------------------+---------------------------+---------------------------------+--------------
   12345 | admin   | psql             |             | 2026-07-27 10:00:05.1234  | active | 2026-07-27 10:00:05.1234  | Lock            | relation   | 2026-07-27 10:00:10.5678  | 2026-07-27 10:00:10.5678  | VACUUM FULL public.my_table;    | client backend
   12346 | appuser | my_app           | 10.0.0.10   | 2026-07-27 10:01:01.9876  | active | 2026-07-27 10:01:05.1122  | Lock            | relation   | 2026-07-27 10:01:05.1122  | 2026-07-27 10:01:05.1122  | SELECT id, name FROM public.my_table WHERE ...; | client backend
   12347 | appuser | my_app           | 10.0.0.11   | 2026-07-27 10:01:02.1234  | active | 2026-07-27 10:01:05.1234  | Lock            | relation   | 2026-07-27 10:01:05.1234  | 2026-07-27 10:01:05.1234  | SELECT COUNT(*) FROM public.my_table; | client backend
   (3 rows)
   

Root Cause Analysis

The root cause lies in the locking behavior of the VACUUM FULL command.

1. ACCESS EXCLUSIVE Lock: Unlike a standard VACUUM (which works concurrently with other operations), VACUUM FULL acquires an ACCESS EXCLUSIVE lock on the table it's processing. This is the strongest lock level in PostgreSQL, preventing all other access, including SELECT, INSERT, UPDATE, DELETE, and DDL operations. It needs this lock because it physically rewrites the entire table to a new file, freeing up unused space more aggressively than a regular VACUUM.

2. Blocking SELECT Queries: When VACUUM FULL holds this ACCESS EXCLUSIVE lock, any subsequent SELECT query attempting to read from that table will be queued and blocked until the VACUUM FULL completes and releases the lock. If the VACUUM FULL operation takes a long time (e.g., on a very large table with significant bloat), these blocked SELECT queries will accumulate, eventually leading to application timeouts, connection exhaustion, and a complete service outage.

3. Misuse/Misunderstanding of VACUUM FULL: VACUUM FULL is often used when a standard VACUUM (or the autovacuum daemon) isn't sufficient to reclaim disk space, typically due to significant table bloat. However, its disruptive nature means it should be used judiciously and almost exclusively during scheduled maintenance windows when the affected table can tolerate downtime. Many users, especially those new to PostgreSQL, might run it manually without understanding its locking implications.

4. Long-Running Transactions: Even if VACUUM FULL is initiated, it might itself get blocked by a pre-existing long-running transaction (e.g., an uncommitted INSERT or UPDATE transaction) that holds a less restrictive lock on the table. In such a scenario, the VACUUM FULL would be waiting, and then subsequent SELECT queries would be blocked behind the VACUUM FULL after it eventually acquires its lock.

Step-by-Step Resolution

Addressing an ongoing VACUUM FULL lock involves identifying the culprit and, if necessary, terminating the operation, followed by implementing preventative measures.

1. Identify the Blocking VACUUM FULL Process

First, connect to your PostgreSQL database using psql:

sudo -u postgres psql -d your_database_name

Then, execute the following query to list all active processes, paying close attention to the query and state columns, as well as wait_event_type and wait_event.

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    state_change,
    wait_event_type,
    wait_event,
    query_start,
    xact_start,
    query,
    backend_type
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

Look for a process where query contains VACUUM FULL and other processes are waiting (e.g., wait_event_type = 'Lock' and wait_event related to relation). Note the pid of the VACUUM FULL process.

You can also use pg_locks to get a more granular view of held and waiting locks:

SELECT
    a.pid,
    a.usename,
    a.application_name,
    a.client_addr,
    a.query,
    l.locktype,
    l.mode,
    l.granted
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
WHERE a.state = 'active' AND l.granted = false
ORDER BY a.query_start;

This query will explicitly show you queries that are waiting (granted = false) for a lock. You might see a SELECT query waiting for an AccessShareLock that is currently being held or requested by another query, and tracing back the VACUUM FULL as the ultimate blocker.

2. Gracefully Cancel the VACUUM FULL

If possible, always try to cancel a query gracefully first. This sends a SIGINT equivalent signal to the backend process, allowing it to clean up before terminating.

SELECT pg_cancel_backend(12345); -- Replace 12345 with the PID of the VACUUM FULL

After executing this, re-run the pg_stat_activity query to confirm if the VACUUM FULL process has terminated and if the blocked SELECT queries are now progressing.

Cancelling VACUUM FULL will discard all progress made by that operation. The table will remain in its pre-VACUUM FULL state of bloat, and the reclaimed disk space will not be released. This is often an acceptable trade-off to restore service availability immediately.

3. Force-Terminate the VACUUM FULL (If Cancellation Fails)

If pg_cancel_backend() does not work (e.g., the process is unresponsive or stuck), you may need to force-terminate the backend. This is a more aggressive action that sends a SIGTERM equivalent signal.

SELECT pg_terminate_backend(12345); -- Replace 12345 with the PID of the VACUUM FULL

Using pg_terminate_backend() can potentially lead to an incomplete transaction or leave temporary files behind, requiring database recovery processes. While PostgreSQL is generally resilient, this should be a last resort. Always monitor your logs closely after termination.

Once the VACUUM FULL process is terminated, the ACCESS EXCLUSIVE lock will be released, and all waiting SELECT queries should immediately proceed. Monitor your application logs and pg_stat_activity to confirm service restoration.

4. Implement Preventative Measures and Alternatives

To avoid future outages, adopt a strategy that minimizes the need for VACUUM FULL and schedules it carefully when unavoidable.

a. Prefer Regular VACUUM and autovacuum

Regular VACUUM (without FULL) and the autovacuum daemon reclaim space by marking dead tuples as reusable. They operate concurrently with other queries and do not acquire ACCESS EXCLUSIVE locks.

Ensure autovacuum is properly configured and running efficiently. Check postgresql.conf:

# /etc/postgresql/12/main/postgresql.conf (or 13/main)
autovacuum = on               # Enable autovacuum (default is on)
log_autovacuum_min_duration = 0 # Log all autovacuum actions (useful for tuning)
autovacuum_max_workers = 3    # Number of autovacuum processes (default: 3)
autovacuum_vacuum_cost_delay = 10ms # Delay between vacuum cycles (default: 2ms)
autovacuum_vacuum_cost_limit = -1 # Cost limit per worker (default: -1, means use vacuum_cost_limit)

Adjust these parameters based on your workload and hardware. Monitor pg_stat_user_tables to identify tables with high n_dead_tup that aren't being vacuumed sufficiently.

b. Utilize pg_repack for Online Bloat Reduction

For significant table bloat that requires space reclamation without downtime, consider using pg_repack. This extension rewrites tables and indexes online, holding only SHARE UPDATE EXCLUSIVE locks for short periods (during the initial CREATE TABLE AS and final ALTER TABLE SWAP phases), which allows SELECT queries to continue.

Installation (Ubuntu 20.04 – PostgreSQL 12/13):

sudo apt update
sudo apt install postgresql-12-repack  # For PG 12
# OR
sudo apt install postgresql-13-repack  # For PG 13

Then, connect to your database and install the extension:

CREATE EXTENSION pg_repack;

To use it for a table:

pg_repack --dbname=your_database_name --table=public.my_bloated_table

Or for the entire database (use with extreme caution, still has brief lock periods):

pg_repack --dbname=your_database_name

pg_repack requires sufficient free disk space (at least double the size of the table being repacked) to create a copy of the table.

c. Schedule VACUUM FULL for Maintenance Windows

If VACUUM FULL is absolutely necessary (e.g., after a large data deletion, or when pg_repack is not an option), schedule it during periods of low application traffic or during a planned maintenance window where downtime is acceptable.

You can create a systemd timer or cron job to automate this.

Example cron job:

# Edit crontab for the postgres user
sudo -u postgres crontab -e

# Add a line to run VACUUM FULL on a specific table at 3 AM daily
0 3 * * * psql -d your_database_name -c "VACUUM FULL public.my_critical_table;" >> /var/log/postgresql/vacuum_full.log 2>&1

Always test VACUUM FULL operations and their impact in a staging or development environment before running them on a production database.

d. Optimize Long-Running Transactions

Identify and optimize application queries or database transactions that hold locks for extended periods. Long-running transactions can block VACUUM operations, which in turn can lead to bloat that later necessitates VACUUM FULL.

Use pg_stat_activity to find long-running transactions:

SELECT
    pid,
    usename,
    application_name,
    xact_start,
    state,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction' OR xact_start IS NOT NULL
ORDER BY xact_start;

Review your application code to ensure transactions are committed or rolled back promptly.

By understanding the locking mechanisms of PostgreSQL and employing these best practices, you can prevent VACUUM FULL operations from causing critical outages and maintain high availability for your services.