2026-09-25 · 8 min read
- sql
- postgresql
- operations
PostgreSQL normally tracks tuple transaction age and later freezes old tuples during vacuum. COPY FREEZE can mark rows from a controlled initial load as frozen immediately when its strict eligibility conditions are satisfied.
The option targets specialized creation, restore, and reload workflows. It does not make a table immutable, remove the need for autovacuum, or safely accelerate arbitrary imports into an active table.
Start with an operational objective
State whether the goal is reducing post-load vacuum work, shortening a restore window, or improving a repeatable warehouse rebuild. Benchmark ordinary COPY first, including index creation, constraint validation, analyze, WAL, replication, and the next maintenance cycle.
- Optimize the complete readiness window.
- Use the exact production PostgreSQL version.
- Retain ordinary COPY as a safe fallback.
- Document why freezing changes the outcome.
Build an eligible transaction
Design the transaction around the version-specific requirement that the table be created or truncated in the current subtransaction, with no disqualifying older snapshots or cursors. Inventory connection-pool behavior, migration wrappers, savepoints, and monitoring queries that might alter assumptions.
Check target table type and partition behavior explicitly. Do not infer that eligibility of a parent applies to every physical relation receiving rows.
Control visibility and access
The documented visibility caveat means this workflow belongs behind a maintenance boundary. Keep application traffic away from the target until loading, constraints, indexes, statistics, row counts, checksums, and acceptance queries are complete.
Use a dedicated role with only required privileges. Keep source files protected, validate their origin and encoding, and avoid exposing sensitive row samples in logs or browser tools.
Prepare failure and fallback paths
Define behavior for parse errors, disk exhaustion, lock timeout, cancellation, replica lag, transaction rollback, and a FREEZE eligibility error. A retry should recreate the known initial state rather than append uncertain partial data.
If automation cannot prove every prerequisite, execute ordinary COPY and continue the documented maintenance plan. Never disable autovacuum globally to imitate the expected benefit.
Rehearse restores and upgrades
Use Flashman's SQL formatter for load scripts, JSON formatter for synthetic records, diff for catalog and row-count snapshots, timestamp converter for phase timing, and cron helper for maintenance windows.
Test create and truncate workflows, savepoints, open snapshots, partitioned targets, failures, rollback, constraints, indexes, analyze, autovacuum, replicas, backups, restore, permissions, observability, and major-version migration.