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

Related

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