An ALTER TABLE migration that finishes in four milliseconds against a laptop seed database can still take down a healthy production application before lunch. That surprises plenty of experienced engineers the first time they watch it happen. Adding a nullable column in modern PostgreSQL does not even rewrite the table. So why did the whole application stall?
In PostgreSQL, even a fast metadata change has to acquire a brief AccessExclusiveLock. Suppose an analytical reporting query has held a shared read lock (AccessShareLock) on that table for forty seconds when your deploy pipeline kicks off the migration. Your ALTER TABLE cannot acquire its lock yet, so it waits in the lock queue. Meanwhile, PostgreSQL lines up every new incoming SELECT, INSERT, and UPDATE from live traffic behind your waiting DDL statement until application connection pools run dry.
Preventing self-inflicted database outages comes down to a few steady habits. Separate schema changes from application code rollouts, wrap every DDL statement in a short lock timeout, and replace single-pass blocking operations with concurrent steps that let live queries continue.
Blocking DDL Operations and Safe Online Equivalents
| Unsafe Migration Statement | Production Failure Mechanism (PostgreSQL) | Zero-Downtime Pattern |
|---|---|---|
CREATE INDEX idx ON orders (org_id); |
Holds ShareLock, blocking all INSERT, UPDATE, and DELETE writes for the entire build |
CREATE INDEX CONCURRENTLY outside a transaction block |
ALTER TABLE orders ADD CONSTRAINT fk ... FOREIGN KEY ...; |
Acquires ShareRowExclusiveLock while scanning every historical row to validate |
ADD CONSTRAINT ... NOT VALID followed by a separate VALIDATE CONSTRAINT |
ALTER TABLE orders ALTER COLUMN amount TYPE bigint; |
Rewrites the entire heap table and indexes under an AccessExclusiveLock |
Expand, dual-write and backfill, switch reads, then contract across separate deploys |
ALTER TABLE orders RENAME / DROP COLUMN x; |
Running pods on the prior build throw immediate UndefinedColumn errors during a rolling deploy |
Stop reading and writing the column in release N; drop it in release N + 1 |
Guarding Every DDL Statement With lock_timeout
Whether your team runs migrations through Flyway, Alembic, Prisma, Rails, or Goose, production DDL should never wait indefinitely for a lock. Wrapping metadata changes inside a two-second lock_timeout lets a blocked migration fail fast and retry safely instead of piling up live traffic behind it. For foreign keys and CHECK constraints, declare the constraint NOT VALID inside that short metadata window so new writes are checked right away, then validate existing rows online in a separate statement:
-- Fast metadata lock guarded by a 2-second lock_timeout
BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '15s';
ALTER TABLE public.invoices
ADD COLUMN IF NOT EXISTS settlement_reference varchar(64);
ALTER TABLE public.invoices
ADD CONSTRAINT fk_invoices_organization
FOREIGN KEY (organization_id) REFERENCES public.organizations(id)
NOT VALID;
COMMIT;
-- Online validation (takes only ShareUpdateExclusiveLock; reads and writes continue)
SET lock_timeout = '2s';
SET statement_timeout = '0';
ALTER TABLE public.invoices VALIDATE CONSTRAINT fk_invoices_organization;
Before touching a hot table, it also helps to check pg_stat_activity for transactions that have been open longer than a few seconds. Knowing upfront that a forgotten idle in transaction session or a batch CSV export is holding a read lock saves you from burning retry attempts blindly.
Building Indexes Concurrently and Cleaning Up Invalid Leftovers
CREATE INDEX CONCURRENTLY scans the table across two passes under a lighter ShareUpdateExclusiveLock so ordinary reads and writes keep flowing. Two pipeline details trip teams up here.
First, PostgreSQL refuses to run CONCURRENTLY inside an explicit BEGIN ... COMMIT block, so you have to turn off automatic transaction wrapping on that migration file (such as -- migrate:no-transaction or Rails disable_ddl_transaction!). Second, if a deploy cancels partway through or hits a duplicate row on a unique index build, PostgreSQL leaves an unfinished index behind marked indisvalid = false. Queries cannot use that invalid index, yet every write still pays the overhead of updating it. Check for leftover invalid indexes and drop them concurrently before trying again:
-- Inspect any leftover INVALID indexes in PostgreSQL
SELECT c.relname AS index_name, t.relname AS table_name
FROM pg_class c
JOIN pg_index i ON c.oid = i.indexrelid
JOIN pg_class t ON i.indrelid = t.oid
WHERE NOT i.indisvalid;
DROP INDEX CONCURRENTLY IF EXISTS public.idx_invoices_org_created;
CREATE INDEX CONCURRENTLY idx_invoices_org_created
ON public.invoices (organization_id, created_at DESC);
If your stack runs MySQL 8.0+ on InnoDB, specify ALGORITHM=INSTANT where supported, or ALGORITHM=INPLACE, LOCK=NONE explicitly. That way MySQL fails immediately if an operation cannot run online, rather than quietly falling back to a blocking ALGORITHM=COPY table rebuild.
Changing Column Types Safely With Expand and Contract
A familiar B2B billing surprise shows up when an older ledger table stores money in a signed 32-bit integer column like amount_cents integer. Its ceiling is 2,147,483,647 cents—just $21,474,836.47—after which cumulative rollups or large enterprise invoices fail with integer out of range. Running ALTER COLUMN amount_cents TYPE bigint directly on a 100-million-row table rewrites the whole table under an exclusive lock and takes production offline for twenty minutes or more.
How do you widen that column while orders keep coming in? Spread the change across four small, decoupled releases so every step stays reversible.
Start by expanding the schema: add a nullable amount_minor_units bigint column guarded by lock_timeout = '2s'. Live application containers ignore the new column completely, so there is zero cutover risk. Next, ship an application deploy that writes every new or updated record to both amount_cents and amount_minor_units while still reading from amount_cents, and run a background job to backfill historical rows in small key-range batches of 1,000 to 5,000 rows per transaction with short pauses so replication lag and autovacuum bloat stay flat.
Once the backfill finishes, add CHECK (amount_minor_units IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT, confirm zero mismatches between the two columns, and flip application reads over to amount_minor_units behind a feature flag. Because dual-writes are still running, you retain instant backout capability if anything looks wrong. Only after the new read path has soaked cleanly do you contract the schema—removing writes to amount_cents in code first, and dropping the old column in a later migration.
For broader release coordination around database changes, pair these patterns with our change management plan template and zero-downtime system replacement guide, or speak with briskData's refactoring and database modernization engineers.