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

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.

Foreign keys do not automatically index the referencing side. Add that index for parent changes or child lookups. Treat large cascades as writes with lock, WAL, and vacuum costs.

Never add ON DELETE CASCADE to a high-volume relationship without bounding its fan-out and accepting its lock, WAL, latency, and vacuum impact. Prefer an explicit, observable deletion workflow when one parent can own many rows.

Decide JSONB from query patterns

Ask what must be filtered, joined, sorted, validated, or made unique before choosing storage.

• Allow jsonb as a deliberate escape hatch for genuinely unsettled or variable attributes.
• Promote every stable field used for filtering, joining, sorting, uniqueness, or relational integrity to a typed column.
• State the tradeoff: fields kept only in JSONB forfeit ordinary column NOT NULL/CHECK constraints, foreign keys, and cheap per-column planner statistics.
• JSONB can be indexed. Use GIN for containment/key queries, or a B-tree expression index on an extracted value such as (metadata->>'external_id') for a specific access path. An index does not restore relational constraints or ordinary column statistics.

For unbounded event or log tables, design retention and partition early when dropping time ranges will be routine. See postgres-advanced-patterns for maintenance, writing-performant-queries for indexes, and writing-safe-migrations for live changes.

Common Mistakes

• Using timestamp for an instant because all current users share one time zone.
• Omitting a primary key from a table that will later need updates, deduplication, or queue claims.
• Adding a foreign key without considering the referencing-side index and deletion fan-out.
• Storing stable relational fields only inside JSONB.
• Allowing an event table to grow without a retention or partition-maintenance plan.