# Part 2: Reproducing and Diagnosing Autovacuum Failures

> 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 hal…

_Series Summary: This is Part 2 of a_ [_multi-part series_](<https://www.commandprompt.com/blog/tag/autovacuum-failure-blog-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

* * *

## 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.

---
[View this page online](https://www.commandprompt.com/blog/part-2-reproducing-and-diagnosing-autovacuum-failures/)