Performance & Optimization

Performance Fundamentals: Measure First

The Performance Stack

Database performance problems rarely come from a single cause, and the fix at each layer has a very different impact. A badly written query can be 1000x slower than an optimized one. A missing index on a million-row table can turn a 1ms lookup into a 10-second full scan. Better hardware, by contrast, gives you at most 2-5x. This means the order in which you attack performance problems matters enormously: start at the top of the stack where gains are largest and cheapest, and only move down to infrastructure changes after the higher layers are exhausted. The four layers below define that priority order for this lesson.

Priority: Start at Layer 1 (queries). A badly written query can be 1000x slower than an optimized one. Hardware upgrades give you 2-5x at best.

Step 1: Identify Bottlenecks

Performance optimization without measurement is just guessing. Before changing a single query, index, or configuration setting, you need to know exactly where time is being spent. Database slowdowns can originate from many places: a handful of poorly written queries, missing indexes on high-traffic columns, lock contention between concurrent transactions, or resource exhaustion at the hardware level. Treating the wrong layer wastes time and can introduce new problems. This step covers how to surface the actual slow queries driving your load, using database built-ins and external tooling, so that every optimization you make is backed by data rather than intuition.

Finding Slow Queries

Before you can fix a performance problem, you need to find it. Most applications have hundreds of distinct queries, but only a handful are responsible for the majority of database load. Without profiling, teams waste time optimizing queries that run in 10ms while ignoring one that runs in 5 seconds and is called 10,000 times per hour. The tools below let you rank queries by total time consumed, so you always fix the highest-impact bottleneck first.

-- Enable pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- Find top 10 slowest queries by total time
SELECT
    query,
    calls,
    total_exec_time / 1000 as total_time_sec,
    mean_exec_time / 1000 as avg_time_sec,
    max_exec_time / 1000 as max_time_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

query                          | calls | total_time_sec | avg_time_sec
-------------------------------+-------+----------------+--------------
SELECT * FROM orders WHERE... | 10000 |       1250.3   |        0.125
UPDATE users SET last_seen... |  5000 |        850.2   |        0.170

Focus on: High total_time (called often) OR high avg_time (single slow query)

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries > 1 second

-- Analyze with pt-query-digest (Percona Toolkit)
pt-query-digest /var/log/mysql/slow.log

# 5 queries in 0.01x secs, 5 QPS, 5x concurrency

# Query 1: 0.50x slower (worst)
# Exec time  3s  min:  3s  max:  3s  avg:  3s
# Lock time  0   min:  0   max:  0   avg:  0
# Rows examined  1000000  Rows sent  1

SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

# (Ranked list continues for all queries above the threshold)

Step 2: Query Optimization

Query optimization is the highest-leverage step in the performance stack. A single poorly written query can bring a well-provisioned database to its knees, while a well-written query runs efficiently even without indexes. Before reaching for indexes, caching, or bigger hardware, the first question should always be: does the database understand what you are asking for, and is it finding the data in the most efficient way possible? This section covers the two essential skills: reading query execution plans with EXPLAIN to diagnose problems, and recognizing the common anti-patterns that cause unnecessary full table scans, repeated round-trips, and excessive data transfer.

Understanding EXPLAIN

Once you've identified a slow query, EXPLAIN tells you exactly why it is slow. Rather than executing the query, it shows the database's execution plan, the step-by-step strategy the query planner chose to retrieve your data. You can see whether it's scanning every row in a table (a sequential scan), or jumping straight to matching rows via an index (an index scan). The difference between these two plans can be the difference between a query that takes 2,500ms and one that takes 120ms.EXPLAIN ANALYZE goes one step further and actually runs the query, reporting both the estimated and real execution times, making it the definitive tool for diagnosing query performance.

-- PostgreSQL: EXPLAIN ANALYZE (shows actual execution)
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

Seq Scan on users  (cost=0.00..10000.00 rows=100000 width=40)
  Filter: (created_at > '2024-01-01'::date)
  Rows Removed by Filter: 500000
Planning Time: 0.5 ms
Execution Time: 2500 ms   ← scans ALL 600K rows to find 100K matches

-- Fix: Add index on created_at
CREATE INDEX idx_users_created_at ON users(created_at);

