Find PostgreSQL Tables That Need VACUUM FREEZE

calendar Apr 2, 2026 / · 3 min read · postgresql administration vacuum maintenance database  ·
Share on: linkedin copy

Find PostgreSQL Tables That Need VACUUM FREEZE

PostgreSQL uses transaction IDs (XIDs) to track which rows are visible to which transactions. Over time, these IDs age. When a table's oldest unfrozen XID gets too old, PostgreSQL triggers an aggressive autovacuum to freeze it. This is controlled by the vacuum_freeze_table_age setting (default: 150 million transactions).

If a table's XID age exceeds this limit, autovacuum will aggressively scan the entire table the next time it runs. This can cause sudden I/O spikes and slow down your database. It can also lead to transaction ID wraparound if left unmanaged.

This query shows you exactly which tables have exceeded the vacuum_freeze_table_age threshold so you can freeze them proactively.

Sample Code

 1SELECT
 2    c.oid::regclass AS table_name,
 3    greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age_transactions,
 4    current_setting('vacuum_freeze_table_age')::integer AS freeze_age_limit,
 5    (greatest(age(c.relfrozenxid), age(t.relfrozenxid)) > current_setting('vacuum_freeze_table_age')::integer) AS is_over_limit
 6FROM
 7    pg_class c
 8    LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 9WHERE
10    c.relkind IN ('r', 'm')
11    AND c.oid::regclass::text NOT LIKE 'pg_%'
12ORDER BY
13    age_transactions DESC;

Notes: Works on PostgreSQL 9.4 and later. Covers both regular tables (r) and materialized views (m). Includes TOAST tables, which store large column values and have their own XID age. System tables (prefixed with pg_) are excluded.

Code Breakdown

  • c.oid::regclass — Converts the table's OID to a human-readable table name.
  • age(c.relfrozenxid) — Returns how many transactions ago the main table was last frozen. A higher number means more unfrozen transactions.
  • age(t.relfrozenxid) — Same calculation for the table's TOAST table, which stores oversized column data separately.
  • GREATEST(...) — Takes the higher of the two ages (main table vs TOAST). Either can trigger a forced vacuum, so we track whichever is older.
  • current_setting('vacuum_freeze_table_age') — Reads the current threshold from PostgreSQL settings. Default is 150 million.
  • is_over_limit — A true/false flag. true means this table has already exceeded the threshold and will trigger aggressive autovacuum.
  • LEFT JOIN pg_class t ON c.reltoastrelid = t.oid — Joins to the TOAST table associated with each table, if one exists.
  • relkind IN ('r', 'm') — Filters to regular tables and materialized views only.

Key Points

  • Any row where is_over_limit = true means autovacuum will do a full table scan next time it runs on that table.
  • Results are sorted by age_transactions descending, so the most at-risk tables appear first.
  • Even if is_over_limit = false, tables with ages close to freeze_age_limit should be on your radar.
  • The TOAST table age can sometimes be higher than the main table age. This query accounts for both.

Insights and Explanations

When you see tables with is_over_limit = true, you have two options:

Option 1: Let autovacuum handle it. Autovacuum will freeze these tables eventually. The risk is that it does so during peak hours, causing performance impact.

Option 2: Freeze manually during off-peak hours. You can run this command on any table that is over the limit:

1VACUUM FREEZE ANALYZE your_table_name;

To find the current vacuum_freeze_table_age setting on your server:

1SHOW vacuum_freeze_table_age;

If you are seeing many tables over the limit regularly, autovacuum may not be running frequently enough. Check these settings:

1SELECT name, setting, unit
2FROM pg_settings
3WHERE name IN (
4    'autovacuum_vacuum_cost_delay',
5    'autovacuum_naptime',
6    'vacuum_freeze_table_age',
7    'autovacuum_freeze_max_age'
8);

A high autovacuum_vacuum_cost_delay can throttle autovacuum so much that it falls behind on busy databases.

Additional Considerations

  • Permissions: Any user with SELECT on pg_class can run this query. That includes most database users by default.
  • Materialized views: Materialized views also age like tables. If you refresh them frequently, they are less likely to be a problem, but they are worth monitoring.
  • Long-running transactions: A long-running open transaction can block autovacuum from freezing rows. If this query shows many tables over the limit and they are not being cleared, check for long-running transactions with pg_stat_activity.

References

Posts in this series