postgresql

# 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](./references/schema-and-migrations.md). - For indexes, query plans, JSONB, pagination, or query anti-patterns: [query-and-index-patterns.md](./references/query-and-index-patterns.md). - For RLS, transaction boundaries, locks, partitioning, pooling, or operational features: [security-and-operations.md](./references/security-and-operations.md). Existing specialized references, when the corresponding topic applies: - [operations.md](./references/operations.md). - [concurrency-patterns.md](./references/concurrency-patterns.md). - [full-text-search.md](./references/full-text-search.md). - [performance-patterns.md](./references/performance-patterns.md).