Zero-Downtime Schema Changes in PostgreSQL: Adding Columns and Indexes to a 200M-Row Table
You have a PostgreSQL table with 200 million rows . It's serving live traffic. Now you need to add a column or an index. Run the wrong command and every INSERT , UPDATE , and DELETE hangs until it finishes. On a table this size, that can be hours. Here is how to do it safely. The core problem: locks Every DDL statement ( CREATE , ALTER , DROP , TRUNCATE ) takes a lock on the table. What matters…
This article explains how to perform schema changes on a massive PostgreSQL table with 200 million rows without causing downtime. The key issue is that every DDL statement (CREATE, ALTER, DROP, TRUNCATE) locks the table, which can bring live traffic to a grinding halt.
To add a column, the safest approach is to add a nullable column with no default value, which only changes the catalog metadata and finishes in milliseconds. For columns with a default value, PostgreSQL 11 and later can add the column with a default instantly. However, adding a NOT NULL column directly is not recommended; instead, do it in steps: add a nullable column first, write the default value from your application, backfill old rows in batches, add a CHECK constraint without scanning the entire table, validate the constraint, set the column to NOT NULL, and finally drop the redundant CHECK constraint.
When backfilling old rows, update in small batches instead of a single massive UPDATE to avoid a huge transaction, bloat, replication lag, and dead tuples.
Adding an index the wrong way is to use CREATE INDEX, which takes a SHARE lock and blocks all writes for the entire operation, potentially causing hours of downtime on a table of this size. The correct way is to use CREATE INDEX CONCURRENTLY, which scans the table twice and picks up changes made in between without blocking writes. Running it off-peak and cleaning up invalid indexes is essential.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
