2026-09-23 · 8 min read
- sql
- postgresql
- migrations
Adding a constraint to a large PostgreSQL table can combine catalog changes, table scans, lock acquisition, and application compatibility in one risky deployment. For supported constraint types, NOT VALID can separate initial enforcement from verification of existing rows.
The database still checks relevant new or changed rows after the constraint is added. A later VALIDATE CONSTRAINT scans legacy data and records the constraint as validated when every row conforms.
Confirm feature and version semantics
Check the deployed PostgreSQL version and exact constraint type before designing the migration. NOT VALID is not a generic modifier for every constraint, and behavior across inheritance and partitioning needs version-specific verification.
- Capture the generated DDL before production.
- Inspect catalog validation state after each phase.
- Keep database enforcement as the source of truth.
- Do not describe NOT VALID as disabled.
Find and repair legacy violations
Write a diagnostic query equivalent to the proposed invariant and run it with bounded resource use. Classify violations, decide whether to update, archive, or reject them, and make cleanup repeatable under concurrent writes.
The constraint protects new relevant writes while cleanup proceeds, but every writer and replica path should be tested. Avoid application-only checks that race or implement subtly different null and comparison semantics.
Schedule validation as an operation
Measure the scan on production-like cardinality, identify lock modes and likely blockers, set statement and lock timeouts deliberately, and observe I/O, replicas, autovacuum, and application latency. Validation should have an owner and a safe retry plan.
Long-lived transactions can block catalog or lock progress even when ordinary queries remain fast. Diagnose the dependency before deciding whether an operator should wait, reschedule, or terminate a session.
Test the complete deployment sequence
Use Flashman's SQL formatter for DDL and diagnostics, diff for catalog snapshots, JSON formatter for synthetic rows, timestamp converter for lock timelines, and cron helper for a low-traffic validation window.
Test clean and dirty data, inserts and updates after creation, null values, partitions, concurrent transactions, blocked validation, cancellation, retry, replica lag, mixed application versions, dump and restore, migration rollback, and post-validation query plans.