Migration Safety Auditor

Migration Safety Audit — add_email_verified.sql

Engine: Postgres 15 · Table: users (estimated 12M rows from prior context)

Migration Under Review

-- add_email_verified.sql

ALTER TABLE users
  ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE users
  ADD COLUMN email_verified_at TIMESTAMPTZ;

CREATE INDEX users_email_verified_idx
  ON users (email_verified);

ALTER TABLE users
  ADD CONSTRAINT users_verified_at_present_when_verified
  CHECK (email_verified = FALSE OR email_verified_at IS NOT NULL);

Findings

Finding 1: ADD COLUMN ... NOT NULL DEFAULT FALSE — historically a full rewrite

Severity: Low (PG 15) Operation: ALTER TABLE ADD COLUMN

In Postgres versions earlier than 11, adding a column with a non-null default required rewriting every row in the table. Postgres 11+ optimizes this for constant defaults (FALSE, literal values) by storing the default in the table's pg_attribute row instead of touching the heap — meaning this specific case is fast, not the catastrophe it would have been on Postgres 10.

However:

• The optimization applies only to literal defaults. If the default were a function call (e.g. gen_random_uuid()), the rewrite kicks back in.
• The operation still acquires AccessExclusiveLock on the table for the duration of the metadata update — short, but during that window all reads and writes block. On a 12M-row table at peak traffic this is typically sub-second, but the lock blocks queries that are already running on the table, including long-running analytical queries.

Risk classification: Low-to-Medium for this specific literal-default case on Postgres 15. Would be HIGH if the default were a function call.

Finding 2: CREATE INDEX without CONCURRENTLY blocks writes

Severity: High Operation: CREATE INDEX

CREATE INDEX without CONCURRENTLY acquires ShareLock on the target table for the entire duration of the index build. On a 12M-row table the build is estimated at 45 seconds to 2 minutes depending on hardware and current load. During that window:

• All INSERT, UPDATE, and DELETE against users block.
• SELECT queries continue to run, but new write transactions queue up behind the lock.
• If write throughput exceeds the connection pool size during the block, the application sees connection-pool exhaustion and returns 500s to users.

Additional concern: an index on a low-cardinality boolean column (email_verified: only TRUE or FALSE) is rarely useful. The planner will almost always choose a sequential scan over a 2-value index unless the column is very skewed (e.g. 99.9% one value). If the intent is to find unverified users, a partial index is the right shape:

CREATE INDEX CONCURRENTLY users_email_unverified_idx
  ON users (id)
  WHERE email_verified = FALSE;

Risk classification: HIGH for the locking; the index itself is probably the wrong shape regardless.

Finding 3: ADD CONSTRAINT ... CHECK validates against every existing row

Severity: Medium Operation: ADD CONSTRAINT (CHECK)

Adding a CHECK constraint validates every existing row in the table by default. On 12M rows this is a sequential scan under AccessExclusiveLock — estimated 8 to 25 seconds. Like Finding 2, this blocks writes.

The safe pattern is the two-step NOT VALID / VALIDATE CONSTRAINT dance:

-- Step 1: add the constraint, mark NOT VALID — fast, no row scan
ALTER TABLE users
  ADD CONSTRAINT users_verified_at_present_when_verified
  CHECK (email_verified = FALSE OR email_verified_at IS NOT NULL)
  NOT VALID;

-- Step 2: validate in a separate transaction — takes a ShareUpdateExclusiveLock,
-- which does NOT block writes (only blocks other DDL)
ALTER TABLE users
  VALIDATE CONSTRAINT users_verified_at_present_when_verified;

Risk classification: MEDIUM. Won't cause an outage on a healthy 12M-row table but will visibly degrade write latency for the duration.

Finding 4: No rollback script provided

Severity: Medium Operation: All four

Best practice for any production migration is a corresponding rollback script committed alongside it. The rollback for this migration is straightforward (drop the index, the constraint, and the columns in reverse order), but if the deploy fails partway through, the operator should not be discovering the rollback path on a 2 AM Pager Duty page.

Recommended Replacement

Split into three deploys, each idempotent and reversible:

Deploy 1: Add the columns (fast, online)

-- add_email_verified__part1_columns.sql
ALTER TABLE users
  ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE users
  ADD COLUMN email_verified_at TIMESTAMPTZ;

Rollback:

ALTER TABLE users DROP COLUMN email_verified_at;
ALTER TABLE users DROP COLUMN email_verified;

Deploy 2: Add the constraint (NOT VALID first, then validate)

-- add_email_verified__part2_constraint.sql
ALTER TABLE users
  ADD CONSTRAINT users_verified_at_present_when_verified
  CHECK (email_verified = FALSE OR email_verified_at IS NOT NULL)
  NOT VALID;

Then, after the application has been deployed and is writing data consistent with the constraint, run in a separate transaction:

ALTER TABLE users
  VALIDATE CONSTRAINT users_verified_at_present_when_verified;

Rollback:

ALTER TABLE users DROP CONSTRAINT users_verified_at_present_when_verified;

Deploy 3: Add the partial index (concurrent, no lock)

-- add_email_verified__part3_index.sql
CREATE INDEX CONCURRENTLY users_email_unverified_idx
  ON users (id)
  WHERE email_verified = FALSE;

Notes: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Most migration tooling (Flyway, Liquibase, Drizzle, Prisma) supports flagging a migration as non-transactional; verify the tool's syntax before deploying. If the build fails partway, the resulting index is INVALID and must be dropped and re-created.

Rollback:

DROP INDEX CONCURRENTLY IF EXISTS users_email_unverified_idx;

Pre-Deploy Checklist

• Confirm Postgres version is 11+ (so the literal-default fast path applies). Below 11, Deploy 1 needs to be restructured to use ADD COLUMN + UPDATE + SET NOT NULL in stages.
• Confirm the application code reads email_verified defensively. Between Deploy 1 and Deploy 2 application restart, some app instances will return FALSE for all users (including ones who'd previously been treated as verified by other means). Behavior must be acceptable during that window.
• Confirm CREATE INDEX CONCURRENTLY is supported by the migration tooling and that the migration is flagged as non-transactional.
• Run all three on a staging environment first to confirm timings and lock behavior against representative data volume.

Post-Deploy Verification

After all three deploys land:

-- 1. Confirm the column exists and the default is captured
SELECT column_name, data_type, column_default, is_nullable
FROM information_schema.columns
WHERE table_name = 'users' AND column_name LIKE 'email_verified%';

-- 2. Confirm the constraint is VALID (not just NOT VALID)
SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'users'::regclass
  AND conname = 'users_verified_at_present_when_verified';

-- 3. Confirm the index is valid and has the expected shape
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users' AND indexname = 'users_email_unverified_idx';

Expect:

• is_nullable = NO for email_verified, YES for email_verified_at
• convalidated = true for the check constraint
• The partial index WHERE email_verified = FALSE appears in indexdef

Summary

Recommendation: Do not deploy the original migration as written. Use the three-part replacement above. Total engineering effort to restructure is approximately 30 minutes; the alternative is a high-confidence incident window during deploy.

This audit reviews a hypothetical migration for illustration. Always run the recommended changes on a representative staging environment before production deploy.

This sample illustrates the skill's output format. Names, metrics, and operational details are illustrative unless the artifact explicitly analyzes public information.