Recipe

Postgres design patterns

Battle-tested schema and query patterns for production Postgres workloads. This recipe covers the three patterns that matter most when your dataset crosses 10M rows: partitioning, denormalized read paths, and idempotent writes. Every choice below trades a small amount of complexity for a large amount of headroom.

1. Range-partition hot tables by time

Append-heavy tables (events, audit logs, ledger entries) should be range-partitioned by month or week the day you create them. Backfilling partitioning later requires a full rewrite. With native declarative partitions, autovacuum and index maintenance scale per-partition instead of per-table.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_06
  PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

2. Separate write models from read models

Normalize your write path for correctness, then derive a denormalized read table via triggers or a logical-replication consumer. The read table holds exactly the columns your hottest query needs, indexed exactly the way that query reads them. Latency drops by an order of magnitude and you stop fighting query planner regressions on every minor schema change.

3. Make every write idempotent

Retries are the default in any distributed system. Use a client-suppliedrequest_idcolumn with a unique index, and write viaINSERT ... ON CONFLICT DO NOTHING. This lets you retry safely from queue workers, webhooks, and clients without building a coordination layer. The cost is a single uuid column per row.