Postgres tuning I wish I'd known at 15

I've run PostgreSQL in production for three years. Here are the things I got wrong early, and what I'd tell my past self.

EXPLAIN ANALYZE is not optional

Every slow query I've ever debugged came down to one of three things: a missing index, a bad row estimate, or a sequential scan on a table I thought was indexed.

EXPLAIN ANALYZE shows you the actual execution plan. Not the estimated plan — the actual one, with real row counts and real timing. Learn to read it. Specifically, look for rows-estimated vs rows-actual discrepancies. A large mismatch usually means stale statistics; run ANALYZE on the table.

Connection pooling matters more than you think

Postgres opens a process per connection. At 200 connections, you're spending meaningful CPU on process overhead. Use PgBouncer in transaction mode for most workloads. The configuration is simple; the payoff is significant.

Vacuum is your friend, not your enemy

Autovacuum gets a bad reputation for "randomly" consuming resources. It's not random — it triggers based on dead tuple accumulation. If autovacuum is causing visible performance problems, the fix is usually to tune it more aggressively, not disable it.

-- Per-table autovacuum tuning for high-write tables
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

Indexes are not free

Every index costs write performance and storage. I used to add indexes speculatively ("this column might be queried"). Now I add them only when EXPLAIN ANALYZE shows a sequential scan on a hot path.

The corollary: partial indexes are underused. If 90% of your queries filter on status = 'active', index only those rows.

CREATE INDEX CONCURRENTLY idx_jobs_active
ON jobs (created_at)
WHERE status = 'active';

The thing I got most wrong

I treated the database as a black box for too long. Understanding what happens inside — MVCC, WAL, the buffer pool — changed how I design schemas and queries. It's not optional background knowledge.