designing-postgres-schemaslisted

# Designing Postgres Schemas ## Overview Design from invariants and real access paths. Put correctness in types, constraints, and keys; add indexes for actual reads and writes. Make growth and retention explicit before deployment. ## Quick Reference | Decision | Starting point | Check before committing | |---|---|---| | Primary key | `bigint` identity or `uuid` | Generation location, exposure, index locality | | Time instant | `timestamptz` | Display zone belongs at the application boundary | | Local civil time | `timestamp` plus explicit zone/rules | Use only when the value is intentionally not an instant | | Relationship | Foreign key | Index the referencing columns used for joins/deletes | | Flexible attributes | `jsonb` | Promote constrained, filtered, or joined fields to columns | | Unbounded events/logs | Time-based retention plan | Consider partitioning before the table becomes large | **Use `timestamptz` for an instant.** It normalizes an instant and displays it in the session time zone; it does **not** retain the input's zone name or original offset. If the originating IANA zone matters to product behavior, store it separately (for example, `origin_tz text`). Use `timestamp` only for an intentional wall-clock value; otherwise differently configured clients can silently interpret the same zone-less value as different instants. ## Baseline pattern ```sql CREATE TABLE tasks ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, tenant_id uuid NO