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+.