LLM Best Practices

Postgres Best Practices

4 min read

View markdown source

Overview

Use Postgres 18 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. Postgres 18 (released 2025-09-25) adds an async I/O subsystem, B-tree skip scan, native uuidv7(), and virtual generated columns. This page covers schema, indexes, migrations, JSONB, pooling, and the operational defaults. For the head-to-head against MySQL, see postgres-vs-mysql.

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.
  • In concurrent workloads, design transactions to acquire locks in a consistent order. Postgres can identify cycles; see deadlock-detection for how it detects and resolves them.
  • For surrogate keys, prefer bigint identity columns when writes are single-primary, or Postgres 18’s native uuidv7() (time-ordered UUIDs) when you distribute writes. Avoid random uuid() v4 as a primary key; it scatters inserts across the B-tree and bloats the index.

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. For the full per-type rundown, see postgres-indexes.

  • 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. See postgres-jsonb for the deeper indexing and promote-to-column rules.

-- 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. See postgres-explain for the full plan-reading playbook, postgres-explain for a one-page node-type reference, and debug-postgres-slow-query for the end-to-end debug loop. When sorting or ranking within partitions, postgres-window-functions covers the window function syntax.

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. See postgres-vacuum for the full bloat-control story.

Graph View

Backlinks