Index Scan using idx_users_created_at on users  (cost=0.00..500.00 rows=100000 width=40)
  Index Cond: (created_at > '2024-01-01'::date)
Planning Time: 0.4 ms
Execution Time: 120 ms   ← 20x faster (cost 10000 → 500, time 2500ms → 120ms)

Common Query Anti-Patterns

Anti-patterns are coding habits that feel natural but silently destroy database performance. They often go unnoticed in development, where datasets are small, but cause serious slowdowns in production with millions of rows. Each pattern below has a clear fix that requires no schema changes, just better SQL.

-- BAD: Fetches all 50 columns (including large TEXT fields)
SELECT * FROM products WHERE category = 'electronics';

-- Scanned: 1M rows × 50 columns = 50M cells
-- Time: 3.2 seconds

-- GOOD: Select only needed columns
SELECT id, name, price FROM products WHERE category = 'electronics';

-- Scanned: 1M rows × 3 columns = 3M cells
-- Time: 0.4 seconds

-- BAD: 1 query for users + N queries for orders
SELECT * FROM users LIMIT 100;  -- 1 query

-- Then in application loop:
for user in users:
    SELECT * FROM orders WHERE user_id = user.id;  -- 100 queries!

-- Total: 101 queries, 850ms

-- GOOD: Single query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LIMIT 100;

-- Total: 1 query, 45ms

-- BAD: Subquery runs for EACH row
SELECT
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;

-- Subquery executes 1M times!
-- Time: 45 seconds

-- GOOD: Use JOIN with GROUP BY
SELECT
    u.name,
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Single pass through data
-- Time: 2.3 seconds

-- BAD: Index can't be used with leading %
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Full table scan: 10M rows
-- Time: 15 seconds

-- GOOD: Trailing wildcard can use index
SELECT * FROM users WHERE email LIKE 'john%';

-- Index scan (if index exists on email)
-- Time: 0.05 seconds

-- Alternative for full-text: Use specialized search
-- PostgreSQL: to_tsvector, MySQL: FULLTEXT index

Step 3: Indexing Strategy

Indexes are the highest-leverage optimization after rewriting bad queries. Structurally, an index is a sorted copy of one or more columns maintained by the database engine. Instead of scanning every row in a table (O(n)), the engine can do a binary search through the index tree and jump directly to the matching rows (O(log n)). For a table with 10 million rows, that difference can mean a query taking milliseconds instead of seconds. The trade-off is real though: every index consumes disk space and must be updated on every INSERT, UPDATE, and DELETE. Over-indexing slows writes and inflates storage, so choosing which columns to index, and which type of index to use, is as important as adding indexes at all.

When to Index

Types of Indexes

B-Tree Index (Default)

Best for: Equality (=) and range (<, >, BETWEEN) queries

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Query: O(log n) instead of O(n)
SELECT * FROM users WHERE email = 'john@example.com';
-- Without index: Scans 10M rows (8 seconds)
-- With index: Scans 1 row (0.002 seconds) - 4000x faster!

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Works efficiently for:
WHERE user_id = 123 AND order_date > '2024-01-01'  ✅
WHERE user_id = 123                                 ✅
-- Does NOT work efficiently for:
WHERE order_date > '2024-01-01'                     ❌ (user_id not in WHERE)

Partial Index (PostgreSQL)

Index only rows matching a condition, smaller, faster indexes

-- Only index active users (90% of queries filter on status='active')
CREATE INDEX idx_users_active_email
ON users(email)
WHERE status = 'active';

-- Index size: 500MB (full) → 50MB (partial) - 10x smaller!
-- Query performance: Same speed for active users
-- Benefit: Faster updates, less disk space

Covering Index (Index-Only Scan)

Index contains ALL columns needed, no table lookup required

-- Query needs: user_id, email, created_at
SELECT user_id, email, created_at
FROM users
WHERE status = 'active';

-- Regular index: Still needs to look up table for email, created_at
CREATE INDEX idx_users_status ON users(status);

-- Covering index: Includes all needed columns
CREATE INDEX idx_users_status_covering
ON users(status, user_id, email, created_at);

-- Result: Index-only scan (no table access)
-- Speed: 2x faster (no random disk I/O to table)

