Running prisma migrate deploy in a CI/CD pipeline feels great in development when your tables have ten thousand rows. In production with millions of rows, that same migration can acquire an exclusive table lock that queues every incoming API read and brings your site to its knees.
Zero-downtime database migrations are not about hoping for the best; they are about designing additive, backward-compatible steps that allow both old and new code versions to run concurrently during a rolling deploy.
The expand-and-contract pattern
Never rename a database column in a single migration. If version 1 of your application expects first_name and last_name, and version 2 needs full_name, renaming the column immediately breaks active version 1 pods while the deploy rollout is underway.
Instead, follow the expand-and-contract lifecycle across three distinct deployments: first, add the new nullable column; second, deploy code that writes to both columns while reading from the old; third, backfill historical data and cut over reads; finally, contract the schema by dropping the old column.
Avoiding table locks on large indexes
Adding a standard B-tree index with CREATE INDEX locks writes on the target table until the index is completely built. On high-throughput transactional tables, even a thirty-second index build triggers connection pool exhaustion.
In PostgreSQL, production indexes must always be constructed using CREATE INDEX CONCURRENTLY. This acquires only a share-update-exclusive lock, allowing reads and writes to proceed unhindered while PostgreSQL builds the index across two separate table scans.
-- Safe production index creation without blocking writes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_org_created_at
ON users (organization_id, created_at DESC);Backfilling data safely in batches
Updating ten million rows with a single UPDATE table SET new_col = old_col transaction creates massive WAL write spikes and holds row locks that block production traffic.
Write backfill scripts that process rows in bounded batches with an intentional micro-delay between chunks. By committing each thousand-row chunk independently, locks release immediately, keeping replica lag near zero and preventing database crashes.