postgres-strict

PostgreSQL Strict Standard

Rules for production Postgres. Targets 16+ with notes for 17/18 features.

Version Targets

PostgreSQL 18 is GA (released Sept 2025). New deployments should target 18. Highlights worth designing around:

• Async I/O subsystem (AIO): sequential scans, bitmap heap scans, and VACUUM issue concurrent reads instead of blocking each one. Up to ~3x faster on bulk-read workloads. No code change required.
• Skip scan on B-tree indexes: multicolumn indexes can now be used when the leading column is not in the predicate, reducing the need for redundant index variants.
• Parallel GIN index builds: CREATE INDEX ... USING gin runs in parallel. Big win for JSONB and full-text builds on large tables.
• uuidv7() native: no extension required (see PG-26 below).
• Virtual generated columns are the default: stored columns require explicit STORED. Virtual columns compute on read, no write amplification.
• OLD and NEW in RETURNING: capture the row state before and after UPDATE/DELETE/MERGE in one statement.
• OAuth 2.0 authentication in libpq: easier SSO integration.
• Planner stats survive major version upgrades: post-upgrade clusters reach steady-state performance much faster, no immediate ANALYZE storm.

CRITICAL: Migration Safety

A migration is a contract with a running system. The wrong migration takes prod down.

PG-01: CREATE INDEX CONCURRENTLY on hot tables

-- BAD: takes ACCESS EXCLUSIVE lock for the duration of the build
CREATE INDEX events_user_id_idx ON events (user_id);

-- GOOD: short ACCESS EXCLUSIVE on metadata, no row lock during build
CREATE INDEX CONCURRENTLY events_user_id_idx ON events (user_id);

CONCURRENTLY cannot run inside a transaction block. Migration tools that wrap statements in BEGIN/COMMIT must opt out for these statements. Failed concurrent index builds leave an INVALID index, drop and retry.

PG-02: Add NOT NULL columns in two steps

-- BAD: rewrites the whole table while holding ACCESS EXCLUSIVE
ALTER TABLE users ADD COLUMN status text NOT NULL DEFAULT 'active';

-- GOOD on PG 11+ for defaults, but still problematic if you need NOT NULL on existing nulls
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';
-- Backfill in batches in app code
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN $1 AND $2;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;