designing-postgres-schemaslisted

# Designing Postgres Schemas ## Overview The schema is the hardest thing to change after deployment. Get primary keys, timestamp types, and the read/write shape right up front — design iteratively from the queries your app actually runs, not from an abstract entity diagram. ## When to Use - Creating a new table or adding columns. - Choosing a primary key strategy. - Deciding a timestamp / id column type. - Considering foreign keys with cascading deletes. - Tempted to store a blob of fields in `jsonb`. Not for: query tuning (see `writing-performant-queries`) or changing an existing live schema (see `writing-safe-migrations`). ## Quick Reference | Decision | Do this | Why | |---|---|---| | Primary key | Identity column (auto-int) or built-in `uuid` | Both index well; always give every table a PK | | Timestamps | `timestamptz` — never `timestamp` | Stores UTC + offset; avoids timezone corruption | | Foreign keys | FK + `ON DELETE CASCADE` at low volume | Data correctness; **use caution at high write volume** | | Fast-moving fields | `jsonb` escape hatch when moving fast | Flexible, but you lose constraints/indexing guarantees | ## Pattern ```sql CREATE TABLE tasks ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- always have a PK tenant_id uuid NOT NULL, status text NOT NULL, payload jsonb, -- escape hatch; flag the tradeoff created_at timestamptz NOT NULL DEFAULT now(),