In PostgreSQL, what does COPY ... WITH (FREEZE) do, when is it allowed, and which MVCC rule does it break?
answer
- frozen at birth, not later
- created or truncated in this subtransaction
- visibility map bits set early
- snapshots that predate the load
basics
~20 sCOPY FREEZE writes rows already frozen and marks their pages all-visible and all-frozen, sparing later vacuum work. It needs the table created or truncated in the current subtransaction, and committed rows become visible even to older snapshots.
solid answer
~50 sFreshly loaded rows normally carry the loading transaction's ID, and later VACUUM work must revisit every page to set visibility-map bits and eventually freeze the rows: on 50 million rows, another pass that dirties the whole table. `FREEZE` inserts the rows already frozen and, for pages it fills from empty, sets the visibility map's all-visible and all-frozen bits. It is allowed only when the table was **created or truncated in the current subtransaction** and the transaction has no open cursors or older snapshots; otherwise it errors (SQLSTATE `55000` or `25000`). It is rejected on partitioned tables and, in PostgreSQL 18, on foreign tables, where 17 silently ignored it. The price is MVCC: once the load commits, a snapshot taken before it sees the rows anyway. With `TRUNCATE`, readers also wait on its `ACCESS EXCLUSIVE` lock until commit.
code
sql · 6 linesBEGIN;
TRUNCATE census_staging;
COPY census_staging
FROM '/srv/import/census_2026.csv'
WITH (FORMAT csv, HEADER, FREEZE);
COMMIT;go deeper
Recall that COPY FREEZE loads rows already frozen and only works on a table created or truncated in the same transaction.
Explain what freezing and the visibility map bits save, the subtransaction rule, why a SAVEPOINT breaks it, and the partitioned-table restriction.
Show judgement about the MVCC trade: older snapshots see the rows after commit, and TRUNCATE's ACCESS EXCLUSIVE lock blocks readers for the whole load.
Weigh deferred maintenance cost against isolation guarantees: where skipping the later freeze pass is worth letting long-running reports see data their snapshots predate.
## Why a fresh load is paid for twice Every PostgreSQL row version stores `xmin`, the ID of the transaction that inserted it. Until something records that the inserter committed and that the row is visible to everyone, readers must check commit status, and VACUUM must eventually do two more things to every page of a freshly loaded table: - set the page's **all-visible** bit in the **visibility map**, which index-only scans and later vacuums depend on; - **freeze** the rows, replacing their reliance on `xmin` so that transaction-ID wraparound can never make them invisible (the wraparound topic covers the details). On a 50-million-row census table that is a second pass that dirties, and writes out again, every page you just loaded. ## What FREEZE changes `COPY ... WITH (FREEZE)` inserts rows **already frozen**, "just as they would be after running `VACUUM FREEZE`" in the manual's words. For each page it starts filling from empty, it also sets the visibility map's **all-visible and all-frozen** bits. The effects: - The first VACUUM finds the pages already all-frozen and has almost nothing to do. - Index-only scans can use the visibility map immediately after the load. - There is no later wave of freezing I/O triggered by the table's age. ## The preconditions Frozen rows carry no transaction ID that could be rolled back, so PostgreSQL allows `FREEZE` only where a rollback would throw away the whole file anyway: 1. The table must have been **created or truncated in the current subtransaction**. Otherwise: `cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction` (SQLSTATE `55000`). A `SAVEPOINT` between the `TRUNCATE` and the `COPY` starts a new subtransaction and triggers this error. 2. The transaction must have **no open cursors and no older snapshots** registered. Otherwise: `cannot perform COPY FREEZE because of prior transaction activity` (SQLSTATE `25000`). 3. The target cannot be a **partitioned table** (`cannot perform COPY FREEZE on a partitioned table`, SQLSTATE `0A000`) or, in PostgreSQL 18, a **foreign table**; in 17 a foreign-table `COPY FREEZE` ran but silently ignored `FREEZE`. 4. It is a `COPY FROM` option only. Indexes, unlogged tables and `ON_ERROR ignore` do not stand in its way. ## The MVCC rule it breaks Under normal MVCC, a transaction's snapshot shows only rows whose inserting transactions had committed when the snapshot was taken. Frozen rows count as visible to **every** snapshot. The manual is blunt: "all other sessions will immediately be able to see the data once it has been successfully loaded. This violates the normal rules of MVCC visibility." Concretely: a `REPEATABLE READ` report that began before the census load committed would normally keep seeing the table as it was. After a `COPY FREEZE` commits, that report's next read of the table sees the new rows. Other sessions cannot see them *before* commit, because the table is either new or held by `TRUNCATE`'s lock. The anomaly is about older snapshots afterwards, and it is the same trade `TRUNCATE` already makes, since the manual also calls `TRUNCATE` not MVCC-safe. ## Plain COPY versus COPY FREEZE | | Plain `COPY FROM` | `COPY FROM ... (FREEZE)` | |---|---|---| | Row `xmin` | loading transaction's ID | frozen | | Visibility map bits | set later by VACUUM | set during the load for new pages | | First VACUUM's work | full pass, dirtying pages | close to none | | Older snapshots after commit | do not see the rows | **do** see the rows | | Preconditions | none | new or truncated in this subtransaction, no prior snapshots, not partitioned or foreign | ## Using it in the census load The two natural shapes are `BEGIN; CREATE TABLE ...; COPY ... (FREEZE); COMMIT;` for a new staging table and `BEGIN; TRUNCATE ...; COPY ... (FREEZE); COMMIT;` for a reload. In the second, `TRUNCATE` holds an `ACCESS EXCLUSIVE` lock until commit, so anyone reading the table waits for the whole load: fine for staging nobody else reads, not for a table serving live queries. `FREEZE` does not replace `ANALYZE`: the planner still needs statistics after the load.
- Why does a SAVEPOINT between TRUNCATE and COPY ... FREEZE make PostgreSQL reject the COPY?Frozen rows carry no transaction ID to roll back; their safety rests on the whole new file being discarded if the transaction that created or truncated the table aborts. If the COPY ran in a later subtransaction, rolling back to the savepoint would leave frozen rows in the truncated table. So `FREEZE` requires the creation or truncation in the current subtransaction.
- Does COPY FREEZE remove the need to VACUUM or ANALYZE the loaded table?It removes most of the first VACUUM's work, because pages are already all-visible and all-frozen. It does nothing for statistics, so `ANALYZE` is still needed after the load. Later updates and deletes create dead rows that VACUUM handles as usual.
- COPY FREEZE works on any existing table as long as nobody else is using it.
- Frozen rows stay invisible to other sessions until the next VACUUM runs.
- COPY FREEZE also skips index maintenance on the loaded table.
- COPY FREEZE on a partitioned table freezes each partition separately.
- In PostgreSQL 18, COPY FREEZE into a foreign table loads rows unfrozen without an error.
Assumes PostgreSQL 18, where COPY FREEZE on a foreign table raises an error; 17 accepted it and silently ignored FREEZE.