Performance & Optimization

The 80/20 Rule of Database Performance

A slow query can bring down your entire application. But here's the secret: 80% of performance problems come from 20% of queries. This lesson teaches you systematic optimization, from finding the bottlenecks (profiling) to fixing them (indexing, query rewriting, caching, and hardware tuning). You'll learn to measure before optimizing, focus on high-impact changes, and avoid premature optimization that wastes time.

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
-- Requires shared_preload_libraries = 'pg_stat_statements' in
-- postgresql.conf, then a server restart. CREATE EXTENSION alone
-- succeeds but silently collects nothing without that preload.
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 status=$1 ORDER BY  │    12 │          0.233 │       0.0194 │
│ UPDATE users SET last_seen = now() WHERE id %  │     6 │          0.107 │       0.0178 │
└────────────────────────────────────────────────┴───────┴────────────────┴──────────────┘

Real output from PostgreSQL 16 with pg_stat_statements enabled, after
12 SELECTs and 6 UPDATEs against a 400,000-row table.

-- MySQL, in a SQL client: enable the slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries slower than 1 second

# Then, in a shell: summarise the log by total time consumed
# (pt-query-digest ships with the Percona Toolkit)
pt-query-digest /var/log/mysql/slow.log

# Overall: 5 total, 1 unique, 0 QPS, 0x concurrency ____________________
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          646ms   128ms   132ms   129ms   128ms       0   128ms
# Lock time            6us     1us     2us     1us     1us       0     1us
# Rows sent          1.27M 260.42k 260.42k 260.42k 260.42k       0 260.42k
# Rows examine       5.09M   1.02M   1.02M   1.02M   1.02M       0   1.02M
# Query size           325      65      65      65      65       0      65

# Profile
# Rank Query ID                            Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
#    1 0x0614FDEFF616446BB343D895A18935DE  0.6463 100.0%     5 0.1293 0.00 SELECT orders

# Query 1: 0 QPS, 0x concurrency, ID 0x0614FDEFF616446BB343D895A18935DE
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       5
# Exec time    100   646ms   128ms   132ms   129ms   128ms       0   128ms
# Rows sent    100   1.27M 260.42k 260.42k 260.42k 260.42k       0 260.42k
# Rows examine 100   5.09M   1.02M   1.02M   1.02M   1.02M       0   1.02M
# Query_time distribution
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+

Real output from Percona Toolkit 3.7.0 against MySQL 8.4, on an
800,000-row orders table with no index on status.

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

Measured on 1,000,000 products (PostgreSQL 16, EXPLAIN ANALYZE):

  SELECT *                      413.598 ms
  SELECT id, name, price        229.931 ms

About 1.8x, not the 10x this advice is often sold with. The win comes
from moving less data per row, so it scales with how wide the row is
and how much of it you skip. Worth doing, but if a query is slow the
column list is rarely the reason: check the index first.

# BAD: 1 query for the users, then N more inside the loop.
# This is Python, not SQL: the N+1 problem lives in application code,
# which is exactly why it hides from people who only read the schema.
users = db.query("SELECT * FROM users LIMIT 100")      # 1 query

for user in users:                                      # 100 more
    user.orders = db.query(
        "SELECT * FROM orders WHERE user_id = %s", user.id
    )

# Total: 101 round trips, ~850 ms

-- CAREFUL: this is 1 query, but LIMIT 100 now caps ROWS, not users.
-- A user with 30 orders eats 30 of your 100 rows, so you get far
-- fewer than 100 users back. It is not equivalent to the loop above.
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LIMIT 100;

-- GOOD: pick the 100 users first, then fetch their orders.
WITH page AS (
    SELECT * FROM users ORDER BY id LIMIT 100
)
SELECT p.*, o.*
FROM page p
LEFT JOIN orders o ON o.user_id = p.id;

-- Total: 1 query, and exactly 100 users

Measured over a real TCP connection, 100 users, psycopg 3:

  101 separate queries        8.0 ms
  1 query with a join         0.6 ms

About 14x, and almost none of it is the database: each round trip costs
a network hop plus parse and plan. That is why N+1 gets dramatically
worse the further your app sits from the database, and why it often
looks fine on a laptop and terrible in production.

-- 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

Measured on 100,000 users and 1,000,000 orders:

  correlated subquery         574.427 ms
  LEFT JOIN + GROUP BY        242.476 ms

About 2.4x here. PostgreSQL's planner can sometimes rewrite a
correlated subquery into a join itself, which is why the gap is smaller
than the "runs once per row" mental model suggests. Do not count on it:
the rewrite is not guaranteed, and on other engines you get the naive
behaviour.

-- 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

Measured on 1,000,000 users, both patterns matching one row:

  LIKE '%user123456@gmail.com'   Seq Scan     108.731 ms
  LIKE 'user123456@%'            Seq Scan      22.471 ms   <- still a scan!
  LIKE 'user123456@%' with a text_pattern_ops index
                                 Index Scan     0.036 ms

The middle line is the trap. "Trailing wildcards can use an index" is
only true if the index can be searched by prefix, and in PostgreSQL a
plain B-tree on a text column in a non-C collation CANNOT be. You need:

  CREATE INDEX idx_users_email_pattern
  ON users(email text_pattern_ops);

With that, the prefix search goes from 22 ms to 0.036 ms. The leading
wildcard stays a full scan no matter what you index, because there is
no prefix to seek to.

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: O(log n) lookup instead of O(n)
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';

-- Composite index. Column order decides which queries it can serve:
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
--   WHERE user_id = 123 AND order_date > '2024-01-01'   uses both columns
--   WHERE user_id = 123                                 uses the first
--   WHERE order_date > '2024-01-01'                     leading column
--                                                       missing, so this
--                                                       index is little help
SELECT count(*) FROM orders WHERE user_id = 123 AND order_date > '2024-01-01';

EXPLAIN ANALYZE on a 1,000,000-row users table, PostgreSQL 16:

before  ->  Parallel Seq Scan on users
            (actual time=17.084..17.084 rows=0 loops=3)
            Execution Time: 22.743 ms

after   ->  Index Scan using idx_users_email on users
            (actual time=0.017..0.018 rows=0 loops=1)
            Execution Time: 0.029 ms

Roughly 780x on this machine. Treat that multiplier as illustrative:
it grows with table size and shrinks as more of the table matches.

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: removed in MySQL 8.0.3, do not set query_cache_*
# 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: connection pooling with psycopg 3 (the current driver;
# psycopg2 is in maintenance-only mode).
#   pip install "psycopg[binary,pool]"
from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    "postgresql://postgres@localhost/myapp",
    min_size=10,
    max_size=50,
)

# The context manager checks a connection out and, crucially, always
# returns it, even if the body raises. Hand-rolled getconn/putconn
# leaks a connection on every exception until the pool is exhausted.
with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        row = cur.fetchone()

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 uuid

import boto3
import psycopg

s3 = boto3.client("s3", region_name="us-east-1")
BUCKET = "my-app-uploads"
DATABASE_URL = "postgresql://app@localhost/myapp"   # read from config in real code

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 psycopg.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 psycopg.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