Step 5: Database Configuration Tuning

Database engines ship with conservative default settings designed to run safely on modest hardware, not to perform well under real production workloads. A freshly installed PostgreSQL or MySQL instance, left unconfigured, will under-use available RAM, write to disk more aggressively than necessary, and limit parallelism in ways that make sense for a shared server but not a dedicated database host. Tuning these settings to match your actual hardware and workload profile typically yields 2-5x throughput improvements with no schema or query changes at all. The key areas are memory allocation (how much RAM the engine uses for its buffer pool and query work), write-ahead logging (balancing durability against I/O cost), and parallelism (how many CPU cores the engine is allowed to use). The examples below show the critical knobs for both PostgreSQL and MySQL.

# postgresql.conf - For server with 32GB RAM

# Memory Settings (Most Important!)
shared_buffers = 8GB              # 25% of RAM
effective_cache_size = 24GB       # 75% of RAM (OS + DB cache)
work_mem = 128MB                  # Per-query memory for sorts/joins
maintenance_work_mem = 2GB        # For VACUUM, CREATE INDEX

# Connection Settings
max_connections = 100             # Reduce if using connection pooler
# Each connection uses ~10MB, so 100 × 10MB = 1GB

# Write Performance
wal_buffers = 16MB                # WAL buffer size
checkpoint_timeout = 15min        # How often to checkpoint
checkpoint_completion_target = 0.9 # Spread checkpoints over 90% of interval

# Query Planner
random_page_cost = 1.1            # For SSD (default 4.0 is for HDD)
effective_io_concurrency = 200    # For SSD (default 1)

# Logging (for debugging slow queries)
log_min_duration_statement = 1000 # Log queries > 1 second

Default (conservative, 4GB RAM)  →  Tuned (32GB RAM server)
─────────────────────────────────────────────────────────
Complex queries:       baseline  →  5x faster
Concurrent queries:    baseline  →  3x more throughput
Write performance:     baseline  →  2x faster
shared_buffers:          128MB  →  8GB  (cache more data in memory)

# my.cnf - For server with 32GB RAM

# InnoDB Buffer Pool (Most Critical!)
innodb_buffer_pool_size = 24G     # 75% of RAM (InnoDB caches data here)
innodb_buffer_pool_instances = 8  # Split into 8 pools (for concurrency)

# InnoDB Log Settings
innodb_log_file_size = 2G         # Larger = better write performance
innodb_flush_log_at_trx_commit = 2 # 0=fast, 1=safe, 2=balanced

# Connection Settings
max_connections = 200
thread_cache_size = 100

# Query Cache (Deprecated in 8.0)
# Use Redis instead

# Temp Table Settings
tmp_table_size = 256M
max_heap_table_size = 256M

Default (512MB buffer)  →  Tuned (24GB buffer)
─────────────────────────────────────────────
Cache hit rate:   60%   →  98%
Query latency:   50ms   →  10ms  (5x faster)
Disk I/O:    baseline   →  90% reduction
Buffer pool: 512MB RAM  →  24GB RAM  (fits entire dataset in memory)

Connection Pooling

Opening a new database connection is not free. The process involves a TCP handshake, TLS negotiation, Postgres fork of a backend process, and authentication, typically 10–20ms of overhead before a single query runs. In a web application handling hundreds of concurrent requests, creating a fresh connection per request wastes significant time and can exhaust the database's connection limit entirely. Connection pooling solves this by maintaining a pool of already-open connections that are borrowed and returned rather than created and destroyed. The examples below show two levels of pooling: in-process pooling (within a single Python service) and external process-level pooling (PgBouncer / ProxySQL), which is essential when you have many application instances or serverless functions each trying to connect independently.

# Python: Using connection pool (psycopg2)
from psycopg2 import pool

# Create pool with 10 min, 50 max connections
db_pool = pool.ThreadedConnectionPool(
    minconn=10,
    maxconn=50,
    host='localhost',
    database='myapp',
    user='postgres'
)

# Get connection from pool (instant if available)
conn = db_pool.getconn()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# Return connection to pool (don't close!)
db_pool.putconn(conn)

