AcademyEventsKnowledge baseShopSign in

PostgreSQL: zero-downtime migrations

DEVELOPMENT
PostgreSQL: zero-downtime migrations

Running ALTER TABLE at peak traffic can take down iGaming or e-commerce on Saturday. Design migrations like releases: expand → deploy → contract.

PostgreSQL: zero-downtime migrations
Иллюстрация к материалу

Expand / contract

Add nullable column → deploy dual-write → backfill → NOT NULL → drop old.

Indexes

  • CREATE INDEX CONCURRENTLY in separate migration
  • Never lock orders/wallet for minutes

Sequelize / raw SQL

  • Idempotent up/down where possible
  • Heavy migrations — maintenance window or flag read path
PostgreSQL: zero-downtime migrations
Workflow diagram

Checklist

  • Staging mirrors prod schema
  • Rollback documented
  • Monitor lock wait

When a migration hurts prod

  • Check pg_stat_activity for locks
  • Postpone non-urgent DDL to a window
  • Use CREATE INDEX CONCURRENTLY
  • Split expand (nullable) and contract (NOT NULL) across releases