Designing Postgres Schemas

---
name: designing-postgres-schemas
description: Guides Postgres schema design when creating tables, choosing keys and data types, defining foreign keys and deletion behavior, modeling JSONB, or planning retention for unbounded event and log data.
---

# 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 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.