Back to Schema Migration
Q2Question 2 of 2

What is the expand-and-contract pattern for schema migrations

Answer: The expand-and-contract pattern splits a breaking schema change (like renaming a column) into four phases: add the new column, backfill data, switch application code, and drop the old column, so that zero downtime is achieved during rolling deployments.

Key Concepts
  • The expand-and-contract pattern splits a breaking schema change into multiple backward-compatible migrations deployed over several releases.
  • Phase 1 (expand) adds the new column alongside the old one so both old and new application versions can coexist.
  • Phase 2 (backfill) copies data from the old column to the new column for all existing rows.
  • Phase 3 (switch) updates the application to read and write the new column, while the old column remains for rollback safety.
  • Phase 4 (contract) drops the old column only after all application instances have been verified on the new column.
  • A single ALTER TABLE RENAME COLUMN breaks rolling deployments because old application instances still reference the old column name.
  • Each Flyway migration script is a separate versioned file, and each phase is deployed in a separate release cycle.

Definition

The expand-and-contract pattern splits a breaking schema change (like renaming a column) into four phases: add the new column, backfill data, switch application code, and drop the old column, so that zero downtime is achieved during rolling deployments.
  • Expand-and-contract is a database migration strategy that avoids breaking changes by expanding the schema first (adding new structures alongside old ones) and contracting later (removing old structures after all consumers have migrated).
  • Backward-compatible migration means the new schema works with both the current and previous versions of the application simultaneously.
  • Rolling deployment is a deployment strategy where new application instances replace old ones gradually, so both versions run at the same time for a period.
  • Flyway is a migration tool that executes versioned SQL scripts (V1__, V2__) in order and tracks applied migrations in a history table.
  • Backfill is the process of copying or transforming data from old columns to new columns for all existing rows.

Think of renaming the "Account Holder" field to "Customer Name" on every paper form at National Bank of India. You cannot recall all forms overnight because 500 branches use them daily. Phase 1: print new forms with both fields (Account Holder and Customer Name). Phase 2: a clerk copies the Account Holder value into the Customer Name field on every existing form. Phase 3: train all branches to read and write only the Customer Name field. Phase 4: once every branch uses Customer Name, remove the Account Holder field from the next print run. At no point does any branch have a form it cannot read.

Understanding the Concepts

What is the expand-and-contract pattern?

The expand-and-contract pattern is a strategy for making breaking schema changes in a production database without downtime. Instead of making the change in one step (which requires stopping all application instances), you split it into multiple backward-compatible steps deployed across several releases.

The pattern has four phases:

  1. Expand: Add the new column (or table) alongside the old one. Both exist at the same time.
  2. Backfill: Copy data from the old column to the new column for all existing rows.
  3. Switch: Update the application to read and write the new column. The old column stays for rollback safety.
  4. Contract: Drop the old column after confirming all application instances use the new one.

Each phase is a separate Flyway migration deployed in a separate release. This means you need at least two releases (and often four) to complete a single column rename.

Why does a single ALTER TABLE RENAME break rolling deployments?

During a rolling deployment, both old and new versions of the application run simultaneously. The old version reads account_holder. The new version reads customer_name. If you rename the column in one step, one of the two versions fails.

SQL
-- WRONG: Single-step rename
ALTER TABLE accounts RENAME COLUMN account_holder TO customer_name;
TEXT
Timeline during rolling deployment:

  10:00  All pods running V1 (reads account_holder)       [OK]
  10:01  Flyway runs: RENAME account_holder -> customer_name
  10:02  Pod 1 upgraded to V2 (reads customer_name)        [OK]
  10:02  Pod 2 still running V1 (reads account_holder)     [CRASH]
         ERROR: column "account_holder" does not exist
  10:03  Pod 3 still running V1 (reads account_holder)     [CRASH]

Result: 2 out of 3 pods crash immediately.
The deployment must be rolled back, but the column is already renamed.
Rolling back the app does not roll back the database.

The expand-and-contract pattern prevents this by ensuring both column names exist during the transition.

How does Phase 1 work: Add the new column?

Phase 1 adds the new column alongside the old one. This is a non-breaking change because the old application ignores columns it does not know about.

