PostgreSQL Fundamentals Best Practices
Fundamentals hygiene keeps every later optimization honest. These practices apply before you tune indexes or HA: supported versions, clear namespaces, and catalog literacy.
How to Use This List
- Apply on new cluster bootstrap and annual architecture review.
- Use as onboarding checklist for engineers touching SQL or migrations.
- Pair with monitoring and backup sections before go-live.
A - Version and Support
- Run a supported major release. PostgreSQL 18 stable or 17 maintenance with a documented upgrade path.
- Apply minor releases promptly. Security fixes ship in minors only.
- Pin client libpq and drivers. Match server major to avoid protocol edge cases.
- Track extension versions. Record in migrations and verify after upgrade.
B - Namespace and Objects
- Use dedicated schemas per app or tenant. Avoid accidental objects in public.
- Schema-qualify in migrations. Eliminate search_path ambiguity.
- Prefer identity columns for new PKs. Clearer than serial for standards alignment.
- Document timezone policy. timestamptz in UTC at the database boundary.
C - Operations Mindset
- Treat Postgres as a service with SLOs. Backups, monitoring, and on-call are not optional.
- Know your catalogs. Automate introspection instead of guessing schema.
- Test restores, not just backups. A backup without restore drill is wishful thinking.
- Keep connections pooled. Protect the postmaster from unbounded backends.
FAQs
What is the minimum for a dev laptop?
Docker Postgres 18 plus pinned migrations; still use timestamptz and constraints.
Should dev match prod major?
Yes. Minor skew is OK; major skew hides incompatibilities.
How often revisit fundamentals?
Quarterly quick pass; full ADR review yearly.
Related
- PostgreSQL Basics - hands-on tour
- Versioning & Upgrade Policy - release cadence
- Installation Best Practices - cluster bootstrap
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.