Add NOT NULL to a Large PostgreSQL Table Safely: PostgreSQL 17 vs 18

To make a nullable column required on a busy PostgreSQL table, deploy application writes that populate it, backfill existing rows in small transactions, then validate the constraint separately from its initial creation. PostgreSQL 18 can add a __INLINE_CODE_0__ constraint with __INLINE_CODE_1__ and validate it later. PostgreSQL 17 and earlier supported versions can use a validated __INLINE_CODE_2__ constraint as proof before __INLINE_CODE_3__. Both approaches reduce time spent holding disruptive table locks, but neither guarantees zero downtime.

This guide explains the version-specific SQL, remaining locks, backfill race conditions and verification. The example assumes an existing __INLINE_CODE_0__ column that is currently nullable.

Why can SET NOT NULL disrupt a production database?

The risk is a verification scan performed while holding a strong lock, not a mandatory rewrite of every row. A plain __INLINE_CODE_0__ ordinarily checks the table for nulls. On a large, active table, acquiring and holding the required lock can interrupt requests even if all data is already clean. PostgreSQL's ALTER TABLE reference distinguishes scans from rewrites and explains deferred validation.

A migration also fails if historical rows remain null. New application code may write the field correctly while older orders still lack it. A database default does not automatically repair those old values; PostgreSQL's table-modification guide explains the distinction.

Which migration path should you choose?

This is a real version difference in the official PostgreSQL 18 and PostgreSQL 17 documentation. Do not use PostgreSQL 18's native deferred not-null syntax on PostgreSQL 17.

Step 1: Make new writes populate the field

First deploy application code that supplies a valid __INLINE_CODE_0__ for every insert and update path: API requests, scheduled jobs, importers, admin screens and old workers. During a rolling release, keep code compatible with the nullable database while writing the new value. Enforce the database invariant only after incompatible workers have drained.

A default is appropriate only when the same value is genuinely correct for all affected rows. It cannot invent a missing customer identifier. In the following example, __INLINE_CODE_0__ is assumed to be a verified, populated source of the new value. If that assumption is wrong, stop and resolve the data mapping.

~~~sql SELECT count(*) AS missing_target FROM orders WHERE customer_code IS NULL;

SELECT count(*) AS missing_source FROM orders WHERE customer_code IS NULL AND legacy_customer_code IS NULL; ~~~

Do not proceed if __INLINE_CODE_0__ is nonzero without an approved resolution for those rows.

Step 2: Backfill in small, committed batches

Update a bounded set of rows, commit, and repeat. A single huge __INLINE_CODE_0__ can generate substantial WAL, replication lag, row contention and rollback cost. This statement runs one batch, not a loop; execute it repeatedly from a controlled worker, committing after each batch.

~~~sql WITH batch AS ( SELECT id FROM orders WHERE customer_code IS NULL AND legacy_customer_code IS NOT NULL ORDER BY id LIMIT 500 FOR UPDATE SKIP LOCKED ) UPDATE orders AS o SET customer_code = o.legacy_customer_code FROM batch WHERE o.id = batch.id AND o.customer_code IS NULL RETURNING o.id; ~~~

Tune batch size and cadence to observed latency, WAL, dead tuples and replication lag. __INLINE_CODE_0__ avoids waiting on rows held by another worker but is not a general guarantee of low resource use. The PostgreSQL UPDATE and SELECT locking references explain these mechanisms. Test the query against the actual indexes and workload.

Rerun the null count. Zero is necessary but not sufficient: a still-running old worker could insert a null after the count. The constraint closes that race.

Step 3A: PostgreSQL 18 native NOT VALID NOT NULL

PostgreSQL 18 lets you enforce a not-null rule on future writes without scanning historical rows immediately, then validate the existing data separately. Execute the DDL and validation in separate transactions.

~~~sql -- PostgreSQL 18 only BEGIN; SET LOCAL lock_timeout = '3s'; ALTER TABLE orders ADD CONSTRAINT orders_customer_code_nn NOT NULL customer_code NOT VALID; COMMIT;

-- Run separately when the scan load is acceptable ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_code_nn; ~~~

The __INLINE_CODE_0__ option skips the initial scan, but the constraint applies to subsequent inserts and updates. An update to a historical row still containing a null can therefore fail. Backfill first. The PostgreSQL 18 ALTER TABLE manual says validation acquires a __INLINE_CODE_1__ lock, permitting ordinary row writes while still consuming I/O and conflicting with some maintenance operations.

The three-second __INLINE_CODE_0__ is an illustrative safeguard, not a universal setting. Use an appropriate timeout for the validation session as well. If the initial DDL cannot obtain its lock promptly, let it fail and retry during a quieter period instead of leaving an exclusive-lock request queued.

Step 3B: PostgreSQL 17, validated CHECK then SET NOT NULL

PostgreSQL 17 can use a validated check as proof that no null values exist, avoiding the usual scan during the final __INLINE_CODE_0__. Keep the check until after the conversion succeeds.

~~~sql -- PostgreSQL 17 and earlier supported versions BEGIN; SET LOCAL lock_timeout = '3s'; ALTER TABLE orders ADD CONSTRAINT orders_customer_code_nn_check CHECK (customer_code IS NOT NULL) NOT VALID; COMMIT;

