Zero-Downtime Schema Changes: Reading EXPLAIN Plans and Building Indexes Concurrently in PostgreSQL
Every backend engineer eventually needs to change a production schema. Add a column. Add an index. Fix a query that's crawling.
The problem is that PostgreSQL, by default, isn't gentle about it.
Many schema operations grab locks that block reads, writes, or both, while they run. On a small table, that's invisible. On a table with tens of millions of rows, that same operation can freeze your application for minutes, sometimes longer.
This article covers two tools that let you make real schema and performance changes without taking your database offline: EXPLAIN, to understand what a query is actually doing, and CONCURRENTLY, to build indexes without locking the table.
Why Ordinary Schema Changes Cause Downtime
Most DDL statements in PostgreSQL take an ACCESS EXCLUSIVE lock, even briefly. That lock blocks every other query on the table, including simple SELECTs, until the operation finishes.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
On a small table, this index builds in milliseconds and nobody notices. On a large, actively written table, PostgreSQL has to scan and lock the entire table to build the index, and every other query queues up behind it.
If that table serves your checkout flow, your users just experienced an outage, even though nothing "broke."
Reading the Problem First: EXPLAIN
Before optimizing anything, you need to know what PostgreSQL is actually doing when it runs a query. That's what EXPLAIN is for.
EXPLAIN SELECT * FROM orders WHERE customer_id = 482;
This returns the query plan PostgreSQL intends to use, without executing it. Add ANALYZE, and PostgreSQL actually runs the query and reports real timing and row counts alongside the plan:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 482;
The output tells you whether PostgreSQL is using an index scan or falling back to a sequential scan, how many rows it expects versus how many it actually found, and where the time is really going.
A few patterns to watch for:
- Seq Scan on a large table — PostgreSQL is reading every row, usually because no usable index exists
- Large gap between estimated and actual rows — table statistics are stale; consider running ANALYZE on the table
- Nested Loop over large row counts — often a sign a join needs a supporting index
- High cost with low actual time, or vice versa — the planner's assumptions don't match reality
EXPLAIN doesn't fix anything by itself. What it does is stop you from guessing. Adding an index without knowing why a query is slow is how teams end up with a schema full of indexes nobody needs, each one quietly slowing down every write.
Building Indexes Without Locking the Table
Once EXPLAIN shows you a query needs an index, the next problem is building that index without stopping production traffic.
This is what CONCURRENTLY is for:
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);
Instead of locking the table for the full build, PostgreSQL scans the table twice in the background, allowing normal reads and writes to continue the entire time.
The tradeoffs are worth knowing upfront:
- It takes noticeably longer than a regular CREATE INDEX
- It cannot run inside a transaction block
- If it fails partway through, it can leave behind an invalid index that must be dropped manually
DROP INDEX CONCURRENTLY idx_orders_customer_id;
Then you simply retry the build. The extra build time is a small price for keeping the application fully responsive while it happens.
The same principle applies to removing indexes. DROP INDEX also takes a lock by default, so on a busy table, prefer:
DROP INDEX CONCURRENTLY idx_unused_index;
Other Schema Changes That Deserve the Same Care
Indexes aren't the only operation that can lock a table. A few common ones worth knowing:
- Adding a column with a default value — in PostgreSQL 11 and later, this is fast and doesn't rewrite the table. On older versions, it rewrites every row and locks the table for the duration.
- Adding a NOT NULL constraint — PostgreSQL scans the whole table to verify the constraint. Adding a CHECK constraint as NOT VALID first, validating it separately, then converting it avoids a long-held lock.
- Adding a foreign key — like NOT NULL, this can be added as NOT VALID and validated afterward with VALIDATE CONSTRAINT, which takes a much lighter lock.
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;
The pattern across all of these is the same: split an expensive, blocking operation into two cheaper steps, so the table is never locked for the full duration of the work.
A Real-World Scenario
Imagine an orders table with 40 million rows, taking constant writes from checkout traffic.
A new reporting feature needs to filter by customer_id, and EXPLAIN ANALYZE confirms PostgreSQL is doing a sequential scan on every request, taking several seconds per query.
Running a plain CREATE INDEX during business hours would lock the table and stall checkout for the duration of the build, easily several minutes on a table that size.
Running CREATE INDEX CONCURRENTLY instead takes longer to complete, but checkout traffic never notices. Once it finishes, the same EXPLAIN ANALYZE confirms PostgreSQL is now using an index scan, and query time drops from seconds to milliseconds.
No maintenance window. No status page incident. No customer impact.
Building This Into a Repeatable Pipeline
None of this needs to be manual every time. Teams that do this well tend to build it into their migration process:
- Every new index or constraint is checked against EXPLAIN ANALYZE on a staging copy of production data first
- Every index build on a large table uses CONCURRENTLY by default, not as an exception
- Constraints on large tables are added NOT VALID, then validated in a separate step
- Migrations run through a tool like Flyway, Sonic (for large-table migrations), or a custom script that enforces these patterns automatically, rather than relying on individual engineers to remember
This turns "avoid downtime" from a habit a few senior engineers remember into a rule the pipeline itself enforces, regardless of who writes the migration.
What This Doesn't Solve
It's worth being clear about the limits here.
CONCURRENTLY reduces locking, but it isn't free. It's slower, more resource-intensive, and, on PostgreSQL versions before 12, cannot be run inside a wrapped migration transaction, which means your migration tooling needs to account for that.
EXPLAIN tells you what's happening, but it can't fix a bad schema design or a query that's fundamentally asking for too much data. It's a diagnostic tool, not a repair tool. The fix still requires human judgment about indexes, query structure, or denormalization.
Rule of Thumb
Before adding an index, run EXPLAIN ANALYZE to confirm you actually need one.
Before running any DDL on a large, actively written table, ask whether a CONCURRENTLY or NOT VALID variant exists, and use it by default.
Treat every schema change on a production table as a locking operation until proven otherwise.
Final Thoughts
Zero-downtime schema changes aren't a special feature you turn on. They're the result of understanding exactly what PostgreSQL is doing before you ask it to do something expensive, and choosing the variant of each operation that respects a table under live traffic.
EXPLAIN tells you where the cost is. CONCURRENTLY, NOT VALID, and their equivalents let you pay that cost without anyone noticing.
Put together consistently, across every migration, that's what actually keeps a growing database both fast and always available.
References
- PostgreSQL Documentation – Using EXPLAIN (https://www.postgresql.org/docs/current/using-explain.html)
- PostgreSQL Documentation – CREATE INDEX (https://www.postgresql.org/docs/current/sql-createindex.html)
- PostgreSQL Documentation – Building Indexes Concurrently (https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY)
- PostgreSQL Documentation – ALTER TABLE (https://www.postgresql.org/docs/current/sql-altertable.html)
- PostgreSQL Wiki – Don't Do This (Common Anti-Patterns) (https://wiki.postgresql.org/wiki/Don%27t_Do_This)