---
title: "Zero-Downtime PostgreSQL Migrations at Scale"
published: true
description: "Advisory lock strategies, ghost table patterns, and pg_repack techniques for non-blocking schema migrations on high-traffic mobile backends."
tags: postgresql, architecture, devops, api
canonical_url: https://blog.mvpfactory.co/zero-downtime-postgresql-migrations-at-scale
---
## What We Will Build
By the end of this tutorial, you will have a complete migration playbook for PostgreSQL that eliminates downtime during schema changes. We will walk through advisory locks for coordinating migration runners, the ghost table pattern for lock-free schema rewrites, `pg_repack` for bloat reclamation, and the `CREATE INDEX CONCURRENTLY` failure mode that silently breaks your queries. These are the patterns I use on mobile backends handling 10k+ requests per second.
## Prerequisites
- PostgreSQL 14+ (the patterns work on older versions, but 14+ handles `ADD COLUMN ... DEFAULT` without table rewrites)
- A multi-instance deployment (Kubernetes, ECS, or similar)
- `pg_repack` extension installed on your database server
- Familiarity with basic DDL operations and PostgreSQL locking
## Step 1: Coordinate Runners with Advisory Locks
When multiple pods attempt the same migration simultaneously, you get race conditions. Here is the minimal setup to get this working.
sql
-- Acquire a session-level advisory lock before migrating
SELECT pg_advisory_lock(hashtext('migrations_lock'));
-- Set a timeout so you fail fast instead of queuing
SET lock_timeout = '5s';
-- Run your migration steps here...
-- Always release explicitly
SELECT pg_advisory_unlock(hashtext('migrations_lock'));
Use session-level locks, not transaction-level. If your migration spans multiple transactions, transaction-level locks release too early and another runner slips in mid-migration.
## Step 2: Apply the Ghost Table Pattern for Dangerous Changes
This is the technique behind GitHub's `gh-ost` and tools like `pg-osc`, adapted for PostgreSQL. Let me show you a pattern I use in every project that involves column type changes or `NOT NULL` additions on large tables.
| Step | Action | Lock Required |
|------|--------|---------------|
| 1 | Create a ghost table with the new schema | None |
| 2 | Install triggers on the original table | `SHARE ROW EXCLUSIVE` (brief) |
| 3 | Backfill existing rows in batches | None |
| 4 | Swap table names atomically | `ACCESS EXCLUSIVE` (milliseconds) |
| 5 | Drop the old table | None |
The atomic swap is the only moment you hold the dangerous lock, and it completes in single-digit milliseconds:
sql
BEGIN;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_ghost RENAME TO orders;
COMMIT;
Your replication triggers must handle `INSERT`, `UPDATE`, and `DELETE`, and they must be idempotent to survive the overlap between backfill and live traffic.
## Step 3: Reclaim Bloat with pg_repack
After heavy write workloads, table bloat destroys sequential scan performance. `VACUUM FULL` fixes it but locks the table for minutes. `pg_repack` does the same job online:
bash
pg_repack --table orders --no-superuser-check -d mydb
Internally it uses the same ghost table idea — build a copy, replay changes, swap. One caveat: you need free disk space equal to the table size plus its indexes. Monitor `pg_stat_user_tables.n_dead_tup` and schedule runs before bloat exceeds 30%.
## Step 4: Create Indexes Without Blocking Writes
sql
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);
After creation, always validate:
sql
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indexrelid = 'idx_orders_user'::regclass;
If `indisvalid` is `false`, the index exists but the planner ignores it. Drop it and retry.
## Gotchas
Here is the gotcha that will save you hours:
- **Ghost table DELETE triggers.** Teams forget the `DELETE` case and end up with ghost rows that should have been purged. Always handle all three DML operations.
- **`CREATE INDEX CONCURRENTLY` inside a transaction.** PostgreSQL explicitly disallows this. Your migration tool must run it outside a transaction block, or the command fails immediately.
- **Silent invalid indexes.** A failed concurrent index build does not raise an error in every client library. Add an automated `indisvalid` check to your CI pipeline. Silent failures surface as slow queries days later, and nobody thinks to check the index itself.
- **Advisory lock cleanup.** PostgreSQL cleans up if your session crashes, but relying on that is sloppy. Always release explicitly.
- **pg_repack disk space.** The docs do not mention this prominently, but running `pg_repack` on a 500GB table without 500GB of free space will fail mid-operation.
## The Migration Checklist
For every schema change on our mobile backend, we follow this:
1. Classify the operation — metadata-only (safe) or table rewrite (dangerous)
2. Test on a production-size replica — measure lock duration and WAL generation
3. Deploy with advisory locks to coordinate across pods
4. Use ghost tables for any table over 1GB
5. Validate indexes post-creation with an automated CI check
6. Schedule `pg_repack` weekly for high-churn tables
During one particularly long migration debugging session, [HealthyDesk](https://play.google.com/store/apps/details?id=com.healthydesk) reminded me to take a break right before I got paged — ironic timing, but the stretch probably kept me sane through the incident.
## Conclusion
Schema migrations deserve the same rigor as your API design. Set `lock_timeout` on every migration session — that single setting has saved me from more pages than any other config change. Adopt the ghost table pattern early, validate every concurrent index, and schedule regular `pg_repack` runs. Your users will never know you changed the schema, and that is exactly the point.
**Further reading:**
- [PostgreSQL Advisory Locks documentation](https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS)
- [pg-osc: Online Schema Change for PostgreSQL](https://github.com/shayonj/pg-osc)
- [pg_repack documentation](https://reorg.github.io/pg_repack/)
Top comments (0)