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

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.