scaling

The startup's Postgres survival guide

2 months ago

Start with good schema design using identity columns, primary keys, timestamptz, and foreign keys with cascading deletes for low-volume tables.
Write performant queries by using indexes (primary keys, unique constraints) to avoid sequential scans; use compound indexes with ORDER BY columns last.
Keep transactions short, lock only necessary rows, and use CREATE INDEX CONCURRENTLY to avoid blocking writes.
Use connection pooling (pgbouncer or in-memory pools like pgxpool) to manage connections efficiently.
Understand the query planner: use EXPLAIN ANALYZE to debug slow queries, keep table statistics updated via autovacuum.
Batch writes to improve throughput; tune autovacuum settings to prevent dead tuple bloat and transaction ID wraparound.
Use advanced features: FOR UPDATE SKIP LOCKED for queue/lease operations, partitioning for time-series data, and triggers for large table migrations without long transactions.