PostgreSQL Optimization: From Slow Queries to Fast

Our database was slow. Really slow. Some reports took 30+ seconds to load.

Here's how we fixed it.

The Problem

We were doing full table scans on tables with 100K+ rows. Every. Single. Query.

The Solution

Indexes: Added indexes on foreign keys and frequently queried columns. Query time dropped from 30s to 2s.

EXPLAIN ANALYZE: Actually looked at query plans. Found N+1 queries everywhere.

Connection Pooling: PgBouncer reduced connection overhead significantly.

Materialized Views: Pre-computed complex reports. Refreshed nightly. Users don't need real-time data for last month's sales.

The Numbers

• Average query time: 800ms → 80ms
• P95 query time: 5s → 500ms
• Database CPU usage: 70% → 25%

The Lesson

Most performance problems are self-inflicted. Read the docs. Use the tools. Don't guess.