Demo repository · fictional

Shopfront — pending schema migrations

Eight migrations queued for the next release of a fictional e-commerce backend. Declared production sizes: orders 42M rows, order_items 118M, customers 3.2M. Every lock shown below was measured by replaying the migration in an embedded PostgreSQL (PGlite) and reading pg_locks.

Deploy gate
Gate: FAIL
Repository risk score
100/ 100
Findings
critical 4high 4medium 6
Longest blocking lock (est.)
4.7 min
286,400 writes blocked in total
1-- Speed up "my orders" page
2CREATE INDEX idx_orders_customer_id ON orders (customer_id);
3
criticalLS001line 2

CREATE INDEX without CONCURRENTLY on table "orders"

Acquires a ShareLock for the entire duration of the index build, blocking writes. On large tables this can take minutes.

Measured in PGlite
ordersShareLock· blocks writesidx_orders_customer_idAccessExclusiveLock· blocks reads+writes

Est. lock held 28 s (scan) · ~9,800 writes blocked

Safe pattern

Use CREATE INDEX CONCURRENTLY in its own non-transactional migration.

mediumLS010line 2

Migration acquires a heavy lock but has no SET lock_timeout

Without lock_timeout, a long-running query ahead of your DDL will block it, and then every subsequent query queues behind it (lock queue pile-up), causing an outage.

Measured in PGlite
ordersShareLock· blocks writesidx_orders_customer_idAccessExclusiveLock· blocks reads+writes

Est. lock held 28 s (scan) · ~9,800 writes blocked

Safe pattern

Add `SET lock_timeout = '3s';` at the top of the migration and implement application-level retry logic.

1-- Track shipping time for the new fulfilment dashboard
2ALTER TABLE orders ADD COLUMN shipped_at timestamptz NOT NULL DEFAULT now();
3ALTER TABLE orders ADD COLUMN tracking_code text NOT NULL;
4
highLS003line 2

ADD COLUMN "shipped_at" with volatile DEFAULT on table "orders"

A volatile DEFAULT (e.g. now(), gen_random_uuid()) forces PostgreSQL to rewrite the entire table to materialise the per-row default value, holding ACCESS EXCLUSIVE for the duration.

Measured in PGlite
ordersAccessExclusiveLock· blocks reads+writes

Est. lock held 4.7 min (rewrite) · ~98,000 writes blocked

Safe pattern

Add the column without a DEFAULT, then SET DEFAULT, then batch-backfill existing rows outside the migration.

mediumLS010line 2

Migration acquires a heavy lock but has no SET lock_timeout

Without lock_timeout, a long-running query ahead of your DDL will block it, and then every subsequent query queues behind it (lock queue pile-up), causing an outage.

Measured in PGlite
ordersAccessExclusiveLock· blocks reads+writes

Est. lock held 4.7 min (rewrite) · ~98,000 writes blocked

Safe pattern

Add `SET lock_timeout = '3s';` at the top of the migration and implement application-level retry logic.

criticalLS002line 3

ADD COLUMN "tracking_code" NOT NULL without any DEFAULT on table "orders"

PostgreSQL must verify every existing row satisfies NOT NULL. Without a DEFAULT, the statement fails immediately on any non-empty table.

Measured in PGlite
error: column "tracking_code" of relation "orders" contains null values
Safe pattern

Add the column as nullable first, backfill in batches, then add NOT NULL via ADD CONSTRAINT … CHECK NOT VALID + VALIDATE CONSTRAINT.

1-- Naming consistency with the CRM export
2ALTER TABLE customers RENAME COLUMN email TO email_address;
3
criticalLS008line 2

RENAME column "email" while application code still references it (3 reference(s))

Application code references "email" in demo-repo/src/customers.ts:5, demo-repo/src/customers.ts:5, demo-repo/src/customers.ts:13. After renaming, these queries will fail.

Measured in PGlite
customersAccessExclusiveLock· blocks reads+writes
Safe pattern

Expand/contract: add a new column/alias, update all code to use the new name, deploy, then drop the old column in a later migration.

mediumLS010line 2

Migration acquires a heavy lock but has no SET lock_timeout

Without lock_timeout, a long-running query ahead of your DDL will block it, and then every subsequent query queues behind it (lock queue pile-up), causing an outage.

Measured in PGlite
customersAccessExclusiveLock· blocks reads+writes
Safe pattern

Add `SET lock_timeout = '3s';` at the top of the migration and implement application-level retry logic.

1-- Totals must support cents
2ALTER TABLE orders ALTER COLUMN total TYPE numeric(12,2);
3
criticalLS004line 2

ALTER COLUMN "total" TYPE on table "orders"

A column type change forces a full table rewrite under ACCESS EXCLUSIVE lock, blocking all reads and writes for the duration.

Measured in PGlite
ordersAccessExclusiveLock· blocks reads+writesorders_pkeyAccessExclusiveLock· blocks reads+writesidx_orders_customer_idAccessExclusiveLock· blocks reads+writestable rewritten (relfilenode changed)

Est. lock held 4.7 min (rewrite) · ~98,000 writes blocked

Safe pattern

Add a new column with the target type, dual-write to both columns, backfill, then swap in a separate migration.

mediumLS010line 2

Migration acquires a heavy lock but has no SET lock_timeout

Without lock_timeout, a long-running query ahead of your DDL will block it, and then every subsequent query queues behind it (lock queue pile-up), causing an outage.

Measured in PGlite
ordersAccessExclusiveLock· blocks reads+writesorders_pkeyAccessExclusiveLock· blocks reads+writesidx_orders_customer_idAccessExclusiveLock· blocks reads+writestable rewritten (relfilenode changed)

Est. lock held 4.7 min (rewrite) · ~98,000 writes blocked

Safe pattern

Add `SET lock_timeout = '3s';` at the top of the migration and implement application-level retry logic.