ia-postgresql

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.
• 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:

• operations.md.
• concurrency-patterns.md.
• full-text-search.md.
• performance-patterns.md.