designing-postgres-schemas

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

CREATE TABLE tasks (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   uuid NOT NULL,
    status      text NOT NULL CHECK (status IN ('pending', 'running', 'done')),
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

Give every table a stable key unless a documented reason forbids it. Use GENERATED BY DEFAULT only when callers must supply identity values.

Design from operations

For each table, write down:

• uniqueness and validity rules to enforce;
• hot filters, joins, ordering, and update paths;
• row count, write rate, and retention;
• deletion behavior and the maximum cascade fan-out;
• which fields change often and will create dead tuples;
• tenant or ownership boundaries.