2G

During a rolling deployment, old and new application pods run simultaneously and access the same database. Every schema change must be safe for both.

This guide explains how to roll out database changes without downtime by using additive migrations, phased renames and type changes, careful backfills, and delayed cleanup only after old code paths are gone.

Quick Definitions

• Rolling deployment: a release where old and new application instances run at the same time for a period
• Idempotent migration: a migration that can be run more than once without causing a different end result or an error
• Backfill: updating existing rows so older data matches a new schema requirement
• Dual-write: temporarily writing the same value to both the old and new columns during a migration

Migration Tooling

Use Flyway or Liquibase – never run manual schema changes in production.

• Flyway docs: https://documentation.red-gate.com/fd
• Liquibase docs: https://docs.liquibase.com

-- Flyway: V3__add_status_column_to_users.sql
-- Naming: V{version}__{description}.sql
-- Idempotent: can be run multiple times without error

Migrations run before the application deploy, so:

• New schema is in place when new pods start
• Old pods must still work with the new schema (it must be additive)
• New pods can start using new schema fields

Takeaway: Use migration tooling to make schema changes repeatable, reviewed, and safe to apply before new application code starts using them.

Adding Columns

Safe => make the new column nullable or give it a default so existing rows and old-code inserts aren’t broken.

-- Nullable (safest — no default needed, old code doesn't break)
ALTER TABLE users ADD COLUMN status VARCHAR(20);
-- With default (safer if old code doesn't set the field but new code reads it)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

Danger: ANTI-PATTERN

Adding a NOT NULL column without a default to a table with existing rows.

-- ❌ This fails immediately on a table with data
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
-- ✅ Add nullable first, backfill, then add constraint
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
UPDATE users SET phone = 'N/A' WHERE phone IS NULL;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Real-World Scenario

Suppose the users table needs a new status field for account lifecycle logic. If older application code still inserts rows without status, a nullable column or a safe default keeps those inserts working while newer code gradually starts populating the field intentionally.

Takeaway: additive column changes are usually safe, but NOT NULL without a migration plan is a production outage pattern.

Renaming Columns

Never rename directly; it breaks all old-code reads and writes instantly. Use this 5-step phased approach:

Phase 1: Add new column (nullable)
Phase 2: Dual-write to both old and new columns in application code
Phase 3: Backfill existing rows
Phase 4: Migrate reads to new column; stop writing old column
Phase 5: Drop old column (after all old pods are gone)

Rename Diagram

Phase 1: Add full_name column
         old code -> name
         new code -> name + full_name
Phase 2: Dual-write and backfill
         writes -> name + full_name
         reads  -> name
Phase 3: Move reads, then remove old column
         reads  -> full_name
         writes -> full_name
         drop   -> name

-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
-- Phase 3: Backfill (run during low traffic; use batching for large tables)
UPDATE users SET full_name = name WHERE full_name IS NULL;
-- Phase 5: Drop old column (only after all application code uses full_name)
ALTER TABLE users DROP COLUMN name;

Backfill Large Tables in Batches

For tables with millions of rows, a single UPDATE can lock the table:

-- Batch backfill: run in a loop with pg_sleep to reduce lock contention
DO $$
DECLARE
    batch_size INT := 1000;
    rows_updated INT;
BEGIN
    LOOP
        UPDATE users SET full_name = name
        WHERE full_name IS NULL
        LIMIT batch_size;
        
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;
        PERFORM pg_sleep(0.1);  -- brief pause between batches
    END LOOP;
END $$;

Common Pitfall

Renaming name to full_name directly with ALTER TABLE ... RENAME COLUMN ... looks simple, but any still-running pod that reads name will start failing immediately. The database migration succeeds, while the application breaks.

Takeaway: treat renames as multi-step compatibility work, not a single DDL command.

Dropping Columns

Never drop immediately. Three-step deprecation:

Step 1: Stop writing to the column in the application code (deploy this)
Step 2: Wait one full deploy cycle, confirm no reads in code or monitoring
Step 3: Drop the column

-- Step 3
ALTER TABLE users DROP COLUMN deprecated_column;

Warning

Wait at least one full deployment cycle (all pods on new code) between stopping reads/writes and dropping the column. A column read by any in-flight pod will cause errors.

Real-World Scenario

A cleanup task may still export deprecated_column long after the main request path stopped using it. If the column is dropped too early, the breakage shows up later in batch jobs or admin tooling instead of immediately in the main service.

Takeaway: Before dropping a column, verify both request paths and background jobs have stopped reading it.

Changing Data Types

Changing a column’s type (e.g., VARCHAR → INTEGER) breaks all running old-code queries. Use a parallel column approach:

Phase 1: Add new column with desired type
Phase 2: Dual-write with type conversion (write both old and new)
Phase 3: Backfill existing rows (with batch UPDATE)
Phase 4: Migrate reads to new column
Phase 5: Drop old column

Type-Change Diagram

Phase 1: Add age_int alongside age_str
Phase 2: Write both values during new inserts/updates
Phase 3: Backfill old rows from age_str -> age_int
Phase 4: Move reads to age_int
Phase 5: Drop age_str

-- Phase 1
ALTER TABLE users ADD COLUMN age_int INTEGER;
-- Phase 3 (backfill with type conversion)
UPDATE users SET age_int = CAST(age_str AS INTEGER)
WHERE age_int IS NULL AND age_str ~ '^\d+$';  -- only valid integers
-- Phase 5
ALTER TABLE users DROP COLUMN age_str;

Common Pitfall

If some rows contain non-numeric values such as unknown or N/A, A direct cast can fail or produce partial data cleanup work in the middle of the rollout. Validate dirty data before assuming a type conversion is straightforward.

Takeaway: Type changes are data migrations as much as schema migrations. Plan for invalid historical data.

Creating Indexes

Always use concurrent creation to avoid locking the table during index build.

-- ❌ Blocks reads and writes — NEVER use on production tables with data
CREATE INDEX idx_users_email ON users(email);
-- ✅ Safe — runs in background, doesn't lock
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

Warning

CREATE INDEX CONCURRENTLY takes longer but keeps the table available. It cannot run inside a transaction. Plan for it and don’t run it during peak traffic windows.

Takeaway: on production tables, prefer slower index builds over blocking writes and reads.

Adding Constraints

NOT NULL constraint

Backfill null values first, and then adding NOT NULL to a column with nulls fails immediately.

-- Step 1: Backfill
UPDATE users SET phone = 'N/A' WHERE phone IS NULL;
-- Step 2: Add constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Foreign Key Constraint

For large tables, use NOT VALID to avoid a full table lock during constraint creation:

-- Step 1: Add constraint without validating existing rows (fast, no table lock)
ALTER TABLE orders
  ADD CONSTRAINT fk_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;
-- Step 2: Validate existing rows separately (runs concurrently, no lock)
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

Common Pitfall

Constraint additions often fail not because the rule is wrong, but because existing data already violates it. Check the production data shape before assuming a constraint can be added immediately.

Takeaway: constraints should usually follow cleanup and backfill work, not come before them.

Summary: Change Phases

Written by

Balaji G