SQL
-- V5__expand_add_customer_name.sql
-- Phase 1: Add new column alongside old column
-- Both old app (account_holder) and new app (customer_name) work.

ALTER TABLE accounts
    ADD COLUMN customer_name VARCHAR(100);
DIAGRAM
Table state after Phase 1:

  accounts
  ┌────────────┬──────────────────┬───────────────┐
  │ account_id │ account_holder   │ customer_name │
  ├────────────┼──────────────────┼───────────────┤
  │ SAV2001    │ Rajesh Kumar     │ NULL          │
  │ SAV2002    │ Priya Sharma     │ NULL          │
  │ SAV2003    │ Pankaj Gupta     │ NULL          │
  └────────────┴──────────────────┴───────────────┘

  Old app (V1): SELECT account_holder FROM accounts  [OK]
  New app (V2): not deployed yet

Deploy this migration with the current application version. No code changes needed yet.

How does Phase 2 work: Backfill data?

Phase 2 copies the existing data from the old column to the new column. After this, both columns have the same values.

SQL
-- V6__backfill_customer_name.sql
-- Phase 2: Copy data from old column to new column

UPDATE accounts
   SET customer_name = account_holder
 WHERE customer_name IS NULL;

For large tables (millions of rows), batch the update to avoid long-running transactions:

SQL
-- V6__backfill_customer_name_batched.sql
-- Phase 2: Batched backfill for large tables

DO $$
DECLARE
    batch_size INT := 10000;
    updated INT;
BEGIN
    LOOP
        UPDATE accounts
           SET customer_name = account_holder
         WHERE customer_name IS NULL
           AND account_id IN (
               SELECT account_id FROM accounts
                WHERE customer_name IS NULL
                LIMIT batch_size
           );
        GET DIAGNOSTICS updated = ROW_COUNT;
        RAISE NOTICE 'Backfilled % rows', updated;
        EXIT WHEN updated = 0;
        COMMIT;
    END LOOP;
END $$;
DIAGRAM
Table state after Phase 2:

  accounts
  ┌────────────┬──────────────────┬───────────────┐
  │ account_id │ account_holder   │ customer_name │
  ├────────────┼──────────────────┼───────────────┤
  │ SAV2001    │ Rajesh Kumar     │ Rajesh Kumar  │
  │ SAV2002    │ Priya Sharma     │ Priya Sharma  │
  │ SAV2003    │ Pankaj Gupta     │ Pankaj Gupta  │
  └────────────┴──────────────────┴───────────────┘

  Both columns have identical data.
  Old app still reads account_holder.  [OK]

Optionally, add a trigger to keep both columns in sync for new writes during the transition:

SQL
-- V7__sync_trigger.sql
-- Keep both columns in sync during the migration window

CREATE OR REPLACE FUNCTION sync_account_name()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        IF NEW.customer_name IS NULL THEN
            NEW.customer_name := NEW.account_holder;
        END IF;
        IF NEW.account_holder IS NULL THEN
            NEW.account_holder := NEW.customer_name;
        END IF;
    ELSIF TG_OP = 'UPDATE' THEN
        IF NEW.account_holder IS DISTINCT FROM OLD.account_holder THEN
            NEW.customer_name := NEW.account_holder;
        ELSIF NEW.customer_name IS DISTINCT FROM OLD.customer_name THEN
            NEW.account_holder := NEW.customer_name;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_account_name
    BEFORE INSERT OR UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION sync_account_name();
TEXT
With the sync trigger active:
  Old app writes: UPDATE accounts SET account_holder = 'Raj Kumar' WHERE account_id = 'SAV2001'
  Trigger fires:  customer_name also set to 'Raj Kumar'

  New app writes: UPDATE accounts SET customer_name = 'Raj Kumar' WHERE account_id = 'SAV2001'
  Trigger fires:  account_holder also set to 'Raj Kumar'

Both columns always in sync, regardless of which app version writes.

How does Phase 3 work: Switch reads and writes?

Phase 3 is an application code change, not a database migration. You update the JPA entity and all queries to use customer_name instead of account_holder.

JAVA
package com.bank.model;

import jakarta.persistence.*;
import java.math.BigDecimal;

@Entity
@Table(name = "accounts")
public class Account {

