Resolving PostgreSQL VACUUM FULL Table Locks Blocking Queries on CentOS Stream / Rocky Linux

Diagnose and safely resolve critical table locks caused by VACUUM FULL operations in PostgreSQL, preventing query blocks on CentOS Stream and Rocky Linux.

Introduction

As a seasoned Systems Administrator managing production PostgreSQL databases, encountering prolonged table locks that bring your application to a crawl is a critical incident. One of the most common culprits for severe, application-wide database freezes is an ill-timed or improperly executed VACUUM FULL command. Unlike a regular VACUUM, VACUUM FULL acquires an ACCESS EXCLUSIVE lock on the target table, preventing all other operations, including simple SELECT queries, until it completes. This guide will walk you through diagnosing, safely resolving, and preventing such occurrences on CentOS Stream and Rocky Linux systems.

Symptom & Error Signature

Users will experience slow or unresponsive applications, eventually leading to timeouts. On the application side, you might see errors indicating a cancelled query or an inability to acquire a lock. From the database perspective, pg_stat_activity will reveal an active VACUUM FULL operation holding an ACCESS EXCLUSIVE lock, with many other queries in a waiting state.

Typical Application Error (e.g., Python psycopg2):

psycopg2.errors.QueryCanceled: canceling statement due to statement timeout

PostgreSQL Log Entries (often accompanied by context from the application):

LOG:  duration: 120000.000 ms statement: SELECT * FROM my_large_table WHERE id = 123;
ERROR:  canceling statement due to statement timeout
DETAIL:  Statement has been running for too long.

Identifying the Blocking Process using ps and pg_stat_activity:

You might first notice a long-running postgres process consuming resources, but pg_stat_activity is the definitive tool.

# From the OS, you might see a process, but it's not specific enough
$ ps aux | grep postgres | grep "vacuum full"
postgres 12345 99.9  0.5 1234567 87654 ?        Rs   10:00   1:34:56 vacuum full public.my_large_table

-- Inside psql, this is the primary diagnostic query
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    wait_event_type,
    wait_event,
    query_start,
    query
FROM pg_stat_activity
WHERE state = 'active'
AND query ILIKE '%VACUUM FULL%'
ORDER BY query_start;

Expected output showing the VACUUM FULL and other queries waiting:

  pid  |  usename   | application_name |  client_addr   |      backend_start      |  state  | wait_event_type |  wait_event   |          query_start          |                           query
-------+------------+------------------+----------------+-------------------------+---------+-----------------+---------------+-------------------------------+-------------------------------------------
 12345 | postgres   | psql             |                | 2023-10-26 10:00:05.123 | active  | Client           | ClientRead    | 2023-10-26 10:00:10.456 | VACUUM FULL public.my_large_table;
 12346 | app_user   | MyWebApp         | 192.168.1.100  | 2023-10-26 10:01:00.789 | waiting | Lock            | relation      | 2023-10-26 10:01:05.111 | SELECT id, name FROM public.my_large_table WHERE status = 'active';
 12347 | app_user   | MyWebApp         | 192.168.1.101  | 2023-10-26 10:01:10.123 | waiting | Lock            | relation      | 2023-10-26 10:01:15.444 | UPDATE public.my_large_table SET last_seen = NOW() WHERE id = 123;
(3 rows)

In this output, pid 12345 is running the VACUUM FULL, and pid 12346 and 12347 are waiting for a Lock on the relation (table).

Root Cause Analysis

The root cause of this issue lies in the locking behavior of the VACUUM FULL command in PostgreSQL:

1. ACCESS EXCLUSIVE Lock Acquisition: When VACUUM FULL runs on a table, it requests and holds an ACCESS EXCLUSIVE lock on that table. This is the strictest lock level in PostgreSQL.
2. Complete Blockage: An ACCESS EXCLUSIVE lock prevents any other access to the table. This includes all SELECT, INSERT, UPDATE, DELETE, ALTER TABLE, and other VACUUM operations.
3. Purpose of VACUUM FULL: VACUUM FULL is designed to aggressively reclaim disk space by rewriting the entire table and associated indexes to a new file, freeing up space at the operating system level. It's often misunderstood as a routine maintenance task.
4. Misapplication: It is rarely needed for routine maintenance. Regular VACUUM (often managed by autovacuum) handles tuple visibility and allows space reuse within the table files, but it does not shrink the file size on disk. VACUUM FULL is only justified in specific scenarios, such as:
   • After a massive deletion of rows from a table where significant disk space needs to be returned to the OS, and other methods (like pg_repack) are not feasible.
   • In a maintenance window where application downtime is acceptable.
5. Long-Running Operations: On large tables, VACUUM FULL can take hours or even days to complete, leading to prolonged application outages if run during peak hours.
6. Concurrency Issues: Even if VACUUM FULL is initiated, if there are any existing ACCESS SHARE locks (e.g., from an active SELECT query) on the table, the VACUUM FULL will wait to acquire its ACCESS EXCLUSIVE lock. This means a long-running SELECT query could itself block the VACUUM FULL from even starting, only for the VACUUM FULL to block everything else once it does start.

Step-by-Step Resolution

Addressing a blocking VACUUM FULL requires careful action to minimize further disruption.

