Free tools Windows power users keep installed
One-click scans. No signup required.
Choose the migration based on what existing rows should mean. If every old row should receive the same non-volatile value, PostgreSQL 11 and later can add the column with a constant default without immediately rewriting the table. If each row needs its own value, add the column as nullable, backfill it in controlled batches, then enforce NOT NULL. PostgreSQL 18 also lets you add a NOT NULL constraint as NOT VALID, so new writes are checked before existing rows are validated; PostgreSQL 17 does not document that syntax.
Choose the path by the data, not by the fastest DDL
A fast schema change is useful only if it assigns correct values. Before writing the migration, decide what the new column should contain for historical rows and what concurrent inserts should receive. Then check the deployed PostgreSQL major version: the constant-default fast path dates to PostgreSQL 11, while NOT NULL ... NOT VALID is a PostgreSQL 18 feature.
| Approach | Use it when | Main tradeoff | Version note |
|---|---|---|---|
| Non-volatile constant default | All existing rows should receive the same value. | Fast addition does not make an incorrect historical value correct; volatile defaults require per-row evaluation. | Metadata fast path is available in PostgreSQL 11 and later. PostgreSQL: Modifying Tables |
| Nullable column, backfill, then enforce | Existing values must be derived from each row or otherwise differ. | Backfill is real write work; batch size and pacing depend on the workload. | Use across versions; check the deployed version’s ALTER TABLE behavior. |
NOT NULL NOT VALID, then validate |
New writes must be rejected if null while historical rows are checked later. | Validation still scans existing rows and has a documented lock impact. | PostgreSQL 18. PostgreSQL 18 release notes |
Valid CHECK, then SET NOT NULL |
On PostgreSQL 17 and older documented behavior, a validated check can prove no nulls remain. | The check must first be validated; this is not the same as skipping validation of historical data. | PostgreSQL 17 documents this scan optimization. PostgreSQL 17 ALTER TABLE |
For any route, account for five things: accepted syntax on your server version, whether old rows share one value, the value future inserts receive, scan and lock effects, and whether enforcement can be separated from historical validation.
When a constant default is appropriate
PostgreSQL 11 and later can add a column with a non-volatile constant default by recording the value in metadata for existing rows instead of immediately rewriting every row. Reads of old tuples return that value; a later table rewrite can materialize it physically. This makes the ADD COLUMN operation much faster than a row-by-row update, but it is not a backfill and it does not calculate a distinct historical value for each row. See the PostgreSQL documentation on modifying tables.
#1 Best Overall
For example, if every existing record truly should have status 'legacy', a uniform default may be valid. If status depends on the record’s history, account, or creation date, assigning the same value merely to avoid a rewrite would misstate the data.
ALTER TABLE target_table
ADD COLUMN status text NOT NULL DEFAULT 'legacy';Use only a non-volatile constant for this fast path. PostgreSQL identifies clock_timestamp() as an example of a volatile default: its value must be calculated for each row, so it follows a per-row path rather than the constant metadata optimization. Also distinguish the historical default from future behavior: changing or dropping a column default later affects subsequent inserts, not the values already represented for old rows.
Rank #2
Even a metadata-fast addition is not a promise of a lock-free migration or a guaranteed runtime. PostgreSQL documents lock modes by operation, and lock acquisition itself can affect deployment timing. Test the exact operation on the target major version and account for concurrent workload.
When existing rows need different values, backfill in stages
Use a staged migration when the correct value is row-specific, computed from existing data, or cannot be represented by one legitimate constant. First add the column nullable. Deploy or update application writers so inserts and relevant updates populate it. Then fill historical rows with the proper expression in bounded, repeatable batches, check that no nulls remain, and enforce the constraint.
Recommended Free Tools
ALTER TABLE target_table ADD COLUMN new_column desired_type;The following stages are a deployment outline, not a single transaction to run unchanged. The batch query depends on the table’s key and the expression that defines the correct value:
- Make future writes safe. Deploy code that supplies
new_columnfor new and changed rows, or define an appropriate future default if one is semantically correct. - Backfill existing rows. Update bounded batches where
new_column IS NULL, calculating the value from each row’s actual data. Make the operation safe to retry, and tune batch size and pacing against observed load; PostgreSQL documentation does not prescribe a universally safe batch size. - Check completion. Confirm no nulls remain and that concurrent writers cannot reintroduce them.
- Enforce non-nullness. Use
ALTER TABLE ... ALTER COLUMN ... SET NOT NULL, or stage enforcement and validation with the version-appropriate method below.
ALTER TABLE target_table
ALTER COLUMN new_column SET NOT NULL;Do not substitute an arbitrary placeholder just to make this step easy. The database constraint can guarantee non-nullness, but only your migration logic can ensure the stored values are meaningful.
What NOT VALID changes—and what it does not
NOT VALID separates checking old rows from enforcing a constraint on later writes. PostgreSQL’s documentation says: “With NOT VALID, the ADD CONSTRAINT command does not scan the table and can be committed immediately.” Inserts and updates made after the constraint is installed are still checked; a later VALIDATE CONSTRAINT checks the rows that existed beforehand. Validation is a scan and takes a SHARE UPDATE EXCLUSIVE lock. See PostgreSQL 18 ALTER TABLE.
PostgreSQL 18 adds this facility for NOT NULL. If the column has already been populated for historical rows, or you want to prevent new nulls while completing the old-row check, the staged form is:
ALTER TABLE target_table
ADD CONSTRAINT target_table_new_column_nn
NOT NULL new_column NOT VALID;
ALTER TABLE target_table
VALIDATE CONSTRAINT target_table_new_column_nn;Use the exact syntax supported by the deployed major version and rehearse the migration in a representative environment. NOT VALID does not waive validation: the historical scan still happens in the second step, and the constraint cannot be considered fully validated until that succeeds.
PostgreSQL 17 and earlier: use a validated CHECK as proof
PostgreSQL 17’s documented NOT VALID support covers CHECK and foreign-key constraints, not NOT NULL constraints. If the column is populated and you want to avoid the scan normally associated with setting the column attribute, add and validate a check that proves the column is non-null, then set NOT NULL:
ALTER TABLE target_table
ADD CONSTRAINT target_table_new_column_nn_check
CHECK (new_column IS NOT NULL) NOT VALID;
ALTER TABLE target_table
VALIDATE CONSTRAINT target_table_new_column_nn_check;
ALTER TABLE target_table
ALTER COLUMN new_column SET NOT NULL;The check’s NOT VALID phase avoids an initial scan but still enforces the condition on new or updated rows. Validation checks existing rows. PostgreSQL 17 documents that a valid check constraint proving no null can exist lets SET NOT NULL skip its own table scan. This is a distinct version-compatible route, not PostgreSQL 18’s not-null-constraint syntax.
Plan around locks and workload
Do not describe these changes as lock-free. Lock requirements differ by operation: PostgreSQL documents that most forms of adding a table constraint require ACCESS EXCLUSIVE, with a foreign-key exception, while validation takes SHARE UPDATE EXCLUSIVE. Review the operation-specific lock notes in the manual for your server version rather than assuming that a fast metadata change has no deployment impact.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware match- Test the exact DDL against the PostgreSQL major version in production.
- Run a representative backfill and validation in a staging environment; measure impact on query latency, write load, and replication lag in your own setup.
- Choose batch size and pacing from observed workload rather than copying a universal number; no general safe size or runtime is established.
- Set operational timeouts appropriate to your deployment, and monitor the migration while it runs.
- Keep application writers compatible with the intermediate schema, especially while the column is nullable or historical rows remain unvalidated.
The official documentation describes behavior and lock modes, but it does not establish how long a particular table’s migration will take or how much it will affect a specific workload.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
