/ia-postgresql

name: ia-postgresql
class: language
description: >-
  PostgreSQL schema design, query optimization, indexing, and administration.
  Use when working with PostgreSQL, JSONB, partitioning, RLS, CTEs, window
  functions, or EXPLAIN ANALYZE.

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](./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).