/database-performance

name: database-performance
description: Use when a database is the bottleneck. Covers finding the expensive queries, index strategy, lock contention, connection saturation, and the schema decisions that make queries fast or impossible.
metadata:
  category: data
  version: 1.0.0
  tags: [performance, indexing, locks, query-optimization, scaling]

Database Performance

Purpose

Make the database fast by finding what it is actually spending time on. Database performance work fails when it starts from intuition; it succeeds when it starts from `EXPLAIN ANALYZE` and the slow-query log.

When to Use

• An endpoint is slow and the database is suspected.
• CPU or I/O on the database server is saturated.
• Lock waits, timeouts, or deadlocks in production.
• Planning for a tenfold increase in data volume.

Capabilities

• Query profiling and execution-plan analysis.
• Index strategy: what to add, and what to remove.
• Lock contention and deadlock diagnosis.
• Connection pool sizing.
• Denormalization, materialization, and partitioning as scaling tools.

Inputs

• The slow query log, or the top queries by total execution time.
• Actual execution plans, with row counts.
• Table sizes, index sizes, and hardware.

Outputs

• The identified bottleneck, with evidence.
• A change — index, query rewrite, or schema — and its measured effect.
• Monitoring so the regression is caught next time.

Workflow

1. **Rank by total time, not by mean** — The query taking 20ms and running 50,000 times per minute is the problem. The 3-second report that runs hourly is not. 2. **Read the plan** — `EXPLAIN (ANALYZE, BUFFERS)`. Look for: a sequential scan on a large table, an estimate that differs from the actual by more than 10x, and a nested loop over many rows. 3. **Fix the biggest thing** — Usually a missing index, an N+1 from the application, or a query that fetches far more rows than it uses. 4. **Check the locks** — If queries are fast in isolation but slow in production, the problem is contention, not the plan. Look at lock waits and long-running transactions. 5. **Size the pool correctly** — More connections is not more throughput. Beyond the point where the database is saturated, additional connections increase latency for everyone. 6. **Re-measure under production-like load** — A query that is fast on a warm cache with 10,000 rows tells you nothing about 10 million.

Best Practices

• The N+1 query is the most common database performance defect in application code, and it is invisible in the database's own metrics — it looks like a lot of fast queries. Count queries per request.
• An index makes reads faster and every write slower. A table with fifteen indexes has a write path that is fifteen times more expensive than it needs to be.
• `SELECT *` prevents index-only scans and transfers columns nobody uses. On a wide table this is a significant, invisible cost.
• A long-running transaction blocks vacuum, holds locks, and can stall an entire migration. Keep transactions short — and never open one around an HTTP call.
• Connection pool size should be roughly `(core_count * 2) + effective_spindle_count`, not "as many as the application can open". A pool of 500 against an 8-core database is a queue with extra steps.
• Denormalize deliberately, with a plan for keeping the copy consistent. An unmaintained denormalized column is a bug that reports itself as a customer complaint.

Examples

**Reading a plan for what is actually wrong:**

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_cents, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC LIMIT 50;

Limit  (cost=284913.42..284919.26 rows=50) (actual time=3184.221..3184.238 rows=50 loops=1)
  ->  Sort  (actual time=3184.219..3184.229 rows=50 loops=1)
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (actual time=812.4..3102.8 rows=48,213 loops=1)
              ->  Seq Scan on orders o  (actual time=0.9..2894.1 rows=48,213 loops=1)
                    Filter: ((status = 'open') AND (created_at > ...))
                    Rows Removed by Filter: 8,214,502          <-- reading 8.2M rows
                    Buffers: shared read=184,221               <-- ~1.4 GB from disk
Planning Time: 0.4 ms
Execution Time: 3184.9 ms

The diagnosis is in "Rows Removed by Filter": Postgres read 8.2 million rows to return 50. The fix is an index that matches the filter *and* the sort:

CREATE INDEX CONCURRENTLY idx_orders_open_recent
  ON orders (created_at DESC)
  WHERE status = 'open';
-- Execution time: 3184ms -> 1.8ms. The partial index is also small enough
-- to stay entirely in cache.

**Finding an N+1 from the application side:**

# The database sees 201 fast queries and reports no problem.
# The endpoint takes 900ms. Count queries per request to see it.
with query_counter() as counted:
    response = client.get("/orders")
assert counted.total <= 3, f"N+1 detected: {counted.total} queries for one request"

Notes

• An index that matches both the `WHERE` and the `ORDER BY` lets the database skip sorting entirely. This is frequently the difference between 3 seconds and 2 milliseconds, and it is why column order in the index matters.
• Deadlocks are almost always caused by two transactions acquiring the same locks in different orders. Fix the ordering; retry logic only papers over it.
• Adding read replicas does not fix a slow query — it just runs the slow query on more machines. Fix the query first.