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.
- 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
Run EXPLAIN (ANALYZE, BUFFERS) on changed queries with representative data. 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:
1---2name: ia-postgresql3description: PostgreSQL schema design, query optimization, indexing, and administration. Use when working with PostgreSQL, JSONB, partitioning, RLS, CTEs, window functions, or EXPLAIN ANALYZE.4---56# PostgreSQL78## Working rules910- Preserve raw bytes as bytes when fidelity matters; choose parsed types separately for querying.11- Treat deployed migrations as immutable and account for old and new application versions during rollout.12- Check lock duration and transaction scope; protect read-modify-write paths against concurrent updates.13- Match index predicates and NULL semantics to every writer and migration query.14- Measure query changes with representative data and actual plans; verify invariants as database outcomes.1516## Data Type Defaults1718| Need | Use | Avoid |19|------|-----|-------|20| Primary key | `BIGINT GENERATED ALWAYS AS IDENTITY` | `SERIAL`, `BIGSERIAL` |21| Timestamps | `TIMESTAMPTZ` | `TIMESTAMP` (loses timezone) |22| Text | `TEXT` | `VARCHAR(n)` unless constraint needed |23| Money | `NUMERIC(precision, scale)` | `MONEY`, `FLOAT` |24| Boolean | `BOOLEAN` with `NOT NULL DEFAULT` | nullable booleans |25| JSON | `JSONB` | `JSON` (no indexing), text JSON |26| UUID | `gen_random_uuid()` (PG13+) | `uuid-ossp` extension |27| IP addresses | `INET` / `CIDR` | text |28| Ranges | `TSTZRANGE`, `INT4RANGE`, etc. | pair of columns |29| Raw bytes (verbatim payload) | `BYTEA` | `JSONB`, `TEXT` -- both re-encode |3031A 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.323334## Verify3536Run `EXPLAIN (ANALYZE, BUFFERS)` on changed queries with representative data. 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.3738## Task-specific references3940Read the relevant reference before implementing or reviewing the matching behavior:4142- For table design, constraints, schema changes, or backfills: [schema-and-migrations.md](./references/schema-and-migrations.md).43- For indexes, query plans, JSONB, pagination, or query anti-patterns: [query-and-index-patterns.md](./references/query-and-index-patterns.md).44- For RLS, transaction boundaries, locks, partitioning, pooling, or operational features: [security-and-operations.md](./references/security-and-operations.md).4546Existing specialized references, when the corresponding topic applies:4748- [operations.md](./references/operations.md).49- [concurrency-patterns.md](./references/concurrency-patterns.md).50- [full-text-search.md](./references/full-text-search.md).51- [performance-patterns.md](./references/performance-patterns.md).
iliaal