Reducing P95 API Latency from 2.5s to 180ms with Redis & PostgreSQL Tuning

Executive Summary & Background

API response times exceeding 2,000ms directly cause user churn, abandoned checkout carts, and customer dissatisfaction.

During an emergency backend performance audit for a high-traffic SaaS client, P95 API response times were reaching 2,500ms during peak business hours. Database CPU utilization on the primary PostgreSQL instance spiked above 88%, resulting in database connection timeouts and failed HTTP requests.

This case study breaks down how we diagnosed full table scans on a 15-million row table, fixed N+1 ORM query loops, implemented composite B-Tree indexing, deployed a Redis Cache-Aside layer, and configured PgBouncer connection pooling — dropping P95 API latency by 92.8% to 180ms.

Performance Benchmark Comparison

Benchmark Metric	Initial Unoptimized Backend	Post-Optimization Backend	Performance Improvement
P95 API Latency	2,500ms (2.5 seconds)	180ms	92.8% Latency Drop
Primary DB CPU Usage	88% (Near Collapse)	18% (Idle Capacity)	70% CPU Load Reduction
Max Throughput (RPS)	120 Requests / sec	1,450 Requests / sec	12x Capacity Increase
Database Connection Failures	45 errors / hour	0 errors / hour	100% Uptime & Stability

Root Cause Diagnostics

Using APM distributed tracing (OpenTelemetry) and PostgreSQL slow query logging (pg_stat_statements), we identified three severe bottlenecks:

▸
Missing Composite Index on 15M Rows: The main dashboard query performed a full sequential scan across 15,000,000 records to filter by tenant_id and status while sorting by created_at. Each query took 1,250ms.
▸
N+1 ORM Query Loop: The user list endpoint fetched 50 items in Query 1, then executed 50 individual SQL queries inside a loop to fetch nested user permissions.
▸
Database Connection Overhead: Backend microservices spawned new TCP database connections on every incoming HTTP request, consuming 35% of PostgreSQL server RAM on idle process overhead.

Step-by-Step Engineering Remediation

Step 1: Composite B-Tree Indexing

We analyzed EXPLAIN ANALYZE execution plans and created targeted composite B-Tree indexes using non-blocking concurrent index creation:

-- 1. Identify slow queries in pg_stat_statements
SELECT query, calls, total_exec_time / calls AS avg_time_ms, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- 2. Create non-blocking composite index on tenant_id + status + created_at
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created 
ON orders (tenant_id, status, created_at DESC);

-- 3. Verify index usage with EXPLAIN ANALYZE
EXPLAIN ANALYZE 
SELECT id, total_amount, created_at 
FROM orders 
WHERE tenant_id = 'tenant_9821' AND status = 'COMPLETED' 
ORDER BY created_at DESC 
LIMIT 50;
-- Result: Replaced Sequential Scan (1,250ms) with Index Scan (3.2ms)!

Step 2: Fixing N+1 Queries with Eager Loading

We refactored ORM endpoint handlers to use eager SQL JOIN loading, replacing 51 individual queries with 1 optimized single query:

# BEFORE (N+1 Query Loop - 51 Queries executed!):
users = db.query(User).filter(User.tenant_id == tenant_id).all()
for user in users:
    user.permissions = db.query(Permission).filter(Permission.user_id == user.id).all()

# AFTER (Eager JOIN Loading - 1 Query executed!):
users = db.query(User)          .options(joinedload(User.permissions))          .filter(User.tenant_id == tenant_id)          .all()

Step 3: Redis Cache-Aside Layer with Invalidation

We implemented a resilient Redis caching layer for read-heavy API responses with explicit invalidation triggers on data updates:

import redis
import json

redis_client = redis.Redis(host='redis-cluster.internal', port=6379, db=0, decode_responses=True)

def get_tenant_dashboard_analytics(tenant_id: str):
    cache_key = f"cache:analytics:v1:{tenant_id}"
    
    # 1. Fast path: Attempt Redis cache fetch (1.5ms)
    cached_data = redis_client.get(cache_key)
    if cached_data:
        return json.loads(cached_data)
        
    # 2. Slow path: Fallback to PostgreSQL on cache miss (12ms)
    analytics = fetch_dashboard_from_db(tenant_id)
    
    # 3. Store in Redis with 10-minute TTL
    redis_client.setex(cache_key, 600, json.dumps(analytics))
    return analytics

def invalidate_tenant_cache(tenant_id: str):
    """Triggered by webhooks or DB mutation events"""
    cache_key = f"cache:analytics:v1:{tenant_id}"
    redis_client.delete(cache_key)

Step 4: PgBouncer Connection Pooling

We deployed PgBouncer in front of PostgreSQL in transaction pooling mode, capping backend database connections to a stable 50 pool processes while multiplexing thousands of incoming client requests:

# /etc/pgbouncer/pgbouncer.ini
[databases]
saas_production = host=127.0.0.1 port=5432 dbname=saas_production

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 5

Final Technical Outcomes

▸
P95 Latency: Reduced from 2,500ms to 180ms (92.8% decrease).
▸
Database CPU Load: Dropped from 88% to 18%, providing ample headroom for business growth.
▸
Cost Savings: Avoided a $4,000/mo database hardware upgrade by optimizing existing open-source software layer configuration.