Autovacuum freezing rows in PostgreSQL

October 13, 2025

A few months ago I wrote about tuning autovacuum in PostgreSQL. At that time I was mainly focussed on how autovacuum triggers for inserts/updates/deletes and speeding up autovacuum generally. Recently I observed an autovacuum for anti-wraparound occur which caused me to look into how that type of vacuum works.

Almost all of this information comes from the Postgres vacuuming docs. (I link to Postgres 17's docs as that's what we run at work currently and which I'm primarily engaged with).

What triggers autovacuum?

Autovacuum triggers a vacuum on a table for three reasons:

1. The number of dead rows goes beyond a threshold (rows that were updated or deleted).
2. The number of newly inserted rows goes beyond a threshold.
3. The age of rows that are not frozen goes beyond a threshold.

I am mainly concerned with the third here. You can see my prior post for information on the first two.

What kinds of autovacuum vacuums are there?

1. Regular vacuum
   • Pages modified since the last vacuum are scanned.
2. Aggressive vacuum
   • The same as regular vacuum plus all rows eligible for freezing are also scanned.
3. Anti-wraparound vacuum AKA vacuum to prevent transaction ID wraparound
   • The same as aggressive vacuum plus it does not stop if a query tries to take a conflicting lock. The first two kinds of vacuum stop if a query tries to take a conflicting lock.

When does vacuum freeze rows?

All kinds of vacuums freeze rows. Any time vacuum looks at a page it will freeze rows older than vacuum_freeze_min_age (50 million by default).

However as I mentioned above, not all vacuums look at all pages.

When autovacuum triggers a vacuum, it will normally be due to the dead row threshold or the inserted row threshold and these will normally be regular vacuums. Regular vacuums leave rows in the table unfrozen that are eligible for freezing since they won't look at pages that haven't changed since the last run.

These vacuums will be aggressive vacuums if the table has rows older than vacuum_freeze_table_age - vacuum_freeze_min_age (150 million - 50 million = 100 million by default). This causes vacuum to freeze all eligible rows since it will look at all relevant pages.

This means in general that tables receiving inserts/updates/deletes will periodically receive aggressive vacuums and have all eligible rows frozen.

However what happens if autovacuum doesn't trigger due to the insert/update/delete thresholds? This could happen for large tables where meeting the threshold takes a long time or for tables that do not receive much/any insert/update/delete activity. In this case Postgres freezes rows by triggering an anti-wraparound vacuum when a table has rows older than autovacuum_freeze_max_age (200 million by default). This will be an aggressive vacuum that looks at all pages.

What does this all mean?

Freezing happens all the time, even during regular vacuums. As well, all of a table's rows will typically get frozen periodically even without an anti-wraparound vacuum due to aggressive vacuums.

Anti-wraparound vacuums will occasionally happen for any workload and typically aren't a problem. The lock they take doesn't conflict with DML queries, but it can block DDL queries. Blocking DDL queries isn't ideal, but if you've tuned vacuum to run quickly, it likely isn't a problem (as long as you use appropriate lock_timeouts).

If you want to reduce the incidence of anti-wraparound vacuums in order to reduce blocking DDL queries, you can tune some of the settings I mentioned:

• You could increase autovacuum_freeze_max_age. This will make it more likely that an aggressive vacuum will freeze the rows before Postgres needs to launch an anti-wraparound vacuum. However, there is a risk that if other vacuums do not run for some reason that you end up with a table that needs a lot of vacuuming when this eventually triggers, making the problem worse instead of better.
• You could reduce vacuum_freeze_table_age so that regular vacuums will instead be aggressive more frequently. This will make it more likely that an aggressive vacuum will take care of freezing.
• You could decrease the dead row/inserted row thresholds so that Postgres triggers vacuums more often. This will also make it more likely that an aggressive vacuum will take care of freezing. You can see my previous post for more about this.