Designing postgres schemas

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

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(),               -- timestamptz, not timestamp
    updated_at   timestamptz NOT NULL DEFAULT now()
);

Design Approach

Build iteratively. Start with a rough table, then write the queries the app needs and let them drive the columns and indexes. For each table ask:

• Is this high-read or high-write?
• What are the common filter columns? (those want indexes)
• Which columns update most often? (hot updates → dead tuples → see tuning-autovacuum-and-bloat)

Normalization (1NF/2NF/3NF) reduces duplication but can fight query efficiency — denormalize deliberately when reads demand it, not by accident.

Common Mistakes

• timestamp instead of timestamptz — silent timezone bugs later.
• No primary key — breaks joins, replication, and FOR UPDATE SKIP LOCKED patterns.
• Cascading deletes on a high-volume table — one delete can lock and rewrite huge child sets.
• jsonb everything — you forfeit constraints and cheap indexed lookups; use real columns for anything you filter or join on.