LLM Best Practices

Postgres Best Practices

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Use PostgreSQL 18 as the default relational store for app data. 18 shipped on 2025-09-25 and 18.6 is the current minor release; 19 is in beta with general availability planned for October 2026; 14 reaches end of life on 2026-11-12. This page holds the cross-cutting rules, and each topic below has its own page. For the head-to-head with MySQL, see postgres-vs-mysql.

Use the Postgres 18 features that change decisions

Check SHOW server_version before relying on any of these.

Change in 18Rule
uuidv7()Native time-ordered UUID. Use it for UUID primary keys.
B-tree skip scanA multicolumn index can serve a query with no equality on its leading column. See postgres-indexes.
Async I/Oio_method defaults to worker; io_uring needs a build with liburing. It speeds sequential scans, bitmap heap scans, and vacuum.
Virtual generated columnsGENERATED ALWAYS AS (expr) is virtual by default. A virtual column cannot be indexed or logically replicated; write STORED when you need either.
EXPLAIN ANALYZEReports BUFFERS without being asked. See postgres-explain.
pg_upgradeKeeps planner statistics, but not extended statistics. Afterwards run vacuumdb --all --analyze-in-stages --missing-stats-only, then vacuumdb --all --analyze-only.

Choose primary keys that insert in order

Use bigint GENERATED ALWAYS AS IDENTITY when one database assigns IDs. Use uuidv7() when clients or services generate IDs, or when IDs leave the system. Avoid random UUIDv4 (gen_random_uuid()) as a primary key: inserts land across the whole B-tree, causing page splits and poor cache locality. Before 18, generate UUIDv7 in the application.

Keep one source of truth per fact

users.email lives on users, not copied onto orders.

  • Start with 3NF. Use foreign keys with explicit ON DELETE, not application-layer cascades.
  • Denormalize only for a measured read pattern, and document the job that keeps the copy in sync.
  • Use CHECK, NOT NULL, and domain types. Do not push all validation into fastapi or prisma.
  • Take locks in a consistent order across transactions; see deadlock.

Pool connections in transaction mode

Postgres runs one process per connection, so hundreds of application connections starve the server. Put PgBouncer in front of any service with more than a handful of workers.

  • Use transaction pooling for web workloads. Use session pooling only when clients need session state: LISTEN/NOTIFY, session-level advisory locks, or SET without LOCAL.
  • PgBouncer 1.21 and later supports protocol-level prepared statements in transaction mode when max_prepared_statements is above zero.
  • See prisma-pooling for the Prisma settings and supabase for Supavisor.

Send each topic to its page

Backlinks