9 hours 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.