Series Summary: This is Part 2 of a multi-part series on PostgreSQL autovacuum failures.

In Part 1, we introduced the scenario where autovacuum mysteriously halts in a multi-database PostgreSQL cluster. Now, we’ll reproduce the issue using a lightweight test setup. This walkthrough will help you see the failure in action and understand how quickly your system can degrade.

How to Reproduce the Problem (and Watch PostgreSQL Go Sideways)

Let’s set up a minimal test environment that demonstrates how this scenario plays out and can quickly put your PostgreSQL instance into an unhealthy state.

Step 0: Set up Postgresql to trigger the emergency vacuum event by using the following commands:

psql -d postgresql -c ‘ALTER SYSTEM SET vacuum_freeze_min_age TO 100000;’  
-- 100,000 is the lowest value freeze age can be set to
-- Restart PostgreSQL

For example, execute the following commands in two different open psql sessions.

Pro tip: keep the session open while running the code below.

Step 1: Create a database to hold the temp tables:

CREATE DATABASE temp_tables;
\c temp_tables;
CREATE TEMP TABLE  hi_there(id serial);
INSERT INTO hi_there VALUES (DEFAULT);

Step 2: Create a database that won’t get vacuumed:

CREATE DATABASE  not_vacuumed;
\c not_vacuumed;
CREATE TABLE please_vacuum(id integer);

Step 3: Create a procedure to burn up 100,000 transactions:

CREATE OR REPLACE PROCEDURE test_vacuum()
LANGUAGE PLPGSQL
AS $$
    DECLARE 
    _count integer = 0;
    BEGIN
        LOOP 
            BEGIN
                INSERT INTO please_vacuum VALUES(default);
                _count = _count + 1;
                COMMIT;
            END;
            EXIT WHEN _count > 100000;
        END LOOP;
    END;
$$
call test_vacuum();

To perform this in Bash instead of within PostgreSQL, run the following command:

for i in $(seq 1 10000); do psql -d not_vacuumed -c 'INSERT INTO please_vacuum VALUES(default);'; done;

Step 4: Confirm that VACUUM has not been initiated. Run the query below to verify that autovacuum has not started, even though vacuum_freeze_min_age has been exceeded:

SELECT
       oid::regclass::text AS table,
       age(relfrozenxid) AS xid_age, 
       mxid_age(relminmxid) AS mxid_age, 
       least( 
(SELECT setting::int
            FROM    pg_settings
            WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid), 
(SELECT setting::int
            FROM    pg_settings
            WHERE   name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)  
) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM    pg_class
WHERE   relfrozenxid != 0
relkind IN (‘r’,’t’)
ORDER BY tx_before_wraparound_vacuum;

In Part 3, we’ll go deeper, reviewing how PostgreSQL chooses which database to vacuum and what the logs (don’t) tell you.

⬅️ Back: Part 1 – When Autovacuum Silently Fails Across Databases

➡️ Next: Part 3 – Why Autovacuum Stops — Internal Mechanics Explained (coming June 24)

Need Help?

Command Prompt is the world’s oldest dedicated Postgres services and consulting company, offering expert support for performance optimization and troubleshooting. Contact us today.

Can't wait for the full series? Receive a link to download the full series as a white paper by submitting your contact information below.