Zero-Downtime PostgreSQL Migrations in NestJS: The Expand-and-Contract Playbook

A table with a few million rows and one careless ALTER TABLE is all it takes to turn a routine deploy into an incident. I've watched a "just adding a column" migration take down an API for six minutes because nobody thought about what was sitting in the lock queue behind it.

The root cause is almost always the same: certain PostgreSQL DDL operations grab an ACCESS EXCLUSIVE lock on the table. Once that lock request is queued, every SELECT and INSERT behind it queues too — and in a NestJS app with a connection pool of, say, 20 connections, that pool is exhausted in seconds. Here's what actually causes it, and how to avoid it in a real TypeORM/Kysely + NestJS setup.

Why a "simple" migration takes down production

Three PostgreSQL behaviors cause most of the damage:

• Lock queue blockage. An ACCESS EXCLUSIVE lock request sits behind whatever long-running query is currently touching the table. Every query that comes in after it — even a trivial read — gets stuck waiting behind the lock request, not behind the original query. That's how one slow query turns into a full pool exhaustion.
• Blocking index creation. A plain CREATE INDEX locks the table against writes for the entire time it takes to build the index. On a large table, that could be minutes.
• Instant breaking changes. Rename a column in one migration and every running app container still querying the old name breaks immediately — no grace period, no rolling deploy safety net.

None of these are edge cases. They're what happens by default if you write migrations the way most ORM tutorials teach you to.

Fix #1: build indexes with CONCURRENTLY, always

The fix for blocking index builds is one keyword:

-- Migration SQL (must run outside a standard transaction block)
CREATE INDEX CONCURRENTLY idx_users_organization_status
ON users (organization_id, status);

CONCURRENTLY builds the index without taking the exclusive write lock, so the table stays writable the whole time. The catch: it cannot run inside a multi-statement transaction block. If your migration runner wraps every migration in BEGIN...COMMIT by default — TypeORM does — you need to explicitly disable that wrapping for any migration that uses CONCURRENTLY, or it'll fail outright. This is the single most common reason people give up on CONCURRENTLY and quietly go back to blocking builds. Don't. Fix the runner config instead.

Fix #2: rename columns with expand-and-contract, not in one shot

Never ship ALTER TABLE users RENAME COLUMN phone TO mobile_phone; as a single migration. The moment it runs, every app pod still holding the old query plan starts throwing errors — there's no rolling-deploy window where old and new code can coexist.

The expand-and-contract pattern spreads the same rename across four deploys instead of one:

It looks like overkill for a one-line rename, and for a small table it is — just rename it and move on. The pattern pays off once you have multiple app instances mid-rollout or a table where a full backfill takes real time; that's exactly the situation where a same-shot rename guarantees a window of 500s.

Here's what the dual-write phase looks like as a TypeORM entity:

// src/users/user.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, BeforeInsert, BeforeUpdate } from 'typeorm';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  // Old column (kept during transition)
  @Column({ name: 'phone', nullable: true })
  phone?: string;

  // New column
  @Column({ name: 'mobile_phone', nullable: true })
  mobilePhone?: string;

  @BeforeInsert()
  @BeforeUpdate()
  syncFields() {
    // Keep both columns in sync during Phase 2 transition
    if (this.mobilePhone && !this.phone) this.phone = this.mobilePhone;
    if (this.phone && !this.mobilePhone) this.mobilePhone = this.phone;
  }
}

The @BeforeInsert/@BeforeUpdate hooks are doing the actual work here — they're what keeps both columns honest without touching every service that writes to User.

Fix #3: cap lock waits with lock_timeout, don't let them queue forever

Adding a foreign key or constraint still needs a table lock. Instead of hoping the lock acquires quickly, set a hard lock_timeout and let the migration fail fast rather than sit in the queue and take the connection pool down with it:

// migrations/1700000000000-AddOrganizationConstraint.ts
import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddOrganizationConstraint1700000000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // 1. Set lock timeout to 2 seconds max to prevent queue pileups
    await queryRunner.query('SET lock_timeout = "2s"');

    // 2. Add constraint safely
    await queryRunner.query(`
      ALTER TABLE users
      ADD CONSTRAINT fk_user_org
      FOREIGN KEY (organization_id) REFERENCES organizations(id)
      NOT VALID;
    `);

    // 3. Validate constraint asynchronously without table locking
    await queryRunner.query(`
      ALTER TABLE users
      VALIDATE CONSTRAINT fk_user_org;
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query('ALTER TABLE users DROP CONSTRAINT fk_user_org');
  }
}

The NOT VALID + VALIDATE CONSTRAINT split matters as much as the timeout does. Adding the constraint with NOT VALID only checks new rows going forward and takes a brief lock; VALIDATE CONSTRAINT then scans existing rows without holding that exclusive lock for the whole scan. Skip the split and you're back to a full-table lock for as long as validation takes.

The safety matrix, at a glance

If you only take one thing from this: the pattern across all four rows is the same. Don't make PostgreSQL do the expensive, locking part of the work in one step. Split it into a cheap step that changes the schema and a separate step that does the heavy lifting without an exclusive lock. Once that split clicks, most "zero-downtime migration" problems turn into the same three-move solution.