    @Id
    @Column(name = "account_id")
    private String accountId;

    // BEFORE (V1 app): mapped to account_holder
    // @Column(name = "account_holder", nullable = false)
    // private String accountHolder;

    // AFTER (V2 app): mapped to customer_name
    @Column(name = "customer_name", nullable = false)
    private String customerName;

    @Column(nullable = false, precision = 15, scale = 2)
    private BigDecimal balance;

    public String getAccountId() { return accountId; }
    public String getCustomerName() { return customerName; }
    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }
    public BigDecimal getBalance() { return balance; }
}
DIAGRAM
Table state during Phase 3 (rolling deployment):

  accounts
  ┌────────────┬──────────────────┬───────────────┐
  │ account_id │ account_holder   │ customer_name │
  ├────────────┼──────────────────┼───────────────┤
  │ SAV2001    │ Rajesh Kumar     │ Rajesh Kumar  │
  │ SAV2002    │ Priya Sharma     │ Priya Sharma  │
  └────────────┴──────────────────┴───────────────┘

  Pod 1 (V1): SELECT account_holder FROM accounts  [OK, column exists]
  Pod 2 (V2): SELECT customer_name FROM accounts   [OK, column exists]
  Pod 3 (V2): SELECT customer_name FROM accounts   [OK, column exists]

  Both versions work simultaneously. Zero downtime.

How does Phase 4 work: Drop the old column?

Phase 4 runs only after all application instances have been verified on V2 (using customer_name). This is typically in the next release cycle, days or weeks after Phase 3.

SQL
-- V8__contract_remove_account_holder.sql
-- Phase 4: Remove old column and sync trigger
-- ONLY deploy after confirming zero queries use account_holder.

DROP TRIGGER IF EXISTS trg_sync_account_name ON accounts;
DROP FUNCTION IF EXISTS sync_account_name;

ALTER TABLE accounts DROP COLUMN account_holder;
DIAGRAM
Table state after Phase 4 (final):

  accounts
  ┌────────────┬───────────────┐
  │ account_id │ customer_name │
  ├────────────┼───────────────┤
  │ SAV2001    │ Rajesh Kumar  │
  │ SAV2002    │ Priya Sharma  │
  └────────────┴───────────────┘

  Old column gone. Migration complete.
  Only V2 app runs. No references to account_holder anywhere.

How do you implement this with Flyway and Spring Boot?

Here is the complete project structure showing all four migration files and the application configuration.

TEXT
src/
  main/
    resources/
      db/migration/
        V5__expand_add_customer_name.sql       (Phase 1)
        V6__backfill_customer_name.sql         (Phase 2)
        V7__sync_trigger.sql                   (Phase 2, optional)
        V8__contract_remove_account_holder.sql (Phase 4)
      application.properties
    java/
      com/bank/model/Account.java             (Phase 3: updated entity)
TEXT
# application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/bankdb
spring.datasource.username=bank_app
spring.datasource.password=${DB_PASSWORD}
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.jpa.hibernate.ddl-auto=validate
TEXT
Deployment timeline:

Release 1 (Week 1):
  Deploy V5 + V6 + V7 migrations with V1 application code.
  V5 adds customer_name column.
  V6 backfills data.
  V7 creates sync trigger.
  Application still reads account_holder. No code change.

Release 2 (Week 2):
  Deploy V2 application code (reads customer_name).
  Rolling deployment: V1 and V2 coexist.
  Sync trigger keeps both columns in sync.
  No new migrations in this release.

Release 3 (Week 3):
  All pods confirmed on V2. No queries use account_holder.
  Deploy V8 migration: drop old column and trigger.
  Migration complete.
JAVA
package com.bank.config;

import org.flywaydb.core.Flyway;
import org.springframework.boot.autoconfigure.flyway.FlywayMigrationStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.logging.Logger;

@Configuration
public class FlywayConfig {

    private static final Logger log =
        Logger.getLogger(FlywayConfig.class.getName());

