Applications and operations
Migrate PostgreSQL safely
Use expand-and-contract changes so old and new application versions can run during a deployment.
8 minute lesson
~~~
Add new structures before code requires them. Backfill in restartable batches, switch reads and writes in separate releases, then remove old structures after the compatibility window.
Bound how long a migration can wait or run:
SET lock_timeout = '2s';
SET statement_timeout = '5min';Large constraints, indexes, and table rewrites can lock or scan production data. Test the exact operation on a realistic copy and decide rollback before deployment.
CREATE INDEX CONCURRENTLY reduces blocking on a live table, but it cannot run inside a transaction block. Treat it as its own restartable migration step.
Lesson completed