SQL FREEZE

What does the SQL FREEZE option do?

FREEZE is a PostgreSQL VACUUM option that permanently marks all eligible table rows as frozen, eliminating the need to re-freeze them in future maintenance cycles.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FREEZE: PostgreSQL: Yes MySQL: No SQL Server: No Oracle: No SQLite: No Standard SQL: Not in standard

SQL FREEZE Full Explanation

In PostgreSQL, every row version (tuple) carries a creation transaction ID (XID). As databases age, those XIDs risk wrapping around, potentially corrupting data visibility. Normal VACUUM operations periodically mark very old tuples as frozen so they are skipped in the wraparound calculation. Specifying FREEZE forces VACUUM to treat *all* tuples that are visible to all active transactions as old enough to be frozen, even if they would not yet qualify under the autovacuum_freeze_min_age threshold.Once a tuple is frozen, its XID is replaced with a special FrozenXID constant. Frozen tuples are thereafter immune to wraparound problems, so future VACUUM runs can skip them, reducing maintenance work. However, FREEZE scans the entire table and can generate extra I/O, which is why it is typically used during low-traffic periods or after large data loads.FREEZE can be written in two syntaxes:1. Legacy: VACUUM FREEZE [FULL] [ANALYZE] table_name;2. Parameter style: VACUUM (FREEZE [boolean], FULL, ANALYZE) table_name;Important caveats:- FREEZE still obeys MVCC rules and will not touch tuples that are either not yet visible to every transaction or are still required by any active snapshot.- Although FREEZE reduces future vacuum effort, it does not make further VACUUMs unnecessary; it only exempts already-frozen tuples.- FREEZE has no effect on indexes beyond the usual VACUUM processing.

SQL FREEZE Syntax

-- Simple form
VACUUM FREEZE table_name;

-- Parameter style
VACUUM (FREEZE true) table_name;

SQL FREEZE Parameters

Example Queries Using SQL FREEZE

-- Freeze newly bulk-loaded table during a maintenance window
VACUUM FREEZE orders_2024_q1;

-- Combine with FULL and ANALYZE for one-time deep cleanup
VACUUM FULL FREEZE ANALYZE users_archive;

Expected Output Using SQL FREEZE

PostgreSQL scans the specified table, rewrites any pages that contain tuples eligible for freezing, updates visibility maps, and reports statistics at completion
Application queries continue normally except for the usual VACUUM locking rules (ACCESS SHARE)

Use Cases with SQL FREEZE

After bulk data loads to mark rows as frozen before the table goes into read-mostly mode
During major version upgrades or dump/restore cycles
In data warehousing partitions that will become immutable
Preventive maintenance for very large tables close to autovacuum wraparound limits

Common Mistakes with SQL FREEZE

Assuming FREEZE removes the need for future VACUUMs altogether
Running FREEZE during peak workload and suffering unnecessary I/O contention
Forgetting that FREEZE still requires an ACCESS SHARE lock, which can be blocked by long-running transactions holding ACCESS EXCLUSIVE locks

First Introduced In

PostgreSQL 8.2

Frequently Asked Questions

Does FREEZE lock the table?

VACUUM FREEZE only takes an ACCESS SHARE lock, so normal SELECT, INSERT, UPDATE, and DELETE operations proceed. It blocks operations that need stronger locks such as certain ALTER TABLE commands.

When is VACUUM FREEZE recommended?

Use it after bulk inserts, before archiving partitions, or when a table's age approaches autovacuum_freeze_max_age to avoid emergency wraparound vacuums.

Can I combine FREEZE with other VACUUM options?

Yes. You can pair FREEZE with FULL or ANALYZE either in the legacy syntax (VACUUM FULL FREEZE) or the option list syntax (VACUUM (FULL, FREEZE, ANALYZE)).

Does FREEZE eliminate future VACUUMs?

No. It only exempts the frozen tuples. Routine VACUUM is still required for new or updated rows to reclaim space and maintain visibility maps.