    @Bean
    public FlywayMigrationStrategy flywayMigrationStrategy() {
        return flyway -> {
            log.info("Starting Flyway migration");
            var result = flyway.migrate();
            log.info("Applied " + result.migrationsExecuted
                + " migrations. Current version: "
                + result.targetSchemaVersion);

            if (result.migrationsExecuted > 0) {
                log.info("Migrations applied in this deployment:");
                result.migrations.forEach(m ->
                    log.info("  " + m.version + " - " + m.description
                        + " (" + m.executionTime + "ms)"));
            }
        };
    }
}
TEXT
Startup log (Release 1):
  Starting Flyway migration
  Applied 3 migrations. Current version: 7
  Migrations applied in this deployment:
    5 - expand add customer name (12ms)
    6 - backfill customer name (3450ms)
    7 - sync trigger (8ms)

Startup log (Release 3):
  Starting Flyway migration
  Applied 1 migrations. Current version: 8
  Migrations applied in this deployment:
    8 - contract remove account holder (15ms)

How is expand-and-contract different from other migration strategies?

StrategyDowntimeRiskReleases neededBest for
Single rename (ALTER TABLE RENAME COLUMN)Yes, breaks old instancesHigh: old app crashes1Never in production
Expand-and-contractNoLow: both versions work3 to 4Column renames, type changes
Blue-green with schema copyNoMedium: double storage2Table restructuring
Feature flag in applicationNoLow: flag controls reads2Application-level changes
TEXT
Single rename (WRONG):
  Release 1: ALTER TABLE RENAME + new app code
  Risk: Old pods crash during rolling deployment.

Expand-and-contract (CORRECT):
  Release 1: Add column + backfill      (database change only)
  Release 2: Switch app to new column   (code change only)
  Release 3: Drop old column            (database change only)
  Risk: None. Each step is backward-compatible.

What are common mistakes?

Mistake 1: Combining expand and contract in one migration

WRONG: Adding the new column and dropping the old column in the same migration. If the application is not yet updated, it crashes.

SQL
-- WRONG: V5__rename_column.sql
-- Both operations in one script
ALTER TABLE accounts ADD COLUMN customer_name VARCHAR(100);
UPDATE accounts SET customer_name = account_holder;
ALTER TABLE accounts DROP COLUMN account_holder;
-- Old app crashes immediately: column account_holder does not exist.

CORRECT: Separate the expand and contract into different migrations deployed in different releases.

SQL
-- CORRECT: V5__expand_add_customer_name.sql (Release 1)
ALTER TABLE accounts ADD COLUMN customer_name VARCHAR(100);
UPDATE accounts SET customer_name = account_holder;

-- CORRECT: V8__contract_remove_account_holder.sql (Release 3)
ALTER TABLE accounts DROP COLUMN account_holder;

Mistake 2: Forgetting to backfill existing rows

WRONG: Adding the new column but not copying data. New column is NULL for all existing rows.

SQL
-- WRONG: No backfill
ALTER TABLE accounts ADD COLUMN customer_name VARCHAR(100);
-- customer_name is NULL for SAV2001, SAV2002, SAV2003...
-- When V2 app reads customer_name, it gets NULL for existing accounts.

CORRECT: Always backfill after adding the column.

SQL
-- CORRECT: Add column then backfill
ALTER TABLE accounts ADD COLUMN customer_name VARCHAR(100);
UPDATE accounts SET customer_name = account_holder WHERE customer_name IS NULL;

Mistake 3: Dropping the old column too early

WRONG: Dropping the old column in the same release as the application code change. If the release needs to be rolled back, the rollback app (V1) references the dropped column.

TEXT
-- WRONG timeline:
Release 2:
  Deploy V8 migration (drops account_holder)
  Deploy V2 app code (reads customer_name)

  Problem: V2 has a bug. Rollback to V1.
  V1 reads account_holder. Column is gone. CRASH.

CORRECT: Drop the old column in a separate, later release after V2 is confirmed stable.

TEXT
-- CORRECT timeline:
Release 2:
  Deploy V2 app code only. No migration.
  V2 reads customer_name. V1 pods read account_holder.
  Both columns exist. Both work.

Release 3 (one week later, V2 confirmed stable):
  Deploy V8 migration (drops account_holder).
  Rollback to V2 still works (V2 reads customer_name).

What about NOT NULL constraints during expand-and-contract?

You cannot add a NOT NULL constraint to the new column in Phase 1 because the old application does not write to it. New rows inserted by V1 would have customer_name = NULL and violate the constraint.

