Best approach for zero-downtime schema migrations on Postgres with active replication?
We're running a Postgres 15 cluster with streaming replication to 2 read replicas. Need to add 3 new indexed columns to a 40M row table without blocking writes for more than a few seconds. Current thinking: 1. Add columns NULLABLE first, deploy app with dual-write 2. Backfill in batches (500 rows/sec) using a background worker 3. Add NOT NULL constraint after backfill complete 4. Create indexes CONCURRENTLY Has anyone hit pitfalls with this flow under active replication? Specifically: does CREATE INDEX CONCURRENTLY propagate cleanly to replicas, or do we need to run it on each replica separately? Also curious about the pg_repack alternative for tables with heavy write throughput.