PostgreSQL Patterns I Reach For in Production

Constraints are the cheapest tests you will ever write

Every application bug eventually meets the database. Foreign keys, unique constraints, and check constraints are the last line of defense, and they never take a day off or forget an edge case during a refactor. If a status column can only hold five values, say so in the schema, not in a comment and a hopeful code review.

This matters more as systems grow. Background jobs, admin scripts, and the occasional manual fix all write to the same tables. Application-level validation protects one path in. Constraints protect every path.

Indexes need receipts

An index added on a hunch is write overhead you pay forever for a read you never measured. My rule is simple: an index earns its place with a real slow query and an EXPLAIN plan before and after. The reverse rule matters too: when a feature dies, its indexes should be reviewed for removal instead of haunting the table for a decade.

Partial indexes are the underused gem here. A queue table where most rows are completed and a few are pending does not need the whole history indexed, just the small slice your workers actually scan.

“The database is not a passive bucket under your application. It is a collaborator with strong opinions, if you let it have them.”

-- Only pending jobs are ever scanned by the worker loop.
CREATE INDEX jobs_pending_idx
  ON jobs (scheduled_at)
  WHERE status = 'pending';

Migrations are deployments, not chores

Expand, migrate, contract. Add the new column, backfill it in batches, switch reads over, and only then remove the old shape in a later release. Every step is reversible, and no single deploy needs the old and new code to agree on everything at once.

And keep connection pools small on purpose. PostgreSQL would rather run twenty honest connections than two hundred queued ones pretending to be parallelism. Most mysterious database slowness I have chased ended at a pool configured by optimism.