PostgreSQL Rules Best Practices

How to make Postgres project rules stick: automate linting, embed checks in CI, and keep human review for what machines cannot judge.

How to Use This List

Start with automation (section A) before writing more prose rules.
Align PR templates with section B checklist items.
Audit rule drift quarterly using section C.

A - Automation

SQLFluff (or sql-lint) on db/migrations/** in CI. Fail PR on parse and layout violations.
Custom lint: require SET lock_timeout in migration files. Regex or AST check in pipeline.
pgTAP or psql smoke scripts post-migrate. Schema assertions automated.
SQLFluff naming conventions match ADR. snake_case, index prefix idx_.
Pre-commit hook locally mirrors CI. Developers see failures before push.

B - Review Gates

PR template checkbox: EXPLAIN attached for new hot-path queries. Link to EXPLAIN (ANALYZE, BUFFERS) output.
DBA review required for tables > 10M rows or ACCESS EXCLUSIVE DDL. CODEOWNERS on migrations folder.
Security review when RLS, BYPASSRLS, or SECURITY DEFINER touched. No rubber-stamp on policy changes.
ADR filed for topology, tenancy, or engine changes. Not for every column add.
Rejected alternatives noted in ADR. Future teams understand tradeoffs.

C - Living Standards

Quarterly run Postgres Project Rules Checklist. Track pass/fail trend.
Post-incident update to rules within 5 business days. Incidents teach what checklists missed.
Onboarding doc links rules + psql basics + least privilege. One path for new engineers.
Version bump rules doc when PostgreSQL major upgrades. Behavior changes (DDL, planner) need rule refresh.
Retire rules that automation enforces. Replace manual bullets with linter rule IDs in docs.

D - Tooling Examples

# SQLFluff CI example
sqlfluff lint db/migrations --dialect postgres
 
# Custom migration header check
grep -L "lock_timeout" db/migrations/V*.sql && exit 1 || true

# CODEOWNERS excerpt
/db/migrations/  @data-platform-team
/docs/adr/       @staff-engineers @dba-team

Pin SQLFluff version in CI for reproducible lint results.
Document suppressions with -- noqa: RULE and PR justification only.

E - Culture

Rules are guardrails, not bureaucracy - explain why in one line each. Onboarding retention improves.
SME office hours for rule questions. Reduces shadow IT workarounds.
Celebrate caught-in-CI catches. Reinforces automation investment.
No prod exception without timed ADR amendment. Exceptions rot into defaults.
Sync rules with security/compliance control IDs. SOC2 auditors map checks to evidence.

FAQs

SQLFluff vs hand review?

Lint catches style and some anti-patterns; humans judge plan quality and capacity impact. Both required.

Rules for analysts?

Subset: read-only access, LIMIT, no prod writes - link psql best practices doc.

Postgres Project Rules Checklist - 25 audit items
Naming & Style Rules - SQLFluff config input
Git for Database Work Basics - PR workflow

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

Search Documentation

PostgreSQL Rules Best Practices