PostgreSQL Production Notes

Why Postgres Is A Strong Default

Postgres gives a backend team transactions, constraints, indexes, SQL, JSON support, full-text search, and a mature operational ecosystem in one tool.

Design Principles

• Model important relationships explicitly.
• Use constraints to protect invariants.
• Add indexes for known query paths, not every column.
• Keep migrations reviewed and reversible when possible.
• Avoid long transactions in request paths.

Index Questions

• Which query is slow?
• What is its filter, join, sort, and limit pattern?
• Does the index match that shape?
• Does the index create write overhead?
• Is the query plan using it?

Operational Notes

• Connection pools protect the database from bursty app behavior.
• Backups are not real until restore is tested.
• EXPLAIN is a debugging tool, not a decoration.
• Migrations should be planned for zero or low downtime.

Useful SQL Habit

EXPLAIN ANALYZE
SELECT id, status, created_at
FROM jobs
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;