DB query optimization, Redis caching strategy, connection pool tuning, faster API responses, and k6 load testing. A step-by-step look at performance techniques that actually work in production.
Before you start optimizing, define the metrics and the targets. Optimization that starts without numbers easily loses its way.
| Metric | Description | Production target (typical) |
|---|---|---|
| p50 response time | 50% of all requests finish within this time | < 100ms |
| p95 response time | 95% of all requests finish within this time | < 500ms |
| p99 response time | 99% of all requests finish within this time | < 1,000ms |
| Error rate | Share of 5xx responses | < 0.1% |
| Throughput (TPS) | Requests handled per second | Depends on service scale |
Go after p95 before p99. p99 is affected by factors that are hard to control, such as network jitter and GC pauses. The efficient order is to bring p95 down reliably first, then improve p99.
The fastest way to find the cause of a slow request is to measure the time of each stage directly. Measure, do not guess.
import time
import logging
from contextlib import contextmanager
logger = logging.getLogger(__name__)
@contextmanager
def timer(label: str):
"""Context manager that times each stage of request handling"""
start = time.perf_counter()
try:
yield
finally:
elapsed_ms = (time.perf_counter() - start) * 1000
logger.info(f"[PERF] {label}: {elapsed_ms:.1f}ms")
# Usage example โ FastAPI handler
async def get_dashboard(user_id: int):
with timer("db.fetch_user"):
user = await db.get_user(user_id)
with timer("db.fetch_stats"):
stats = await db.get_stats(user_id)
with timer("cache.set"):
await redis.set(f"dashboard:{user_id}", stats, ex=300)
return {"user": user, "stats": stats}
# Sample log output:
# [PERF] db.fetch_user: 3.2ms
# [PERF] db.fetch_stats: 847.5ms โ the bottleneck!
# [PERF] cache.set: 1.1msEnable the slow query log: in PostgreSQL, log_min_duration_statement = 100 automatically logs queries that take 100ms or longer. In MySQL, set slow_query_log = ON and long_query_time = 0.1.
Most API performance problems start in the database. The three main causes are missing indexes, N+1 queries, and unnecessary full scans.
Indexes speed up reads at the cost of write speed and storage. Index the frequently queried columns first, especially those used in WHERE / ORDER BY / JOIN.
-- Problem: the combined user_id + created_at lookup is a Full Scan
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;
-- Fix: create a composite index (most selective column first)
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);
-- Covering index: include the SELECT columns in the index โ 0 table accesses
CREATE INDEX CONCURRENTLY idx_orders_cover
ON orders (user_id, created_at DESC)
INCLUDE (status, total_amount);| Index type | Best for | Watch out for |
|---|---|---|
| Single column | Simple equality lookups | No benefit when selectivity is low |
| Composite | WHERE + ORDER BY combinations | Needs a condition on the leading column |
| Covering | Queries with a fixed SELECT list | Larger index size |
| Partial index | Lookups on specific status values only | WHERE status = 'active' |
N+1 is the pattern of fetching a list of N items and then firing an extra query for each item. A list of 100 items runs 101 queries.
# Bad: N+1 queries (10 posts โ 11 queries)
posts = await db.query("SELECT * FROM posts LIMIT 10")
for post in posts:
# a separate query runs for every post
author = await db.query("SELECT * FROM users WHERE id = ?", post.user_id)
post.author = author
# Good 1: a single query with JOIN
posts = await db.query("""
SELECT p.*, u.name AS author_name, u.avatar AS author_avatar
FROM posts p
JOIN users u ON u.id = p.user_id
LIMIT 10
""")
# Good 2: batch lookup with an IN clause (when using an ORM)
posts = await Post.query.limit(10).all()
user_ids = {p.user_id for p in posts}
users = {u.id: u for u in await User.query.filter(User.id.in_(user_ids)).all()}
for post in posts:
post.author = users[post.user_id]Watch out for ORM lazy loading. Django ORM, SQLAlchemy, and JPA all lazy-load related objects by default. Load them explicitly with select_related /prefetch_related / JOIN FETCH.
-- PostgreSQL: see the actual execution plan + timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;
-- How to read the key terms
-- Seq Scan โ full scan (no index, or index not used)
-- Index Scan โ index used (good)
-- Index Only โ covering index (best)
-- Hash Join โ large JOIN (normal)
-- Nested Loop โ small JOIN (normal); a problem when large
-- rows=10000 / actual rows=1 โ stale statistics โ run ANALYZE-- Refresh statistics (when plan estimates differ widely from reality)
ANALYZE orders;
-- Check index usage (unused indexes are candidates for removal)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;A cache reuses the result of an expensive operation. Without a strategy for what to cache and when to invalidate it, the cache itself becomes a cause of incidents.
import json
import hashlib
from functools import wraps
import redis.asyncio as redis
r = redis.from_url("redis://localhost:6379", decode_responses=True)
def cache(ttl: int = 300, key_prefix: str = ""):
"""Decorator that caches the result in Redis"""
def decorator(func):
@wraps(func)
async def wrapper(*args, **kwargs):
# Cache key: function name + hash of the arguments
raw_key = f"{key_prefix or func.__name__}:{args}:{sorted(kwargs.items())}"
cache_key = hashlib.md5(raw_key.encode()).hexdigest()
cached = await r.get(cache_key)
if cached:
return json.loads(cached) # cache hit
result = await func(*args, **kwargs)
await r.set(cache_key, json.dumps(result), ex=ttl)
return result
return wrapper
return decorator
# Usage example
@cache(ttl=60, key_prefix="user_stats")
async def get_user_stats(user_id: int) -> dict:
"""DB aggregate query โ cached for 60 seconds"""
return await db.query(
"SELECT COUNT(*) orders, SUM(amount) total FROM orders WHERE user_id = ?",
user_id,
)| Pattern | Description | Suitable data |
|---|---|---|
| Cache-Aside | The app reads the cache itself โ on a miss, reads the DB and stores the result | Frequently read master data |
| Write-Through | Updates the DB and the cache together on write | Data where consistency matters |
| Write-Behind | Writes to the cache first and to the DB asynchronously | Write-heavy data that can tolerate loss |
| TTL-based invalidation | Deleted automatically after the expiry time | Statistics, aggregates, external API results |
Beware of cache stampedes: right after a TTL expires, many requests hit the DB at the same time. Defend against it by implementing a lock with Redis SET NX or by refreshing in the background before expiry.
// Next.js App Router โ cache headers per response
import { NextResponse } from 'next/server';
// Public data: CDN caches for 1 hour, the browser for 5 minutes
export async function GET() {
const data = await fetchPublicData();
return NextResponse.json(data, {
headers: {
'Cache-Control': 'public, s-maxage=3600, stale-while-revalidate=86400',
},
});
}
// Per-user data: no CDN caching, the browser caches for 1 minute
export async function GET(req: Request) {
const data = await fetchUserData();
return NextResponse.json(data, {
headers: {
'Cache-Control': 'private, max-age=60',
},
});
}# Cloudflare Cache Rules example (wrangler.jsonc)
# Static assets: cached for 1 year
# /api/public/*: cached at the edge for 30 minutes
# /api/user/*: no cache (bypass)
# Cache-Control header guide
# public, s-maxage=N โ CDN caches for N seconds
# stale-while-revalidate โ serves the stale version after expiry while refreshing in the background
# no-store โ no caching at all (sensitive data)
# must-revalidate โ must revalidate with the server as soon as it expiresWhen the connection pool is too small, requests wait in the pool and response time spikes. Make it too large and the DB server is overloaded instead.
# SQLAlchemy async connection pool settings
from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine(
"postgresql+asyncpg://user:pass@localhost/db",
pool_size=20, # connections kept open (rule of thumb: CPU cores ร 2-4)
max_overflow=10, # extra connections allowed at peak (pool_size + max_overflow = max)
pool_timeout=30, # max time to wait for a connection from the pool (seconds)
pool_recycle=1800, # recreate connections after 30 minutes (avoids DB timeouts)
pool_pre_ping=True, # validate the connection before use
)# How to size PostgreSQL max_connections
# max_connections = (CPU cores ร 4) + headroom
# e.g. 8-core server โ max_connections = 200
# PgBouncer (connection pooler) โ funnels hundreds of app connections into 10 DB connections
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction # transaction-level pooling (most efficient)
max_client_conn = 1000 # max connections accepted from the app
default_pool_size = 20 # actual connections opened to the DBConnection pool size formula: pool_size = (DB CPU cores ร 2) + effective disk parallelism. The recommendation from HikariCP author Brett Wooldridge is 2 per CPU core. Bigger is not faster โ the DB pays context-switching costs too.
Running independent I/O tasks in parallel can cut total response time sharply. Move heavy work to the background, and always paginate large lists.
import asyncio
# Bad: sequential execution (total time = sum of each task)
async def get_dashboard_slow(user_id: int):
profile = await fetch_profile(user_id) # 50ms
orders = await fetch_orders(user_id) # 80ms
stats = await fetch_stats(user_id) # 60ms
# 190ms in total
# Good: parallel execution (total time โ the slowest task)
async def get_dashboard_fast(user_id: int):
profile, orders, stats = await asyncio.gather(
fetch_profile(user_id), # โ
fetch_orders(user_id), # โโ run concurrently
fetch_stats(user_id), # โ
)
# ~80ms in total (bounded by orders, the slowest)# Cursor-based pagination โ more consistent than OFFSET on large tables
async def get_orders_cursor(user_id: int, cursor: int | None, limit: int = 20):
"""
OFFSET pagination approaches a full scan by the 100,000th page.
The cursor (last id) approach always uses only the index.
"""
query = """
SELECT id, status, total_amount, created_at
FROM orders
WHERE user_id = :user_id
AND (:cursor IS NULL OR id < :cursor)
ORDER BY id DESC
LIMIT :limit
"""
rows = await db.fetch_all(query, {"user_id": user_id, "cursor": cursor, "limit": limit})
next_cursor = rows[-1]["id"] if len(rows) == limit else None
return {"items": rows, "next_cursor": next_cursor}Measuring with k6 before and after an optimization lets you confirm the improvement in numbers. Always verify that p95 response time meets the SLA at the target TPS.
import http from 'k6/http';
import { check, sleep } from 'k6';
import { Trend } from 'k6/metrics';
const dbQueryTime = new Trend('db_query_time', true);
export const options = {
stages: [
{ duration: '1m', target: 50 }, // warm-up
{ duration: '3m', target: 200 }, // normal load
{ duration: '2m', target: 500 }, // peak load
{ duration: '1m', target: 0 }, // cool-down
],
thresholds: {
'http_req_duration': ['p(95)<500', 'p(99)<1000'],
'http_req_failed': ['rate<0.001'],
'db_query_time': ['p(95)<100'],
},
};
export default function () {
// 1. List query (validates DB + cache)
const listRes = http.get('http://localhost:3000/api/orders?limit=20', {
tags: { name: 'list' },
});
check(listRes, {
'list status 200': (r) => r.status === 200,
'list has items': (r) => r.json()?.items?.length > 0,
});
// Measurable if the server adds X-DB-Time to the response headers
const dbTime = listRes.headers['X-DB-Time'];
if (dbTime) dbQueryTime.add(Number(dbTime));
sleep(0.5);
// 2. Single-item query (checks the cache hit rate)
const id = Math.floor(Math.random() * 1000) + 1;
const detailRes = http.get(`http://localhost:3000/api/orders/${id}`, {
tags: { name: 'detail' },
});
check(detailRes, {
'detail status 200 or 404': (r) => [200, 404].includes(r.status),
});
sleep(Math.random() + 0.5);
}# Sample result summary โ before vs after optimization
# โโ Before โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
# http_req_duration p(95)=1,240ms p(99)=3,820ms โ over the SLA
# http_req_failed rate=0.82% โ error rate too high
# โโ After (index added + Redis cache) โโโโโโโ
# http_req_duration p(95)=187ms p(99)=412ms โ
# http_req_failed rate=0.00% โ
# db_query_time p(95)=23ms โ
k6 run system-perf-test.js --out json=result.jsonExpose server-side timing through response headers. Adding X-DB-Time and X-Cache-Status headers lets k6 track DB query time and the cache hit rate directly. In production, restrict them so they are only exposed to internal IPs.