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.

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

Official PostgreSQL Documentation - Central hub for all manuals.
Awesome Postgres - Curated collection of tools, libraries, extensions, and resources.
PostgreSQL.org Main Site - Downloads, news, events, and community portal.
Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.
PostgreSQL: Up and Running (3rd Edition) - Regina O. Obe & Leo S. Hsu.

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

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.

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

Official PostgreSQL Documentation - Central hub for all manuals.
Awesome Postgres - Curated collection of tools, libraries, extensions, and resources.
PostgreSQL.org Main Site - Downloads, news, events, and community portal.
Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.
PostgreSQL: Up and Running (3rd Edition) - Regina O. Obe & Leo S. Hsu.

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

Postgres Project Rules Checklist

Search Documentation

Postgres Project Rules Checklist