Zero-Downtime PostgreSQL Migrations: Locks, Expand-Contract and Safe Backfills

Learn how to change a PostgreSQL schema in production without taking the service down: lock_timeout and the lock queue, indexes with CONCURRENTLY, NOT VALID + VALIDATE constraints, the expand-contract pattern for renames and type changes, and batched backfills. With ready-to-use SQL and Python and the mistakes that cause silent outages.

14 minutes read

2026-07-05

0 views

Was this resource helpful?

Share your comments or suggestions to improve our content.

Loading comments...

Related Resources

Guía

Database Indexing: Designing Indexes and Optimizing Queries in PostgreSQL

Learn to design indexes that genuinely speed up your queries: B-tree, composite, partial, and covering indexes. Includes how to read EXPLAIN ANALYZE, correct column ordering, the mistakes that silently disable an index, and what's new in PostgreSQL 18.

Guía

Idempotency in APIs: Designing Endpoints That Are Safe to Retry

Learn how idempotency keys turn unreliable networks into predictable APIs, so retries, timeouts, and double-clicks never create a duplicate payment or order. Covers server-side implementation, concurrency handling, what to cache, and common pitfalls.

Guía

PREMIUM

Keyset Pagination in PostgreSQL: Why OFFSET Doesn't Scale (and How to Fix It)

Learn why LIMIT/OFFSET pagination falls apart on large tables and how to replace it with keyset (cursor) pagination: tuple comparison, the right composite index, opaque cursors for your API, common pitfalls, and when to use each approach. Includes production-ready code in SQL and Python.