Backend interview: how would you migrate a PostgreSQL constraint with NOT VALID?

Question

A production PostgreSQL table needs a new CHECK or foreign-key constraint. Existing rows may violate it, and the business cannot tolerate a long write block. Design the flow from finding violations, adding a NOT VALID constraint, repairing historical rows, and running VALIDATE CONSTRAINT. Cover locks, concurrency, monitoring, and failure handling.

What the interviewer is testing

• Whether you distinguish the lock behavior of adding a constraint from the later validation scan.
• Whether you know that NOT VALID skips old-row scanning while new inserts and updates are still checked.
• Whether you coordinate repair, validation, and application rollout instead of issuing one blocking DDL.
• Whether constraint state stays visible and recoverable through validation failures, long transactions, and rollback.

Model answer

Start with read-only queries to estimate violating rows, index availability, and long transactions. During a controlled window, run ADD CONSTRAINT ... NOT VALID. It does not scan existing rows, but it immediately checks later inserts and updates; historical rows may still violate the rule, so the state is explicitly pending validation.

Repair historical rows in bounded batches with commit boundaries and progress records. The repair logic must match the application rule, so deploy compatible code first when necessary. Then run VALIDATE CONSTRAINT while observing lock waits, scan duration, and database load. A successful validation marks the catalog constraint valid and completes the migration.

For a foreign key, verify a suitable uniqueness constraint on the referenced columns and assess concurrent writes and deletes. If validation fails, keep the NOT VALID constraint to protect new writes, repair the remaining rows, and retry. Drop it only when the requirement is genuinely removed.

Migration flow

-- 1. Record violations and create repair work
SELECT count(*) FROM orders WHERE total < 0;

-- 2. Add the constraint without scanning historical rows
ALTER TABLE orders
  ADD CONSTRAINT orders_total_nonnegative
  CHECK (total >= 0) NOT VALID;

-- 3. Repair in batches, then validate
ALTER TABLE orders
  VALIDATE CONSTRAINT orders_total_nonnegative;

The migration tool should store the constraint name, batch cursor, start and end times, validation result, and operator. Before rollout, check every application write path against the same rule so the repair job and business logic cannot overwrite each other.

The value of NOT VALID is separating an expensive historical scan from the add-constraint step; validation still scans the table and takes the documented lock, so it is not free. Check long transactions and replication lag, set a statement timeout and lock-wait alerts, and run validation in a controlled window.

New transactions are checked during validation, while historical repair must avoid overwriting business updates. Use stable index ranges and short transactions for batches. FOR UPDATE SKIP LOCKED can claim work when appropriate, but skipped rows must not make progress metrics look complete.

Common pitfalls

• Assuming NOT VALID disables the constraint and allows new dirty writes.
• Running VALIDATE CONSTRAINT without checking long transactions, lock waits, and replication capacity.
• Repairing every historical row in one huge transaction, causing bloat, long locks, and a hard rollback.
• Fixing a CHECK while ignoring the referenced index and delete path required by a foreign key.
• Dropping a failed constraint and losing protection for new writes and the later remediation path.

Failure handling and rollback

Use explicit states such as planned, not_valid, backfilling, validating, validated, and aborted. Persist every transition in a migration table and audit log. When validation finds violations, record constraint name and redacted key samples, pause validation, and keep the constraint; after repair, resume from the recorded progress.

If an application release must roll back, compatibility code should still handle both old rows and the new constraint. Dropping a NOT VALID constraint is a last resort and requires confirming that new writes cannot recreate the problem. Any DROP CONSTRAINT should have approval, backup, and a re-add plan.

Observability

Monitor violation count, repair rate, estimated remaining time, validation progress, lock waits, oldest transaction age, WAL growth, and replication lag. Distinguish “a new write violated the constraint” from “historical rows are not validated”; they require different response priorities.

After validation, query pg_constraint.convalidated and the constraint name to confirm the catalog state. Store the result, query plan, and load window in the migration record. External reports should use redacted keys and aggregates, never business data in logs.

• PostgreSQL 17 ALTER TABLE: lock and concurrency semantics for NOT VALID and VALIDATE CONSTRAINT.
• Current PostgreSQL constraint documentation: CHECK, foreign-key, and historical-row validation rules.
• PostgreSQL 17 pg_constraint: catalog fields such as convalidated.

Follow-up questions

Why are new writes checked while old rows may remain invalid?

NOT VALID skips the scan of rows that already exist when the constraint is added. The definition still applies immediately to later INSERT and UPDATE operations, preventing the historical backlog from growing.

Can writes continue during validation?

They can, but validation reads the table and takes the documented lock, so wait time and load must be controlled. New rows are checked, and repair batches must avoid overwriting application updates.

How do you estimate validation duration?

Use table size, scan plan, cache behavior, concurrent workload, and the maintenance window for a measured estimate or sample. Do not assume row count alone is linear; set timeout and cancellation policies before production.

What is special about a foreign-key migration?

Verify uniqueness and indexing on the referenced columns and define stable delete semantics. During validation, watch existing child rows together with concurrent deletes, updates, and lock conflicts.

When should you abandon and drop the constraint?

Only when the requirement is withdrawn or designed incorrectly and an alternative protection exists. Validation failure alone is not a reason to drop it; keeping NOT VALID continues protecting new data and preserves remediation.