PostgreSQL
Working rules
- Preserve raw bytes as bytes when fidelity matters; choose parsed types separately for querying.
- Treat deployed migrations as immutable and account for old and new application versions during rollout.
- Data rollback needs provenance. Selecting rows by the destination value can also select rows the forward migration never touched. When reversibility is required, preserve affected identities and original values under an explicit policy for subsequent writes. Verify restored data with the application version that will read it.
- Check lock duration and transaction scope; protect read-modify-write paths against concurrent updates.
- Match index predicates and NULL semantics to every writer and migration query.
- Measure query changes with representative data and actual plans; verify invariants as database outcomes.
Data Type Defaults
| Need | Use | Avoid |
|---|---|---|
| Primary key | BIGINT GENERATED ALWAYS AS IDENTITY |
SERIAL, BIGSERIAL |
| Timestamps | TIMESTAMPTZ |
TIMESTAMP (loses timezone) |
| Text | TEXT |
VARCHAR(n) unless constraint needed |
| Money | NUMERIC(precision, scale) |
MONEY, FLOAT |
| Boolean | BOOLEAN with NOT NULL DEFAULT |
nullable booleans |
| JSON | JSONB |
JSON (no indexing), text JSON |
| UUID | gen_random_uuid() (PG13+) |
uuid-ossp extension |
| IP addresses | INET / CIDR |
text |
| Ranges | TSTZRANGE, INT4RANGE, etc. |
pair of columns |
| Raw bytes (verbatim payload) | BYTEA |
JSONB, TEXT (both re-encode) |
A spec that says "log the raw response" is asking for byte fidelity, and no text type provides it. JSONB reparses: it drops insignificant whitespace, sorts object keys, keeps only the last of duplicate keys, and rewrites numbers out of exponent notation (1e0 -> 1; trailing zeros in 1.00 do survive, so "all numeric forms collapse" overstates it). A non-JSON body cannot be stored at all and usually lands as NULL. TEXT rejects a NUL byte and any sequence invalid in the database encoding, so a binary or mis-encoded body errors instead of storing. Persist the bytes in BYTEA with the content type beside them, and add a parsed JSONB column separately when queries need one. Reading the column type as proof the body is kept is the review error.
Verify
Start changed-query inspection with EXPLAIN. Run EXPLAIN (ANALYZE, BUFFERS) with representative data only when executing the query and its side effects is authorized and safe. For writes, prefer disposable data; use a transaction followed by rollback only when all relevant effects are transactional. Investigate unexpected sequential scans and compare actual costs; accept a sequential scan when reading much of a table is cheaper than using an index. Confirm no unindexed FK columns before declaring done.
Task-specific references
Read the relevant reference before implementing or reviewing the matching behavior:
- For table design, constraints, schema changes, or backfills: schema-and-migrations.md.
- For indexes, query plans, JSONB, pagination, or query anti-patterns: query-and-index-patterns.md.
- For RLS, transaction boundaries, locks, partitioning, pooling, or operational features: security-and-operations.md.
Existing specialized references, when the corresponding topic applies: