Postgres Basics That Prevent Production Pain

PostgreSQL will run your SaaS for years if you respect a few basics early. Most production pain is not mysterious—missing indexes on foreign keys, migrations that lock huge tables, nullable columns that should be constrained, and queries that scan millions of rows because WHERE org_id = ? was never indexed.

This post targets application developers who own SQL but are not DBAs. It links operational habits to background jobs that touch the database, auth sessions stored in tables or Redis, and API design that should not force full-table scans for list screens.

Primary keys and foreign keys

Use bigint or uuid primary keys consistently; document generation strategy (gen_random_uuid() versus app-side ids). Every foreign key column needs an index on the referencing side unless you enjoy sequential scans on joins. Postgres does not auto-index FK columns the way some ORMs imply.

Name constraints (fk_orders_customer_id) so error messages in logs are readable. ON DELETE behavior should be explicit—CASCADE for join tables, RESTRICT or SET NULL when business rules demand it.

Constraints beat application-only validation

NOT NULL, CHECK, and UNIQUE constraints catch bugs when a deploy ships without the matching Zod schema update. Prefer database uniqueness for natural keys (org_id, slug) even if the app also checks—race conditions happen under load.

Use timestamptz for instants; store UTC. The PostgreSQL documentation on data types is the reference when debating text versus varchar—in modern Postgres, text is fine for variable strings.

Indexes you will actually need

Index columns that appear in WHERE, JOIN, and ORDER BY for hot queries—especially org_id on tenant-scoped tables. Composite indexes match left-to-prefix rules: (org_id, created_at DESC) supports filtering by org and sorting recent rows.

Avoid indexing every column “just in case.” Write-heavy tables pay insert cost per index. Use EXPLAIN (ANALYZE, BUFFERS) on slow queries in staging with realistic data volumes—not empty dev databases; the PostgreSQL EXPLAIN guide explains how to read plans without drowning in jargon.

Partial indexes help soft deletes: CREATE INDEX ... ON users (org_id) WHERE deleted_at IS NULL when most queries ignore archived rows.

Migrations without drama

Ship migrations in small steps compatible with rolling deploys:

1. Add nullable column.
2. Backfill in batches via job.
3. Set NOT NULL with default or check constraint after backfill.
4. Drop old column in a later release.

Long ACCESS EXCLUSIVE locks from reckless ALTER break deploys. For large tables, use concurrent index creation: CREATE INDEX CONCURRENTLY as documented in the PostgreSQL CREATE INDEX section.

Never edit applied migration history in shared environments; forward-fix with new migrations.

Connection pools and timeouts

App servers should use a pool sized to Postgres max_connections reality—often PgBouncer in transaction mode for serverless or high fan-out. Set statement timeouts for interactive requests so one bad query does not wedge the pool.

Long-running analytics belong on replicas or warehouse exports, not the primary OLTP instance your API uses.

Transactions and isolation

Keep transactions short. Load data, decide, commit—do not hold transactions open while calling external HTTP APIs. Use SELECT ... FOR UPDATE when two workers might claim the same row (job queues, inventory).

Understand your default isolation level; READ COMMITTED is Postgres default and suits many SaaS apps. Serializable isolation solves rare anomalies at a performance cost—reach for it deliberately, not by default.

N+1 at the database layer

ORM lazy loading causes query storms. Prefer explicit joins or batched WHERE id IN (...) for list endpoints. If the API returns nested collections, consider JSON aggregation in SQL for read-heavy paths—measure before micro-optimizing.

Align list pagination with indexed sort keys; offset pagination deep in large tables degrades even with indexes—cursor pagination by (created_at, id) is often cheaper.

Vacuum, bloat, and monitoring

Postgres needs autovacuum; monitor table bloat and dead tuples on high-churn tables. Disk growth from WAL and unreclaimed space is an ops ticket waiting to happen. Cloud managed Postgres exposes metrics—wire alerts on replication lag, connection count, and storage.

Secrets and least privilege

Application roles should not own superuser. Separate migration role from runtime role if your platform allows. Rotate credentials and avoid checking connection strings into repos—pair with environment practices from your broader platform work.

Local dev parity

Use the same major Postgres version locally and in production. Seed scripts with realistic cardinalities expose missing indexes early. Run migration CI against ephemeral databases on every PR.

Row-level security (optional advanced)

Postgres row-level security policies can enforce tenant isolation at the database layer as defense in depth. They help when multiple services query the same tables, but they complicate migrations and superuser bypass paths. Many SaaS teams rely on application-level org_id filters with integration tests instead—either approach works if you never ship a query without tenant scope.

When to reach for extensions

pgcrypto, citext, and uuid-ossp solve specific problems—enable only what you need. Read extension docs before baking them into core schema; managed providers differ on supported extensions.

JSON columns: use with intent

jsonb is flexible for metadata and feature flags; index with GIN when you query inside JSON often. Do not store core relational data only in JSON because migrations feel hard—queryability and constraints suffer. When you do use JSON, validate shape at write time in application code and optionally with CHECK (jsonb_typeof(...)) for invariants that must never break.

Read replicas and reporting

Route heavy read-only reporting to replicas with lag awareness. UI copy or timestamps should reflect replication delay if users expect near-real-time totals. ETL to a warehouse is the long-term path for complex analytics; replicas are a bridge, not a BI platform.

Backup and restore drills

Managed Postgres offers automated backups; still run restore drills into an isolated environment quarterly. Verify point-in-time recovery meets your RPO promises to customers. Application-level exports complement but do not replace database backups for operational recovery.

Lock contention smells

Rising wait_event metrics on row locks often trace to long transactions touching hot rows—session counters, global config, or job lease tables. Split hot counters into append-only event tables aggregated asynchronously if needed. Design job claiming with FOR UPDATE SKIP LOCKED patterns for queue-like workloads.

Statistics and planner surprises

Stale statistics skew the query planner toward bad plans after large data shifts. ANALYZE after big imports; consider autovacuum tuning on append-heavy tables. When a query suddenly slows without code changes, compare EXPLAIN before and after row count growth—often an index was always needed but the table was small enough to hide the problem.

ORM escape hatches

ORMs help productivity until they hide N+1 or emit SELECT * on wide tables. Code review should flag raw SQL and include graphs on hot paths. Avoiding any at boundaries applies to query result typing when using lightweight SQL builders.

Closing

Postgres basics that prevent pain are mostly discipline: index tenant and join columns, constrain data in the database, migrate in expandable steps, pool connections, and measure queries with EXPLAIN. Your future on-call self will thank you when list endpoints stay fast and deploys do not freeze the orders table at peak traffic. Small habits compound. Treat every missing org_id filter in a pull request as a security defect, not a style nit—that single habit prevents more incidents than exotic tuning. Index reviews belong in the same ceremony as API reviews when list endpoints ship.