Add the NOT NULL constraint in Phase 4, after all application instances write to the new column.

SQL
-- Phase 1: Add column as NULLABLE
ALTER TABLE accounts ADD COLUMN customer_name VARCHAR(100);
-- NOT NULL constraint NOT added yet.

-- Phase 4: After all apps write to customer_name
ALTER TABLE accounts ALTER COLUMN customer_name SET NOT NULL;
ALTER TABLE accounts DROP COLUMN account_holder;

For PostgreSQL, adding NOT NULL with a check constraint is less disruptive on large tables:

SQL
-- Phase 4: Lock-friendly NOT NULL for large tables (PostgreSQL)
ALTER TABLE accounts
    ADD CONSTRAINT chk_customer_name_not_null
    CHECK (customer_name IS NOT NULL) NOT VALID;

ALTER TABLE accounts
    VALIDATE CONSTRAINT chk_customer_name_not_null;

-- NOT VALID skips checking existing rows (we know they are backfilled).
-- VALIDATE checks existing rows with a lighter lock (ShareUpdateExclusive).
-- This avoids AccessExclusive lock from ALTER COLUMN SET NOT NULL.
TEXT
Lock comparison on a table with 10 million rows:

  ALTER COLUMN SET NOT NULL:
    Lock: AccessExclusive (blocks ALL reads and writes)
    Duration: 5-30 seconds (scans all rows)

  ADD CONSTRAINT CHECK NOT VALID + VALIDATE:
    Lock: ShareUpdateExclusive (allows reads, blocks only DDL)
    Duration: same total time, but reads are not blocked

What should a senior or principal engineer know?

- Parallel-safe migrations. In a multi-pod environment, Flyway uses the database's advisory lock to ensure only one pod runs migrations. The other pods wait until the migration completes, then verify the schema. This is safe by default in Spring Boot.

- Migration testing. Test every migration against a dump of production data before deploying. Use flyway info to see pending migrations and flyway validate to check checksums. In CI, run migrations against a PostgreSQL container with production-like data.

- Observing the old column before dropping. Query pg_stat_user_columns (PostgreSQL) or enable query logging to confirm that no queries reference the old column. Do not rely on code search alone because ad-hoc queries, reports, and third-party tools may still use it.

SQL
-- Check if the old column is being read in PostgreSQL
SELECT schemaname, relname, attname, n_live_tup
  FROM pg_stat_user_tables t
  JOIN pg_attribute a ON a.attrelid = t.relid
 WHERE t.relname = 'accounts'
   AND a.attname = 'account_holder';

-- If seq_scan or idx_scan on the old column is zero
-- for 7+ days, it is safe to drop.

- Rollback strategy. Flyway Community Edition does not support automatic rollback. If a migration fails, you write a new migration to undo it. For critical production changes, prepare the undo migration in advance and test it.

- Schema version pinning. Tag each application version with the minimum and maximum schema version it supports. The application startup should verify the schema version and fail fast if it is outside the supported range.

JAVA
package com.bank.config;

import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Component
public class SchemaVersionCheck implements CommandLineRunner {

    private final Flyway flyway;

    @Value("${app.schema.min-version:5}")
    private int minVersion;

    @Value("${app.schema.max-version:8}")
    private int maxVersion;

    public SchemaVersionCheck(Flyway flyway) {
        this.flyway = flyway;
    }

    @Override
    public void run(String... args) {
        var info = flyway.info();
        int currentVersion = Integer.parseInt(
            info.current().getVersion().getVersion());

        if (currentVersion < minVersion || currentVersion > maxVersion) {
            throw new IllegalStateException(
                "Schema version " + currentVersion
                + " is outside supported range ["
                + minVersion + ", " + maxVersion + "]");
        }
    }
}
TEXT
App V1 supports schema versions 1-7 (reads account_holder)
App V2 supports schema versions 5-8 (reads customer_name)

Schema at version 6: both V1 and V2 work.
Schema at version 8: only V2 works. V1 would fail the version check.