Without pool:  15ms (connect) + 5ms (query) =  20ms per request
With pool:      0ms (connect) + 5ms (query) =   5ms per request  ← 4x faster

At 1000 req/sec:
Saves 15ms × 1000 = 15 seconds of connection overhead per second
Prevents connection storms that can crash the database server

Step 6: Hardware & Scaling

Hardware and scaling decisions are the last resort, not the first. Throwing more RAM or CPU at a database with unoptimized queries and no indexes rarely solves the problem; it just makes bad code run slightly faster until traffic grows again. That said, once queries are optimized, indexes are in place, caching is effective, and connection pooling is configured, hardware becomes the real ceiling. The two levers available are vertical scaling (a single, more powerful machine) and horizontal scaling (distributing load across multiple machines). Each has a distinct sweet spot: vertical scaling is simpler and works well up to a point, while horizontal scaling handles write-heavy workloads and global availability, but adds significant operational complexity. The sections below cover both, along with the key hardware components that have the most impact on database performance.

Vertical Scaling (Scale Up)

Horizontal Scaling (Scale Out)

Step 7: Monitoring & Observability

You cannot optimize what you do not measure, and you cannot debug incidents you did not record. Monitoring is what turns database performance from guesswork into engineering: it gives you a continuous record of how the system behaves under real traffic, so you can spot degradation trends before they become user-facing outages, correlate slowdowns with deployments or traffic spikes, and have data to justify infrastructure decisions. Observability goes one step further by making the internal state of the database queryable from the outside, combining metrics (what is happening), logs (what happened and when), and traces (which code path caused it). The sections below cover the key metrics to track, the alerting thresholds that signal real problems vs normal variance, and the tooling stack that ties it together.

Key Metrics to Monitor

Alerting Thresholds

Avoid Storing Large BLOBs in the Database

Storing large binary files, images, videos, documents, audio, directly in your database is one of the most common architectural anti-patterns. While relational databases technically support BLOB (Binary Large Object) columns, doing so at scale causes serious performance, cost, and scalability problems. The industry-standard approach is to store files in dedicated object storage (such as Amazon S3) and keep only a metadata pointer in the database.

Why BLOBs Hurt Performance

Where Is the Practical Limit?

The Recommended Pattern: S3 + URL in Database

import boto3
import psycopg2
import uuid

s3 = boto3.client("s3", region_name="us-east-1")
BUCKET = "my-app-uploads"

def upload_user_avatar(user_id: int, file_bytes: bytes, content_type: str) -> str:
    """Upload binary to S3, store only the URL in PostgreSQL."""

    # 1. Upload binary to S3 (NOT to the database)
    key = f"avatars/{user_id}/{uuid.uuid4()}.jpg"
    s3.put_object(
        Bucket=BUCKET,
        Key=key,
        Body=file_bytes,
        ContentType=content_type
    )

    # 2. Store only the S3 URL (metadata pointer) in the database
    s3_url = f"https://{BUCKET}.s3.amazonaws.com/{key}"

    with psycopg2.connect(DATABASE_URL) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "UPDATE users SET avatar_url = %s WHERE id = %s",
                (s3_url, user_id)
            )

    return s3_url


def get_avatar_url(user_id: int) -> str:
    """Fetch metadata from DB, return a time-limited presigned S3 URL."""
    with psycopg2.connect(DATABASE_URL) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT avatar_url FROM users WHERE id = %s", (user_id,))
            row = cur.fetchone()

    # Parse S3 key from stored URL, generate expiring presigned URL
    key = row[0].split(f"{BUCKET}.s3.amazonaws.com/")[1]
    return s3.generate_presigned_url(
        "get_object",
        Params={"Bucket": BUCKET, "Key": key},
        ExpiresIn=3600  # URL valid for 1 hour
    )

Database schema (stores only metadata):
  avatar_url  VARCHAR(512)  -- a URL string, not binary data

Flow:
  upload_user_avatar()  →  PUT binary to S3  →  store URL in DB
  get_avatar_url()      →  read URL from DB  →  return presigned S3 URL (expires in 1h)

Result:
  Database stays lean and fast, S3 handles all binary I/O
  Presigned URLs add time-limited access control without routing traffic through your server

Performance Optimization Checklist

Real-World Optimization: E-Commerce Platform