1. Safely Identify the Blocking Process and Blocked Queries

First, connect to your PostgreSQL database using psql. Ensure you connect as a superuser (e.g., postgres user).

sudo -u postgres psql

Then, execute the following query to identify all active and waiting queries, focusing on the VACUUM FULL operation and those waiting on it.

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    wait_event_type,
    wait_event,
    query_start,
    query,
    age(now(), query_start) AS query_duration
FROM pg_stat_activity
WHERE state IN ('active', 'waiting')
ORDER BY wait_event IS NOT NULL DESC, query_start;

Note down the pid of the VACUUM FULL process. This is the process you will need to target for termination. Also, observe the query_duration to gauge how long it has been running.

2. Assess the Impact and Decision to Terminate

Before terminating, consider:

• How long has it been running? If it's only been a few minutes on a small table, it might finish soon.
• How critical is current application uptime? If the application is completely down, termination is likely the best course.
• What table is it running on? Terminating a VACUUM FULL will roll back its transaction, which might take some time itself, proportional to the work done. The table will remain in its original state.

Terminating a VACUUM FULL will roll back the operation. This means any disk space it would have reclaimed will not be reclaimed. The table will remain in its pre-VACUUM FULL state.

3. Gracefully Cancel the Blocking VACUUM FULL

The preferred method to stop a long-running query is pg_cancel_backend(). This sends a SIGINT to the backend process, requesting it to terminate gracefully. The backend will attempt to stop its current operation and roll back its transaction.

-- Replace <PID_OF_VACUUM_FULL> with the actual PID identified in step 1.
SELECT pg_cancel_backend(<PID_OF_VACUUM_FULL>);

After executing, wait a few moments and re-run the pg_stat_activity query from Step 1.

• If the VACUUM FULL process disappears, it was successfully cancelled.
• If it's still there but state has changed (e.g., to idle in transaction or aborting), it's in the process of rolling back.
• If it remains active with the same query, it might be stuck or ignoring the cancel request.

4. Forcefully Terminate the Blocking VACUUM FULL (If Cancellation Fails)

If pg_cancel_backend() does not work after a reasonable wait time (e.g., 1-5 minutes, depending on the workload), you may need to forcefully terminate the backend using pg_terminate_backend(). This sends a SIGTERM to the backend, which is a stronger signal. The backend will immediately disconnect, and its transaction will be aborted and rolled back.

Use pg_terminate_backend() with caution. While generally safe for the database's integrity (PostgreSQL's crash recovery ensures consistency), it immediately kills the backend process and can take longer for rollback on very large transactions. Ensure you target the correct PID.

-- Replace <PID_OF_VACUUM_FULL> with the actual PID identified in step 1.
SELECT pg_terminate_backend(<PID_OF_VACUUM_FULL>);

After termination, monitor your database logs and pg_stat_activity to ensure the process is gone and other queries resume normal operation.

5. Prevent Future VACUUM FULL Lockdowns

Prevention is key. VACUUM FULL should almost never be run in a production environment without a dedicated maintenance window.

a. Understand Autovacuum

PostgreSQL's autovacuum daemon handles routine VACUUM and ANALYZE operations automatically. Ensure it's enabled and correctly configured. This daemon performs regular VACUUM operations that do not acquire ACCESS EXCLUSIVE locks and are non-blocking.

Check postgresql.conf:

autovacuum = on

Also, review autovacuum_vacuum_cost_delay and related settings. A common tuning point is reducing the delay for busier tables to allow autovacuum to run more frequently and efficiently.

b. Use pg_repack for Online Table Rewrites

For reclaiming disk space without downtime, consider pg_repack. pg_repack is an external tool that reorganizes PostgreSQL tables and indexes with minimal locking, similar to VACUUM FULL but online.

# On CentOS Stream / Rocky Linux, install pg_repack:
sudo dnf install pg_repack<version>_14 # For PostgreSQL 14, adjust version as needed
# Or, if using EPEL:
sudo dnf install pg_repack

Then, you can use it like this (example for database mydb, table my_large_table):

pg_repack -d mydb -t my_large_table

pg_repack still requires certain locks for brief periods, but it is far less disruptive than VACUUM FULL. Always test on a staging environment first.

c. Schedule VACUUM FULL During Maintenance Windows Only

If VACUUM FULL is absolutely necessary, schedule it during known downtime or low-activity periods. This might involve:

• Stopping application servers.
• Notifying users of planned maintenance.
• Performing the operation.
• Restarting applications.

d. Review and Audit Scheduled Jobs

Check any cron jobs, scripts, or database maintenance tools that might be inadvertently running VACUUM FULL. Remove or modify these to use VACUUM (without FULL) or pg_repack instead.

# Check system-wide cron jobs
sudo grep -r "VACUUM FULL" /etc/cron.* /var/spool/cron/
# Check for scripts in common locations
sudo find /opt /usr/local -name "*.sh" -exec grep -l "VACUUM FULL" {} +

By understanding the nature of VACUUM FULL and leveraging appropriate tools and practices, you can prevent these critical table lock situations and maintain high availability for your PostgreSQL databases on CentOS Stream or Rocky Linux.