cubic
Try cubic for free

Add PostgreSQL Constraints Without Long Table Locks

Flags PostgreSQL constraints added without NOT VALID, SET NOT NULL that scans the table, and column type changes that rewrite it under a blocking lock.

Catch this in your code

What it flags

Flaggedmigrations/20260916_orders_constraints.sql
SET lock_timeout = '3s';

-- Each statement scans all of orders while holding a lock that blocks writes
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE orders
  ADD CONSTRAINT orders_total_cents_nonnegative
  CHECK (total_cents >= 0);

ALTER TABLE orders
  ALTER COLUMN customer_id SET NOT NULL;
Passesmigrations/20260916_orders_constraints.sql
-- Migration 1: NOT VALID skips the scan, so the locks are brief
SET lock_timeout = '3s';
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders
  ADD CONSTRAINT orders_total_cents_nonnegative
  CHECK (total_cents >= 0) NOT VALID;
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_not_null
  CHECK (customer_id IS NOT NULL) NOT VALID;

-- Migration 2: validation scans rows without blocking reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_cents_nonnegative;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;

-- Migration 3: PostgreSQL 12+ uses the validated check to skip the scan
SET lock_timeout = '3s';
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_not_null;

Why it matters

Adding a foreign key or CHECK constraint checks every existing row, and PostgreSQL holds a lock that blocks writes to the table for the whole scan; for a CHECK constraint, the lock blocks reads too. lock_timeout bounds only the wait for the lock; once the scan starts, the lock stays held until the transaction ends. Adding the constraint as NOT VALID enforces it for inserted and updated rows at once, and a later VALIDATE CONSTRAINT checks existing rows under a lock that allows reads and writes. Type changes such as integer to bigint are worse: they hold an ACCESS EXCLUSIVE lock, which also blocks reads, while they rewrite the table and rebuild every index on it.

Agent instructions

cubic checks every pull request against these instructions. Paste them into AGENTS.md or Cursor rules and your AI coding tool follows them while it writes code.

AGENTS.md

Add this section to AGENTS.md at the repository root. Codex, Cursor, and other AI coding tools read it.

## Add PostgreSQL Constraints Without Long Table Locks

Applies to `**/*.sql`, `**/migrations/**`, `**/migrate/**`.

Review PostgreSQL migrations for constraint and column changes that scan or rewrite a table while holding a blocking lock.

Flag changes that:
- Add a FOREIGN KEY or CHECK constraint to an existing table without `NOT VALID`. The validation scan runs while writes are blocked, on both tables for a foreign key.
- Run `VALIDATE CONSTRAINT` in the same transaction as the `NOT VALID` add, which keeps the add's lock held through the scan.
- Run `ALTER COLUMN ... SET NOT NULL` on an existing large table without a validated `CHECK (col IS NOT NULL)` constraint. PostgreSQL 12 and later skip the full scan when that constraint exists.
- Add a UNIQUE or PRIMARY KEY constraint to an existing table with `ALTER TABLE ... ADD CONSTRAINT`, which builds the index under ACCESS EXCLUSIVE. Build it with `CREATE UNIQUE INDEX CONCURRENTLY`, then attach it with `ADD CONSTRAINT ... UNIQUE USING INDEX` (or `PRIMARY KEY USING INDEX` on columns that are already `NOT NULL`).
- Change a column type in a way that rewrites the table and rebuilds its indexes, such as `integer` to `bigint` or a `USING` expression that transforms values.
- Add a column with a volatile default, such as `gen_random_uuid()` or `clock_timestamp()`, which rewrites every row.

Do not flag:
- Constraints and column changes on tables created earlier in the same migration.
- `VALIDATE CONSTRAINT` in its own migration, which is the safe second step.
- Changes PostgreSQL applies without a rewrite, such as `varchar(n)` to `text`, raising a `varchar` length limit, or ADD COLUMN with a constant default.

Checks files matching **/*.sql, **/migrations/**, **/migrate/**.

More PostgreSQL agents

See all PostgreSQL code review standards

Run this agent on every pull request

cubic reviews every pull request against the agents you turn on and comments on the lines that break them.

Catch this in your code