Schema Evolution: Adding Columns Without Downtime

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 zero-downtime migrations.
• Adding a nullable column is always safe and fast. Adding a NOT NULL column requires a multi-step process to avoid a table rewrite.
• CREATE INDEX CONCURRENTLY builds indexes without blocking reads or writes. Always use it on production tables.
• The expand-contract pattern is the framework behind all zero-downtime schema changes: add the new structure, migrate data and code, 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 one-step process. In production, on a table with 50 million rows and 500 concurrent connections, the 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 zero-downtime migrations are possible for almost every schema change, but they require a different mental model than sequential migration scripts. Each schema change becomes a multi-step process: 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 after the migration is verified. For Velmora, adopting this process reduced migration-related incidents from a regular occurrence to zero, at the cost of making migrations take longer to plan and execute.

Common mistakes

1. 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).

1. 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.

1. 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.

1. 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.

1. Not testing migrations against a production-size database. A migration that completes in 10 seconds on the 100,000-row staging database may take 30 minutes on the 100-million-row production database. 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-contract pattern before proceeding.

Where to start

1. 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 multi-step expand-contract process manageable because each step is a versioned migration file with a clear execution record.

1. Define a migration policy for the team. Write down the rules: all migrations are backward compatible (no column drops or renames without a multi-step 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.

1. 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 multi-step process. Time each step. Verify that the application runs correctly with the table in intermediate states. This drill builds the muscle memory for zero-downtime migrations before a real migration requires it under production pressure.

Related reading

• Schema Design Decisions That Haunt You at Million User Scale
• Query Optimization in PostgreSQL: Real Examples From Real Projects
• Scaling from One Thousand to One Hundred Thousand Users: The Invisible Database Bottlenecks
• Soft Deletes vs Hard Deletes: A SaaS Data Strategy Debate