-- Separate validation transaction ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_code_nn_check;

-- After validation succeeds BEGIN; SET LOCAL lock_timeout = '3s'; ALTER TABLE orders ALTER COLUMN customer_code SET NOT NULL; COMMIT;

-- Optional later cleanup, after verifying the column ALTER TABLE orders DROP CONSTRAINT orders_customer_code_nn_check; ~~~

The PostgreSQL 17 manual explicitly describes the valid-check proof that skips the ordinary scan. Do not drop the check in the same statement as __INLINE_CODE_0__, because PostgreSQL needs the proof during that operation. Cleanup is optional and itself requires another schema lock.

What locks remain?

Deferred validation reduces the duration of disruptive locking; it does not eliminate locks or scan costs. Most __INLINE_CODE_0__ forms require an __INLINE_CODE_1__ lock, which conflicts even with reads. The later __INLINE_CODE_2__ uses __INLINE_CODE_3__, which allows ordinary inserts, updates and deletes but conflicts with some maintenance and DDL. See PostgreSQL's ALTER TABLE and explicit-locking documentation.

Long-running transactions can delay initial DDL. A queued exclusive-lock request may then cause new queries to pile up behind it. Use short lock timeouts, a defined retry policy and workload-based deployment windows. Monitor lock waits, not just average query latency.

~~~sql SELECT pid, state, wait_event_type, wait_event, now() - query_start AS query_age, left(query, 120) AS query_preview FROM pg_stat_activity WHERE datname = current_database() AND wait_event_type = 'Lock' ORDER BY query_start; ~~~

Visibility depends on monitoring privileges. See PostgreSQL's activity-monitoring reference. Run this outside a long-lived transaction to inspect current activity.

Step 4: Verify the invariant in the database and application

A successful migration has three independent proofs: no remaining nulls, a validated constraint, and correct behaviour from real application write paths.

~~~sql -- Data SELECT count(*) AS remaining_nulls FROM orders WHERE customer_code IS NULL;

-- PostgreSQL 18 native constraint SELECT conname, contype, convalidated FROM pg_constraint WHERE conrelid = 'orders'::regclass AND conname = 'orders_customer_code_nn';

-- PostgreSQL 17 column attribute SELECT attname, attnotnull FROM pg_attribute WHERE attrelid = 'orders'::regclass AND attname = 'customer_code'; ~~~

For PostgreSQL 18, expect __INLINE_CODE_0__ and __INLINE_CODE_1__; for PostgreSQL 17, expect __INLINE_CODE_2__. PostgreSQL's pg_constraint catalog reference documents native not-null constraints and validation status. Schema-qualify the table if it is outside your search path.

Test a valid insert, an insert missing the field (which should fail), an update to a historical row, and normal worker/import flows. Perform negative tests in staging or a rolled-back transaction, not by inserting fake customer orders into production.

Failure-mode matrix

Rollback must cover application compatibility as well as schema. Dropping a constraint is not a safe rollback if newer code assumes the column is always populated. Preserve compatible application code and a documented rollback path until verification is complete.

Production release checklist

1. Run __INLINE_CODE_0__ and test the exact SQL against that major version.
2. Inventory all writers, including asynchronous jobs and old application instances.
3. Verify a legitimate historical data source and a recoverable backup.
4. Backfill in bounded transactions; monitor WAL, replication and latency.
5. Check remaining nulls and apply a short lock timeout to initial DDL.
6. Validate separately; watch blocked sessions and write failures.
7. Verify catalog state and positive/negative application tests.
8. Remove compatibility code and redundant constraints only after observation.

For transaction-heavy products, this sequence belongs in custom software development and web application architecture planning, not as a last-minute database command.

FAQs

Does PostgreSQL 18 eliminate locking when adding NOT NULL?

No. __INLINE_CODE_0__ skips the initial historical-data scan, but the DDL still acquires a lock. Validation later uses a less disruptive lock mode and still consumes resources.

Can I use NOT NULL NOT VALID on PostgreSQL 17?

No. Use __INLINE_CODE_0__, validate that check, and then __INLINE_CODE_1__. Native deferred not-null validation is documented for PostgreSQL 18.

Is a zero-null COUNT query enough?

No. It reflects one snapshot. A concurrent old worker could insert a null before the database constraint is installed.

Does VALIDATE CONSTRAINT block normal writes?

Ordinary row writes can continue under the documented __INLINE_CODE_0__ lock, but validation can compete for resources and conflict with maintenance or schema changes.

Should I use a default instead of a backfill?

Only when that default is correct for every affected record. A default cannot create missing customer identity information and does not generally repair historical rows.

If you are planning a live migration, contact Softotic with your PostgreSQL version, table size, write rate and proposed constraint. A short architecture review can identify lock, worker-compatibility and rollback risks before deployment.

Sources and references

• PostgreSQL 18: ALTER TABLE, deferred not-null validation and locking
• PostgreSQL 17: ALTER TABLE and validated CHECK proof
• PostgreSQL 18: Explicit Locking
• PostgreSQL 18: UPDATE
• PostgreSQL 18: Monitoring Database Activity
• PostgreSQL 18: pg_constraint catalog