LLM Best Practices

Postgres in Production

Last updated 4 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

Run Postgres on a managed provider until you have a concrete reason not to: the provider handles backups, patching, replication, and paging, and you own schema and queries. After the rules in postgres, restore-test backups, add WAL archiving when the RPO is minutes, drill failover, pool connections, and use replicas for read scale, not safety.

Default to a managed provider

Neon, Supabase, AWS RDS or Aurora, and Google Cloud SQL all ship automated backups, minor-version patching, monitoring, and read replicas.

  • Neon: branchable Postgres with separated storage and compute; good for preview environments.
  • Supabase: Postgres plus auth, storage, and edge functions, when the app needs more than a database (supabase-backups).
  • RDS or Cloud SQL: boring and durable, costly at the high end; pick it when you already live in AWS or GCP.

Self-host only when the managed bill is provably worse at scale, residency rules pin you to a region no provider serves, or you need an extension the provider blocks. Baseline: hostinger-vps.

Restore a backup before you trust it

Run a full restore drill quarterly and after any backup configuration change; record the wall-clock time to first query.

# Nightly logical dump: directory format allows parallel jobs
pg_dump --jobs=4 --format=directory --file=/backups/app-$(date +%F) app_prod
# Drill: restore into a scratch instance
pg_restore --jobs=4 --dbname=app_restore /backups/app-2026-10-01

Push dumps off the box with restic (hostinger-vps-backups) or the provider’s snapshots. Retention is your call; a common starting point is 7 daily, 4 weekly, and 12 monthly.

Add WAL archiving when the RPO is minutes

A daily dump means up to a day of loss. Continuous WAL archiving gives point-in-time recovery.

  • Managed: PITR is built in, but windows differ by plan. Neon keeps 6 hours on Free, up to 7 days on Launch and 30 days on Scale. RDS automated backups retain 0 to 35 days. Confirm the window and the latest restorable time on your plan.
  • Self-hosted: set archive_command to ship WAL to object storage; pgBackRest or wal-g add retention, encryption, and parallel restore.

RPO is roughly the WAL archive interval; RTO is dominated by replay since the last base backup. Size both against your incident budget (disaster-recovery).

Time the failover before the incident

Run the drill quarterly. Managed: trigger the provider’s failover and time endpoint propagation, pool reconvergence, and app reconnect. Self-hosted: promote a replica with pg_ctl promote or Patroni and update the connection string at the pool. Write down the steps, the expected duration, and the rollback path beside the runbook.

Pool connections instead of multiplying them

Postgres runs one process per connection, so a hundred workers holding ten connections each starve the server. Put PgBouncer in transaction mode in front of any service with more than a few workers; Supabase and Neon ship their own poolers. Pooling rules: postgres; Prisma flags: prisma; rollouts: migrations.

Use replicas for read scale, not safety

A replica lagging ten seconds is not a backup. Use replicas for reporting, search indexing, and a warm failover candidate. Back up from the primary or a dedicated backup node, never from a lagging replica. Trade-offs: postgres-replication.

Keep the database URL out of plain env files

Never commit the production URL. Use a secret manager (AWS Secrets Manager, GCP Secret Manager, Doppler, 1Password) and rotate on a schedule (secrets-and-env). Prefer short-lived IAM credentials where supported (RDS IAM auth, Cloud SQL IAM). fastapi and similar clients read the URL from the store at startup.

Wire three signals before you need them

  • pg_stat_statements (in shared_preload_libraries): review top time consumers weekly, then use postgres-explain and postgres-indexes.
  • Slow query log: for example log_min_duration_statement = 250ms, shipped to the app’s log pipeline.
  • Autovacuum: alert on dead-tuple ratios and last_autovacuum age for hot tables (postgres-vacuum).

Add host metrics (hostinger-vps), app traces and SLOs (observability), disk above 80 percent, and replication lag above the RPO target.

Backlinks