Schema Evolution: Adding Columns Without Downtime
Schema evolution refers to the process of modifying a database schema (adding columns, changing types, dropping tables, creating indexes) while the application is running and serving production traffic. Migrating schema without downtime requires careful sequencing of DDL statements, application code changes, and data backfills to avoid table locks that block reads and writes, and to maintain compatibility between the old and new application code during the deployment window.
What you need to know
- Most DDL operations that cause downtime do so because they hold an exclusive table lock. Understanding which operations require locks and which do not is the foundation of migrating without downtime.
- Adding a nullable column is always safe and fast. Adding a NOT NULL column takes a few more careful steps to avoid a table rewrite.
- CREATE INDEX CONCURRENTLY builds indexes without blocking reads or writes. Always use it on production tables.
- The expand and contract pattern is the framework behind every zero downtime schema change: add the new structure, migrate data and code, then remove the old structure.
- Backfilling large tables must be done in batches. A single UPDATE on millions of rows creates a long running transaction and table bloat.
The core argument
Schema migrations are where well intentioned engineering practice diverges sharply from production reality. In a tutorial environment, running a migration file is a single step. In production, on a table with 50 million rows and 500 concurrent connections, that same migration can cause a full service outage if executed without care.
The source of danger is the lock hierarchy. PostgreSQL acquires locks at different levels for different operations, and certain DDL statements acquire an AccessExclusiveLock that blocks every read and write until it completes. On a small table, this lock is held for milliseconds. On a large table, the same operation takes minutes, during which the application sees every database call timeout or queue, and connection pools exhaust.
The good news is that migrating with zero downtime is possible for almost every schema change, but it requires a different mental model than a sequential migration script. Each schema change becomes several steps: an expand phase that adds structure without removing anything, a migration phase where application code and data catch up to the new structure, and a contract phase where the old structure is removed once the migration is verified. For Velmora, adopting this process took migration incidents from a regular occurrence down to zero, at the cost of making migrations take longer to plan and execute.
Common mistakes
Running migrations in the same deployment as the application code change. When the migration and the code change deploy simultaneously, there is no safe rollback: if the application fails after the migration runs, rolling back the application to the previous version leaves it running against the new schema. Deploy schema migrations separately, before the application code that depends on them. This requires backward compatible migrations (new nullable columns, not renamed or dropped columns).
Backfilling large tables in a single UPDATE statement. UPDATE without a WHERE clause on a large table locks every row for the duration of the update, preventing concurrent writes. Batch the backfill: process 1,000 to 5,000 rows per transaction, with a brief sleep between batches to allow the autovacuum to reclaim dead tuples and avoid table bloat. The total backfill takes longer but has no impact on application throughput.
Not checking for invalid indexes after a failed CONCURRENTLY build. If CREATE INDEX CONCURRENTLY is interrupted (server restart, network failure), it leaves an index in the pg_indexes catalog marked as invalid. Invalid indexes exist but are not used by the query planner. They do consume space and add maintenance overhead. Check for invalid indexes after any migration that uses CONCURRENTLY and DROP them if found before retrying the build.
Dropping columns immediately when application code stops using them. When application code is updated to stop using a column, the column can technically be dropped immediately. But if the deployment is rolled back, the old application code reading the dropped column will fail. Leave dropped columns in place for at least one deployment cycle after the application code has stopped using them. Then drop in the contract phase once rollback is no longer required.
Not testing migrations against a production size database. A migration that completes in 10 seconds on a staging database with 100,000 rows may take 30 minutes against a production database with 100 million rows. Test migrations on a replica of production data before scheduling the production run. If the migration takes longer than acceptable, redesign it using the expand and contract pattern before proceeding.
Where to start
Adopt a migration tool that supports transactional DDL. Tools like Flyway, Liquibase, or Golang-migrate track migration history in a database table and ensure each migration runs exactly once. The tooling provides the plumbing. The zero downtime patterns above provide the technique. Having consistent tooling makes the multistep expand and contract process manageable because each step is a versioned migration file with a clear execution record.
Define a migration policy for the team. Write down the rules: all migrations are backward compatible (no column drops or renames without an expand and contract process), all indexes use CONCURRENTLY, all backfills run in batches. Codify these rules in a code review checklist for migration files. The policy prevents the mistakes when team members are working quickly under feature pressure.
Run a migration drill on a large table in staging. Take the largest table in the production schema and practice adding a NOT NULL column with a backfill using the expand and contract process. Time each step. Verify that the application runs correctly with the table in intermediate states. This drill builds the muscle memory for migrating without downtime before a real migration requires it under production pressure.
FAQ
Frequently asked
- What operations in PostgreSQL require a table lock and how long do they hold it?
- How do you add a new NOT NULL column to a large production table without downtime?
- How do you change a column type without downtime?
- What is the expand and contract migration pattern?
- How do you create an index on a large production table without blocking traffic?
Author
Why I am the right person for this kind of build
I do not have a degree yet. I do not need one. I have shipped Dwarka Bricks, Expert Tutorials, Prominence Football Academy, Velmora, and Nexli. The work is on real URLs, used by real people. Yashveer Singh, founder of Yashveer Labs. If the topic on this page is the one you are facing right now, I have done it for someone else and I can do it for you.