Postgres Project Rules Checklist

25 verification rules for schemas, queries, migrations, and operations. Use at project kickoff, quarterly audits, and before major releases.

How to Use This Checklist

• Work top to bottom for new projects; skip sections already certified.
• Check items in PR templates for database-touching changes.
• Failed items become tickets with owner and due date - not meeting notes.

Schema & Naming (1-5)

• 1. snake_case for tables, columns, and constraints. No camelCase in SQL objects.
• 2. Singular or plural table names - pick one team-wide. Document in ADR; never mix user and orders.
• 3. Primary keys named id or {table}_id consistently. Surrogate bigint/identity preferred.
• 4. timestamptz for all instants; never timestamp without time zone for events. Timezone bugs are expensive.
• 5. Foreign keys on relationships the app relies on. Referential integrity in DDL, not hope.

Indexes & Queries (6-10)

• 6. EXPLAIN (ANALYZE, BUFFERS) on new API queries before merge. No unbounded seq scans on large tables.
• 7. Every production query has a row bound or aggregation limit. APIs do not SELECT * millions of rows.
• 8. Index columns match WHERE and JOIN predicates leftmost. Composite order documented in PR.
• 9. Partial indexes where queries always filter status/tenant. Smaller, faster, cheaper writes.
• 10. Remove unused indexes yearly. pg_stat_user_indexes.idx_scan = 0 candidates reviewed.

Migrations (11-15)

• 11. Versioned SQL in git; no manual prod DDL. Hotfixes backfilled into migration files same day.
• 12. lock_timeout set in migration scripts. Fail fast, do not block traffic.
• 13. Large table DDL uses CONCURRENTLY and NOT VALID patterns. See zero-downtime guide.
• 14. Expand/contract for breaking changes. No same-release DROP COLUMN with app rollback risk.
• 15. CI migrates empty database on every PR. Grants and ordering validated automatically.

Security & Access (16-20)

• 16. Application never uses superuser. Break-glass roles logged and rare.
• 17. Least-privilege app role; migrator separate. Owner role is NOLOGIN.
• 18. RLS on tenant data in shared schema with FORCE. Index tenant_id in policies.
• 19. SCRAM-SHA-256 auth; no trust on TCP. pg_hba.conf default-deny network.
• 20. TLS required for remote connections. hostssl in hba for app subnets.

Operations (21-25)

• 21. Run current minor of supported major. PostgreSQL 13 EOL passed - upgrade plan exists.
• 22. Backups tested with restore drill quarterly. Backup without restore is wishful thinking.
• 23. pg_stat_statements or equivalent query insights enabled. Top queries reviewed monthly.
• 24. Autovacuum monitored; no long idle-in-transaction sessions. xmin horizon visible in dashboards.
• 25. Connection pooling (PgBouncer) before raising max_connections past 200. Pool math documented.

When You Are Done

• File checklist results in team wiki or audit ticket.
• Schedule remediation sprint for failed ops and security items first.
• Re-run after major version upgrade or tenant isolation change.

Related

• Naming & Style Rules - naming depth
• Index & Query Rules - query standards
• Migration Safety Rules - deploy timeouts
• Security Rules - access control rules

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