2026-09-23 · 6 min read · Rahul Chitturi
- sql
- postgresql
- migrations
PostgreSQL can add supported constraints as NOT VALID so existing rows are not scanned during the initial change. The constraint still applies to new or changed rows, and a later VALIDATE CONSTRAINT checks legacy data.
A rollout becomes confusing when teams read NOT VALID as disabled, validation waits on another transaction, or application code starts relying on an invariant before old violations are repaired.
Read the deployed state
Inspect the exact constraint definition, validation flag, table, inheritance or partition relationships, server version, migration history, and active sessions. Verify behavior against the production role and schema search path.
- Separate creation from later validation.
- Confirm new writes are actually rejected.
- Find old violations with a bounded diagnostic query.
- Monitor blockers and scan impact during validation.
Explain lock waits precisely
Capture the validating session, requested lock, blocking transaction, query age, and deployment timeout. An apparently idle transaction can delay validation even when the table scan itself would be acceptable.
Do not cancel arbitrary database sessions from an automated migration. Define an operational decision path, retry policy, and rollback that preserve the previous application contract.
Build a rollout fixture
Use Flashman's SQL formatter for migration and catalog queries, diff for schema snapshots, JSON formatter for synthetic violating rows, and timestamp converter for lock timelines.
Test clean and dirty tables, writes before and during validation, null semantics, partitions, long transactions, replica lag, interrupted validation, migration retries, application version overlap, dumps, restores, and rollback.