Pragmatic Database Migrations in PostgreSQL

As data volume grows, unindexed foreign keys and table-locking migrations become major availability bottlenecks.

Composite Indexing for Temporal Queries

When filtering by tenant or user ID and ordering by creation timestamp, composite indexes are essential:

CREATE INDEX CONCURRENTLY idx_orders_user_created 
ON orders (user_id, created_at DESC);

Safe Column Additions

• Always use ADD COLUMN ... DEFAULT NULL when adding nullable fields to large tables.
• Populate default values in batched background workers to avoid long-running exclusive locks.