AdvancedDatabase & Storage

PostgreSQL Zero-Downtime Schema Migrations

Executing DDL commands in Postgres acquires aggressive locks. This recipe shows how to alter tables and build indexes without blocking application reads and writes.

PostgreSQL

Prerequisites

PostgreSQL 12+
Active application workload

Configuration Files

001_safe_migration.sql./migrations/001_safe_migration.sql

SET statement_timeout = '2s';\nSET lock_timeout = '1s';\n\n-- Add column without default to avoid table rewrite\nALTER TABLE users ADD COLUMN new_status VARCHAR(50);\n\n-- Build index concurrently (cannot run in transaction block)\nCOMMIT;\nCREATE INDEX CONCURRENTLY idx_users_status ON users(new_status);

Explanation:Enforces lock timeouts to prevent queueing blockages and uses CONCURRENTLY to build indexes in the background.

Verification Steps

Verifies the index exists.

$psql -c "\\d users"

Expected OutputIndexes:\n "idx_users_status" btree (new_status)

Production Gotchas

CREATE INDEX CONCURRENTLY can fail and leave an INVALID index. You must manually DROP and recreate it if this happens.

Frequently Asked Questions

Why not add a default value to the new column?

In Postgres < 11, adding a default value rewrites the entire table, causing massive downtime.