What are common interview follow-up questions?

  • Why can you not rename a column in one step during a rolling deployment?
  • How many releases does the expand-and-contract pattern require?
  • What happens if the backfill migration fails halfway through?
  • How do you handle NOT NULL constraints during expand-and-contract?
  • What is the difference between Flyway's versioned and repeatable migrations?
  • How do you ensure only one pod runs migrations in a multi-instance deployment?
  • How do you test migrations before applying them to production?
  • What is the impact of a long-running UPDATE on a large table during backfill?

Visual Overview

DIAGRAM
  Expand-and-Contract: 4 Phases
  ================================

  Phase 1: EXPAND (add new column)
  ┌────────────────────────────────────────────┐
  │  accounts                                  │
  │  ┌────────────┬────────────┬─────────────┐ │
  │  │ account_id │ acct_holder│ cust_name   │ │
  │  ├────────────┼────────────┼─────────────┤ │
  │  │ SAV2001    │ Rajesh     │ NULL        │ │
  │  └────────────┴────────────┴─────────────┘ │
  │  Old app reads acct_holder  [OK]           │
  │  New column exists but is empty            │
  └────────────────────────────────────────────┘
            │
            ▼
  Phase 2: BACKFILL (copy data)
  ┌────────────────────────────────────────────┐
  │  UPDATE accounts                           │
  │    SET cust_name = acct_holder             │
  │                                            │
  │  ┌────────────┬────────────┬─────────────┐ │
  │  │ SAV2001    │ Rajesh     │ Rajesh      │ │
  │  └────────────┴────────────┴─────────────┘ │
  │  Both columns have same data               │
  │  Sync trigger keeps them in sync           │
  └────────────────────────────────────────────┘
            │
            ▼
  Phase 3: SWITCH (new app code)
  ┌────────────────────────────────────────────┐
  │  V1 pod: SELECT acct_holder       [OK]     │
  │  V2 pod: SELECT cust_name         [OK]     │
  │  Both columns exist, both work             │
  │  Zero downtime during rolling deployment   │
  └────────────────────────────────────────────┘
            │
            ▼
  Phase 4: CONTRACT (drop old column)
  ┌────────────────────────────────────────────┐
  │  DROP COLUMN acct_holder                   │
  │  DROP TRIGGER sync_trigger                 │
  │                                            │
  │  ┌────────────┬─────────────┐              │
  │  │ SAV2001    │ Rajesh      │              │
  │  └────────────┴─────────────┘              │
  │  Only cust_name remains. Migration done.   │
  └────────────────────────────────────────────┘
DIAGRAM
  Deployment Timeline
  =====================

  Week 1          Week 2          Week 3
  ┌───────────┐   ┌───────────┐   ┌───────────┐
  │ Release 1 │   │ Release 2 │   │ Release 3 │
  │           │   │           │   │           │
  │ V5: add   │   │ App V2:   │   │ V8: drop  │
  │   column  │   │ reads new │   │   old col │
  │ V6: back- │   │   column  │   │   + trigger│
  │   fill    │   │           │   │           │
  │ V7: sync  │   │ No DB     │   │           │
  │   trigger │   │ migration │   │           │
  └───────────┘   └───────────┘   └───────────┘
       │               │               │
   App V1 only    V1 + V2 coexist   App V2 only

Key Takeaway

The expand-and-contract pattern is the safe way to make breaking schema changes in a production database without downtime. Instead of renaming a column in one step (which crashes old application instances during a rolling deployment), you split the change into four phases deployed across multiple releases.

Phase 1 adds the new column alongside the old one. Phase 2 backfills existing data from the old column to the new column. Phase 3 updates the application code to read and write the new column. Phase 4 drops the old column and the sync trigger after all instances are confirmed on the new version. Each phase is a separate Flyway migration, and each phase is backward-compatible with both the old and new application versions.

The most common mistake is combining the expand and contract steps in one migration or one release. If you add the new column and drop the old column at the same time, old application pods crash immediately. If you drop the old column in the same release as the code change, you lose the ability to roll back safely.

For large banking tables with millions of rows, batch the backfill to avoid long-running transactions, and use PostgreSQL's CHECK constraint with NOT VALID instead of ALTER COLUMN SET NOT NULL to avoid blocking reads during the constraint validation.

Remember one sentence: expand first (add the new column), backfill data, switch the application code, and contract last (drop the old column) in a separate release.

---

Share
Use ← → arrow keys to navigate