ia-postgresql

PostgreSQL

Data Type Defaults

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.

Schema Rules

• Every FK column gets an index (PG does NOT auto-create these)
• NOT NULL on every column unless NULL has business meaning
• CHECK constraints for domain rules at DB level
• EXCLUDE constraints for range overlaps: EXCLUDE USING gist (room WITH =, during WITH &&)
• Default created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• Separate updated_at with trigger, never trust app layer alone. Gate it with WHEN (OLD.* IS DISTINCT FROM NEW.*) so a no-op write neither fires the function nor bumps the timestamp -- on BEFORE UPDATE the row image is built before the trigger runs, so the comparison sees the caller's row, not the one the trigger is about to stamp.
• Use BIGINT PKs -- cheaper JOINs than UUID, better index locality
• Safe migrations: CREATE INDEX CONCURRENTLY, add columns with a non-volatile DEFAULT (instant add). Never ALTER TYPE on large tables in-place.
• A DEFAULT whose expression is VOLATILE rewrites the entire table under ACCESS EXCLUSIVE; only IMMUTABLE/STABLE defaults get the metadata-only fast path. Check before shipping the migration: SELECT provolatile FROM pg_proc WHERE proname = 'gen_random_uuid'; -- v is volatile, s/i are not. So DEFAULT 7 and DEFAULT now() are instant, DEFAULT gen_random_uuid() is a full rewrite; add the column nullable, backfill in batches, then set the default.
• NULLS NOT DISTINCT on unique indexes (PG15+) -- treats NULLs as equal for uniqueness
• A UNIQUE constraint proves at most one row per key, never exactly one. The lower bound has to come from elsewhere -- a NOT NULL FK from the covered side, a CHECK, or a seeding invariant -- so any docblock, MR description, or review conclusion of the form "the index is unique, therefore every X has exactly one Y" is unsound until that other source is named. The tell is the word exactly, or a downstream promise phrased as a universal. One query settles it: enumerate the domain and count the members with zero rows.
• Under NULLS NOT DISTINCT, a pre-flight duplicate check written with SQL = misses NULL/NULL collisions -- the index rejects the second row, but NULL = NULL evaluates to NULL (not true), so a self-join or WHERE a.col = b.col probe silently skips exactly the pairs the index will reject. Write the probe with IS NOT DISTINCT FROM so NULL/NULL compares as equal.
• ORDER BY col DESC puts NULLs FIRST (ASC puts them last), so a "keep the newest row" dedup written ORDER BY updated_at DESC picks the row whose timestamp is NULL. ORM timestamps() helpers typically create created_at/updated_at as nullable, so the exposure is routine rather than exotic. Pin the order (ORDER BY updated_at DESC NULLS LAST, id DESC) or make the column NOT NULL. MySQL's DESC default is the opposite (NULLS LAST), so a query ported between the two silently changes which row survives.
• Revoke default public schema access: REVOKE ALL ON SCHEMA public FROM public
• Derive every attribute at its own grain. A property of the parent -- a session, a day, an order -- computed from one child row lands on every child and is legitimately partial for most of them, so the aggregate disagrees with itself depending on which child is read. Put parent-scoped facts in a table keyed at the parent grain, populate them from the child the parent designates, and prefer a boundary observation (the last event's timestamp) over a count threshold.