Database Optimization Template for PMs

What This Template Is For

The database is the performance bottleneck in most web applications. A single missing index can turn a 5ms query into a 3-second full table scan. An N+1 query pattern hidden inside an ORM can generate 500 database round trips where 1 would suffice. A connection pool sized too small starves the application of database access during traffic spikes.

These problems compound. As data grows, unoptimized queries degrade logarithmically or worse. A query that ran in 20ms with 100K rows runs in 800ms with 10M rows if it is doing a sequential scan instead of using an index. By the time users complain, the fix requires understanding query plans, index selectivity, and connection pool behavior under load.

This template provides a structured approach to database optimization. It covers slow query identification, indexing strategy, connection pool tuning, and capacity projections. The goal is to move database performance from reactive firefighting to proactive management.

Use this alongside the API Performance Template to connect database improvements to user-facing latency. The Monitoring and Alerting Template covers alerting on database-specific metrics. For schema-level changes, the Database Migration Template provides a safe execution plan. The Technical PM Handbook explains how PMs should evaluate the product impact of database performance investments. See also the glossary entry on technical debt for context on why unoptimized databases accumulate cost over time.

When to Use This Template

• User-facing latency has increased and the root cause traces to database queries
• A specific query or set of queries consistently appear in slow query logs
• You are approaching a data milestone (10M rows, 100M rows) and need to verify query performance scales
• Cloud database costs are increasing and you suspect query inefficiency is a factor
• A new feature will add significant read or write load to existing tables
• Connection pool errors (timeouts, exhaustion) are occurring during peak traffic

How to Use This Template

1. Start with the Slow Query Audit. Pull the top offenders from your slow query log and analyze each one.
2. Build the Indexing Strategy. For each slow query, determine whether an index would help and what columns to index.
3. Tune the Connection Pool. Review current settings against actual usage patterns.
4. Estimate Capacity. Project when your current database configuration will no longer support traffic growth.
5. Prioritize and schedule. Not every optimization is equally impactful. Rank by user-facing latency improvement.
6. Measure after each change. Compare before/after for every optimization. If the numbers do not improve, revert.

The Template

Database Overview

Slow Query Audit

How to find slow queries.

• Postgres: pg_stat_statements extension (tracks all queries with timing), or log_min_duration_statement in config.
• MySQL: slow_query_log with long_query_time threshold.
• Application APM: Datadog, New Relic, or similar will attribute latency to specific queries.

Query Analysis

For each slow query, document the following:

Query #1: [Description]

-- Original query
[Paste the full SQL]

EXPLAIN ANALYZE output.

[Paste the EXPLAIN ANALYZE output]

Analysis.

• Scan type. [Sequential scan / Index scan / Index-only scan / Bitmap heap scan]
• Rows estimated vs actual. [Estimated X, Actual Y. If >10x off, statistics may be stale.]
• Bottleneck. [Where is time being spent? Sort? Filter? Join? Disk I/O?]
• Proposed fix. [Add index / Rewrite query / Add caching / Partition table]
• Estimated improvement. [X ms to Y ms]

Indexing Strategy

Index design guidelines.

• Composite indexes. Order columns by selectivity (most selective first) and by query filter order. An index on (user_id, created_at) serves queries filtering on user_id alone, but not queries filtering on created_at alone.
• Partial indexes. If a query always filters on a condition (e.g., WHERE deleted_at IS NULL), a partial index is smaller and faster.
• Covering indexes (INCLUDE). Add non-indexed columns with INCLUDE to enable index-only scans (Postgres 11+). Avoids heap lookups.
• Index size. Indexes consume disk and memory. A 500MB index on a table with 100M rows is normal. A 5GB index suggests over-indexing.
• Write impact. Every index slows down INSERT, UPDATE, and DELETE operations. Add indexes selectively on write-heavy tables.

Connection Pool Configuration

Total connections check. [App instances] x [Pool size + Max overflow] = [Total max connections]. This must be less than the database's max_connections setting, with headroom for admin connections and monitoring.

Capacity Projections

Optimization Backlog

Filled Example: Postgres-Backed SaaS Application

Database Overview

Slow Query Audit

Query Analysis: Dashboard Event Count

SELECT date_trunc('day', created_at) as day, COUNT(*)
FROM events
WHERE user_id = $1 AND created_at > NOW() - INTERVAL '30 days'
GROUP BY day ORDER BY day;

EXPLAIN ANALYZE. Sequential scan on events (85M rows). Estimated 85M rows, actual 85M. Filter removes 99.97% of rows. Sort on day.

Bottleneck. No index on (user_id, created_at). The planner scans the entire 85M row table to find ~25K matching rows.

Fix. Add composite index CREATE INDEX CONCURRENTLY idx_events_user_created ON events (user_id, created_at).

Estimated improvement. 840ms to 8ms (index scan on ~25K rows instead of seq scan on 85M).

Indexing Strategy

Connection Pool (Current Issues)

The application runs 8 instances with pool_size=25 each. 8 x 25 = 200, which equals max_connections. No headroom. During deploys (rolling restart), new instances open connections before old ones close, causing connection exhaustion errors.

Fix. Reduce pool_size to 20 per instance (160 total). Add PgBouncer as a connection pooler in front of RDS with max 180 server connections and 1,000 client connections.

Key Takeaways

• Fix the top 3 slow queries first. In most applications, 3-5 queries account for 80% of total database time. Use pg_stat_statements (Postgres) or equivalent to find them.
• Add indexes based on EXPLAIN ANALYZE, not guesswork. An index that the query planner does not use wastes disk and slows writes. Always verify with EXPLAIN ANALYZE after adding an index.
• Size your connection pool deliberately. Too small and requests queue for connections. Too large and the database spends time context-switching between connections. The formula (cores * 2) + spindles is a reasonable starting point.
• Project capacity before you hit limits. If your largest table doubles every 6 months, you need to plan for partitioning or archiving before query performance degrades.
• Measure before and after every optimization. Without before/after data, you cannot verify that the change helped and you cannot prioritize future optimizations.