A PostgreSQL Review Checklist

Questions to ask

Are types exact and appropriate (numeric for money, timestamptz for events)? Are NOT NULL, UNIQUE, CHECK and foreign keys in place? Are NULL comparisons handled correctly? Do queries select only needed columns with explicit ORDER BY? Are indexes matched to real queries, checked with EXPLAIN (ANALYZE)? Are read-modify-write paths protected against lost updates? Are transactions short with retries where needed? Does the application use a least-privilege role (and RLS for tenants)? Are backups tested and migrations low-lock?

The checklist

Use it in schema and query reviews.

[ ] numeric for money; timestamptz for events; bigint ids
[ ] NOT NULL / UNIQUE / CHECK / FOREIGN KEY constraints named
[ ] NULL-aware comparisons (IS NULL, IS DISTINCT FROM, coalesce)
[ ] explicit columns + ORDER BY; keyset pagination for big lists
[ ] indexes for real queries; EXPLAIN (ANALYZE, BUFFERS) checked
[ ] atomic updates or FOR UPDATE for read-modify-write
[ ] short transactions; retries for serialization failures
[ ] least-privilege roles; RLS for multi-tenant data
[ ] backups automated and restore-tested
[ ] migrations: CONCURRENTLY, NOT VALID, lock_timeout, expand/contract

Let the database enforce it

Every rule you encode as a constraint is one less class of bug to hunt in application code.

Quick check: Which item belongs on a PostgreSQL review checklist?

• Grant superuser to the application
• Use float8 for money
• Indexes are checked against real queries with EXPLAIN
• Skip foreign keys for speed by default