LLM Best Practices

Postgres Best Practices

4 min read

View markdown source

Overview

Use Postgres 17 as the default relational store for app data. It gives you transactional integrity, rich indexes, JSONB for variable-shape data, and an operational story that scales from a laptop to a cluster. This page covers schema, indexes, migrations, JSONB, pooling, and the operational defaults.

Normalize first, denormalize on evidence

Keep one source of truth per fact. Every column has a clear owner: a users.email lives on users, not copied onto orders.

  • Start with 3NF. Foreign keys with ON DELETE clauses, not application-layer cascades.
  • Denormalize only when you have a measured read pattern that needs it (a leaderboard, a hot dashboard). Document the denormalization and the job that keeps it in sync.
  • Use CHECK constraints, NOT NULL, and domain types. The database is the last line of defense; do not push validation entirely into fastapi or prisma.

Pick the right index for the access pattern

btree is the default and handles equality, range, and ordering on scalar columns. Reach for other types when the query shape calls for it.

  • btree: equality and range on scalars (WHERE created_at > ?, ORDER BY id).
  • GIN: JSONB containment (@>), full-text search (tsvector), array overlap.
  • GIST: ranges, geometries, and tsrange exclusion constraints.
  • BRIN: append-only large tables ordered by time. Cheap to maintain, narrow win.

Add partial indexes for skewed predicates (WHERE deleted_at IS NULL). Add expression indexes for case-insensitive lookups (lower(email)). Drop indexes you do not use; every write pays for them.

Write idempotent migrations and never edit a shipped one

Migrations are append-only. Once a migration is in main, treat it as immutable.

  • One forward script per change. CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, guarded by version checks.
  • Never edit a migration that has run in any environment. Write a new migration that fixes the prior one.
  • Use CREATE INDEX CONCURRENTLY for hot tables. Run heavy backfills in batched jobs, not inside the migration transaction.
  • See prisma for the Prisma migration workflow that wraps this contract.

Use JSONB for variable-shape data, not as a key-value dump

JSONB is for fields whose shape genuinely varies per row: third-party webhook payloads, user-defined form responses, model output. It is not a shortcut around schema design.

-- Good: variable-shape audit payload
ALTER TABLE events ADD COLUMN payload JSONB NOT NULL;
CREATE INDEX events_payload_gin ON events USING GIN (payload jsonb_path_ops);
 
-- Bad: column-per-feature replaced by a JSON bag
-- user.preferences JSONB containing { theme, locale, timezone, ... }

Promote fields to real columns the moment you query them in WHERE clauses, sort on them, or constrain them. JSONB queries cost more than column queries and are harder to plan.

Pool connections with PgBouncer in transaction mode

Postgres uses one process per connection. Application pools that open hundreds of connections starve the server. Put PgBouncer in front for any service with more than a handful of workers.

  • Transaction pooling for most web workloads. Set default_pool_size per database to your CPU count times two as a starting point.
  • Session pooling only when the client needs session features (LISTEN/NOTIFY, prepared statements without pgbouncer=true).
  • See prisma for the pgbouncer=true flag and Prisma Accelerate notes.

Run EXPLAIN ANALYZE before optimizing

Do not guess at query plans. Read them.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE user_id = $1 AND created_at > now() - interval '30 days'
ORDER BY created_at DESC LIMIT 50;

Look for sequential scans on large tables, mis-estimated row counts, and sort spills. Fix the worst plan first.

Tune autovacuum for hot tables

Default autovacuum is fine for small workloads. High-churn tables need per-table settings.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

Monitor pg_stat_user_tables for n_dead_tup and last_autovacuum. Run VACUUM ANALYZE after large backfills; do not wait for autovacuum to catch up.

Graph View

Backlinks