PostgreSQL Performance Tuning: From 2 Seconds to 20 Milliseconds

A fintech client came to us with a problem: their dashboard was taking 8+ seconds to load. Users were churning. The database had grown to 50 million rows and queries that worked fine at 10K rows were now destroying the user experience.

The Investigation

We started with pg_stat_statements to identify the top 10 slowest queries. Three patterns emerged:

• Missing indexes on commonly filtered columns
• N+1 queries from the ORM loading relations one-by-one
• Full table scans on aggregation queries for dashboard stats

Fix 1: Strategic Indexing

Not all indexes are created equal. We used EXPLAIN ANALYZE on every slow query and created targeted indexes:

-- Composite index for the most common dashboard query
CREATE INDEX CONCURRENTLY idx_transactions_user_date
ON transactions (user_id, created_at DESC)
WHERE status = 'completed';

-- Partial index for active records only
CREATE INDEX CONCURRENTLY idx_accounts_active
ON accounts (owner_id) WHERE deleted_at IS NULL;

Key insight: partial indexes and covering indexes gave us 10x better performance than naive single-column indexes.

Fix 2: Eliminating N+1 Queries

The ORM was issuing 200+ queries per dashboard load. We replaced lazy loading with explicit joins and used dataloader patterns for the remaining relation fetches. Dashboard queries dropped from 200+ to 12.

Fix 3: Materialized Views for Aggregations

Dashboard stats (monthly totals, growth rates, category breakdowns) were recalculated on every page load. We moved these to materialized views refreshed every 5 minutes via pg_cron.

Fix 4: Connection Pooling with PgBouncer

The app was opening 80+ direct connections during peak traffic, exhausting PostgreSQL's default limit. PgBouncer in transaction mode reduced active connections to 20 while handling 500+ concurrent app connections.

Results

• Dashboard load time: 8.2s → 180ms (98% improvement)
• API p95 latency: 2.1s → 22ms
• Database CPU usage: 87% → 15% at peak
• User churn stopped completely

Is your database slowing down your product? We can help you find and fix the bottlenecks.