Adding and Validating PostgreSQL Constraints Safely

When you add a rule to a live PostgreSQL table, PostgreSQL checks every existing row during the schema change unless you defer that check. The scan can keep a strong lock in place for a long time. A constraint is a database rule. For example, it can require a positive price or require an order to reference an existing customer. For CHECK constraints, which test a condition on each row, and foreign-key constraints, which require a matching row in another table, add the rule as NOT VALID, fix violations in old rows, then check those rows separately with VALIDATE CONSTRAINT. PostgreSQL enforces the rule for new writes from the moment you add it, while validation later checks the rows that were already there.

This approach adds the rule first and checks old rows later. PostgreSQL still takes locks, and the later check still scans existing data. It applies to CHECK and foreign-key constraints, with a staged method also available for NOT NULL. Unique and primary-key constraints require a different approach. The PostgreSQL ALTER TABLE documentation describes the available syntax and how validation works.

Add the rule before scanning old rows

Add an eligible constraint with NOT VALID to make PostgreSQL enforce it for new writes without checking old rows yet. Fix violations in the old rows, then validate the constraint:

ALTER TABLE products
  ADD CONSTRAINT products_price_positive
  CHECK (price > 0) NOT VALID;

-- Correct existing rows that violate the rule.

ALTER TABLE products
  VALIDATE CONSTRAINT products_price_positive;

After the first command, PostgreSQL enforces the constraint for subsequent inserts and updates. Existing rows remain unchecked until validation succeeds. That means an update to a legacy row that still violates the rule can fail even if the update changes an unrelated column; the updated row must satisfy the constraint.

For a foreign key, the same sequence can prevent new orphaned references while you resolve old ones:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id)
  REFERENCES customers (id)
  NOT VALID;

-- Correct existing references that have no matching customer.

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_customer_fk;

PostgreSQL checks the existing rows during validation. A successful validation establishes that those rows satisfy the constraint. Until then, the rule applies to new writes, but PostgreSQL has not confirmed that every existing row complies.

Repair data and check the rule’s meaning

Decide what a violation means before changing existing data. An order with a missing customer might need a corrected reference, a newly created customer row, or removal of the invalid order. PostgreSQL can enforce the rule, but the right repair depends on what the data means.

For large tables, run the backfill and cleanup in controlled batches, and monitor how they affect production traffic. Make sure the services, imports, and background jobs that write to the table follow the new rule; otherwise, they will start receiving constraint errors as soon as you add it. PostgreSQL does not prescribe a universal batch size or backfill rate, so choose one based on the table and workload.

Check null behavior as well as obvious violations. A CHECK expression passes when it evaluates to either TRUE or NULL. Therefore, CHECK (price > 0) allows a null price; add a NOT NULL requirement if nulls must also be rejected. To find rows that fail the check itself, look for rows where its expression is FALSE. The PostgreSQL constraints documentation explains these rules.

Foreign keys also have null behavior: by default, a referencing row with a null foreign-key value does not need a matching row in the referenced table. Use NOT NULL if every row must provide a reference. PostgreSQL requires a primary key or unique constraint on the referenced columns; an eligible unique index also meets this requirement. It does not automatically create an index on the referencing columns, so consider one when deletes or updates to referenced rows need to find matching rows efficiently.

Plan for locks and validation work

Using NOT VALID postpones the initial verification scan during constraint creation; PostgreSQL still takes a lock for the schema change. PostgreSQL’s default lock for ALTER TABLE is ACCESS EXCLUSIVE unless a command specifies a different lock. Adding a foreign key uses SHARE ROW EXCLUSIVE locks on both the referencing and referenced tables. A requested lock can still disrupt work if PostgreSQL must wait for an existing transaction to release a conflicting lock.

During validation, PostgreSQL scans existing data while holding a less restrictive lock. PostgreSQL uses SHARE UPDATE EXCLUSIVE on the table being validated, which permits ordinary reads and data updates, though it conflicts with some maintenance and schema operations. Foreign-key validation also takes a ROW SHARE lock on the referenced table. The scan still consumes resources and competes with application work, so plan its timing and monitor the database while it runs. These lock modes and the validation behavior are documented under ALTER TABLE.

Use a different rollout for unique and not-null rules

NOT VALID is not the phased path for ordinary unique or primary-key constraints. To reduce write blocking while adding uniqueness, build a unique index concurrently and attach it as a constraint:

CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx
  ON users (email);

ALTER TABLE users
  ADD CONSTRAINT users_email_unique
  UNIQUE USING INDEX users_email_unique_idx;

PostgreSQL lets inserts, updates, and deletes continue during a concurrent index build. The build scans the table twice and waits for relevant transactions. It also uses additional CPU and I/O. The command cannot run inside a transaction block. A failed build can leave an invalid index that needs inspection. The index also begins enforcing uniqueness during part of the build, so writes can encounter uniqueness violations before the command completes. See PostgreSQL’s CREATE INDEX documentation before planning this migration.

For a large table where a column must become non-null, validate a check constraint first, then set the column’s native NOT NULL property:

ALTER TABLE accounts
  ADD CONSTRAINT accounts_external_id_present
  CHECK (external_id IS NOT NULL) NOT VALID;

-- Backfill or otherwise fix rows with a null external_id.

ALTER TABLE accounts
  VALIDATE CONSTRAINT accounts_external_id_present;

ALTER TABLE accounts
  ALTER COLUMN external_id SET NOT NULL;

ALTER TABLE accounts
  DROP CONSTRAINT accounts_external_id_present;

A valid check constraint proving that the column contains no nulls lets PostgreSQL skip the scan it would otherwise perform for SET NOT NULL. The final schema change still needs its required lock, so check its behavior against the PostgreSQL version and table in use.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com