Database Performance Optimization and Indexing Strategies [2026]

A 100-millisecond increase in page load time reduces conversion rates by up to 7%, according to research from Akamai. Database query performance is the most common source of application latency spikes — not network overhead, not business logic, but query plans that scan millions of rows to return ten results. Database performance optimization is the discipline of ensuring that queries access exactly the data they need, using indexes that eliminate unnecessary row examination, with configurations tuned to the server's available memory.

This guide covers database performance optimization and indexing strategies from first principles: identifying slow queries with diagnostic tools, understanding query execution plans, designing indexes that match actual query patterns, resolving the N+1 query problem, and monitoring production databases for gradual degradation. The examples use PostgreSQL, but the principles apply to MySQL, SQL Server, and other relational databases.

Database Performance Optimization: Identifying the Problem

Performance work without measurement is guessing. The first step in every optimization is identifying which queries are slow and how often they run — not which queries look like they might be slow.

Using pg_stat_statements

pg_stat_statements accumulates statistics on every query executed:

-- Top 20 queries by total execution time
SELECT
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_sec,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows / NULLIF(calls, 0) AS avg_rows
FROM pg_stat_statements
WHERE calls > 100  -- Ignore rarely-called queries
ORDER BY total_exec_time DESC
LIMIT 20;

Interpreting results:

• total_exec_time = aggregate server time consumed. This is the business impact metric.
• mean_exec_time = average duration per call. Combined with calls, indicates individual query health.
• High stddev_exec_time = inconsistent performance (suggests lock contention or data distribution issues)
• avg_rows >> expected = fetching more rows than needed (missing WHERE clause, wrong filter)

Setting Up Slow Query Logging

For development and staging environments, log all queries exceeding a threshold:

# postgresql.conf
log_min_duration_statement = 100   # Log queries over 100ms
log_statement = 'none'             # Don't log all statements
auto_explain.log_min_duration = 100  # Auto-explain for slow queries
auto_explain.log_analyze = true

Query Execution Plan Analysis

EXPLAIN ANALYZE is the primary diagnostic tool for understanding why a specific query is slow.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
    u.id,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS lifetime_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2025-01-01'
  AND u.country = 'US'
GROUP BY u.id, u.email
ORDER BY lifetime_value DESC NULLS LAST
LIMIT 100;

Reading the Execution Plan

Limit  (cost=8432.15..8432.40 rows=100 width=48) (actual time=342.1..342.2 rows=100 loops=1)
  ->  Sort  (cost=8432.15..8445.80 rows=5460 width=48) (actual time=342.1..342.2 rows=100 loops=1)
        Sort Key: (sum(o.total_amount)) DESC NULLS LAST
        Sort Method: top-N heapsort  Memory: 34kB
        ->  HashAggregate  (cost=8079.25..8133.85 rows=5460 width=48) (actual time=336.7..340.5 rows=5460 loops=1)
              Group Key: u.id
              ->  Hash Left Join  (cost=1982.30..7805.45 rows=54760 width=28) (actual time=18.3..312.0 rows=54760 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..4821.00 rows=250000 width=16) (actual time=0.1..145.3 rows=250000 loops=1)
                    ->  Hash  (cost=1846.80..1846.80 rows=10840 width=20) (actual time=17.8..17.8 rows=10840 loops=1)
                          ->  Index Scan using idx_users_country_created on users u  (cost=0.43..1846.80 rows=10840 width=20) (actual time=0.1..14.2 rows=10840 loops=1)
                                Index Cond: ((country = 'US') AND (created_at >= '2025-01-01'))

What to look for:

• Seq Scan on a large table with few rows returned: indicates missing index
• actual rows vs rows estimate: large discrepancy means stale statistics → run ANALYZE
• Buffers: shared read=X: large read value means disk I/O — cache miss
• Join method: Nested Loop is efficient for small inner sets; Hash Join for large sets; Merge Join for pre-sorted inputs

Database Indexing Strategies

The Index Selection Decision

An index speeds up reads and slows down writes. Every INSERT, UPDATE, and DELETE must update all indexes on the affected table. This write overhead is acceptable for read-heavy tables but becomes a bottleneck for high-write tables.

Index when:

• Column appears in WHERE, JOIN ON, or ORDER BY clauses of frequent queries
• Column has high cardinality (many distinct values)
• Query selectivity is high (filter returns less than 5% of rows)

Avoid indexing when:

• Table has under 10,000 rows (sequential scan is faster)
• Column has very low cardinality (boolean, status with 3 values)
• Table is write-heavy with few reads (cost outweighs benefit)
• Index already covered by a compound index starting with the same column

Compound Index Design

The column order in a compound index determines which queries benefit:

-- Index: (country, status, created_at)
-- Supports these queries:
WHERE country = 'US'                            -- Uses index (leftmost prefix)
WHERE country = 'US' AND status = 'active'      -- Uses index
WHERE country = 'US' AND status = 'active' AND created_at > '2026-01-01'  -- Full use
WHERE status = 'active'                         -- Does NOT use index (missing leftmost)
WHERE created_at > '2026-01-01'                 -- Does NOT use index

-- For WHERE status = 'active' and WHERE created_at > ..., create separate indexes

The ESR rule (Equality, Sort, Range): In compound indexes, put equality conditions first, sort conditions second, range conditions last. This ordering maximizes index utility.

-- Query: WHERE tenant_id = 1 AND status = 'active' ORDER BY created_at DESC
-- Correct compound index:
CREATE INDEX idx_orders_tenant_status_date
ON orders (tenant_id, status, created_at DESC);
-- tenant_id: equality, status: equality, created_at: sort

Covering Indexes

A covering index includes all columns needed to satisfy a query, enabling index-only scans that never touch the table heap.

-- Query requires: user_id (filter), email, display_name, created_at (output)
-- Covering index:
CREATE INDEX idx_users_covering
ON users (country, status)
INCLUDE (email, display_name, created_at);
-- INCLUDE columns satisfy the query output without a heap fetch

Functional and Partial Indexes

-- Functional index for case-insensitive search
CREATE INDEX idx_users_email_ci ON users (lower(email));
-- Requires: WHERE lower(email) = lower($1)

-- Partial index: only index active records
CREATE INDEX idx_orders_active_user
ON orders (user_id, created_at DESC)
WHERE status = 'active';
-- Smaller index, lower write overhead, benefits queries with WHERE status = 'active'

-- JSONB path index
CREATE INDEX idx_metadata_feature_flag
ON products ((metadata->>'feature_flag'));
WHERE metadata ? 'feature_flag';

Resolving the N+1 Query Problem

The N+1 problem occurs when an application executes 1 query to fetch a list of N items, then N additional queries to fetch related data for each item.

N+1 pattern (1 + N queries):

# This executes 1 query to get orders + N queries to get each user
orders = Order.query.filter_by(status='active').limit(100).all()
for order in orders:
    print(order.user.email)  # Separate query per order
# Total: 1 + 100 = 101 queries

Solution 1: Eager loading with JOIN

# Single query with JOIN
orders = Order.query.options(
    joinedload(Order.user)
).filter_by(status='active').limit(100).all()
# Total: 1 query

Solution 2: Batch loading

orders = Order.query.filter_by(status='active').limit(100).all()
user_ids = [o.user_id for o in orders]
users = {u.id: u for u in User.query.filter(User.id.in_(user_ids)).all()}
for order in orders:
    user = users[order.user_id]
    print(user.email)
# Total: 2 queries

Detecting N+1 in production:

-- pg_stat_statements shows many calls to the same query pattern
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%SELECT%users%WHERE%id%$1%'
  AND calls > 1000
ORDER BY calls DESC;
-- High call count with parameterized WHERE id = $1 indicates N+1 from ORM

Database Performance Monitoring

Active Query Monitoring

-- Currently running queries (find slow/blocked queries)
SELECT
    pid,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    LEFT(query, 80) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- Blocking and blocked queries
SELECT
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query
FROM pg_stat_activity blocking
JOIN pg_stat_activity blocked
  ON blocked.wait_event_type = 'Lock'
  AND blocked.wait_event IS NOT NULL
WHERE blocking.pid != blocked.pid;

Table and Index Health

-- Tables with most sequential scans (candidates for new indexes)
SELECT
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000
ORDER BY seq_scan * n_live_tup DESC
LIMIT 20;

-- Dead tuple accumulation (needs VACUUM)
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    ROUND(100 * n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

Query Rewriting Patterns for Performance

Beyond indexes, poorly written queries cause unnecessary work even when indexes exist.

Avoiding Index Suppression

Certain query patterns prevent the optimizer from using an existing index:

Function on indexed column (suppresses index):

-- Does NOT use index on created_at
WHERE DATE(created_at) = '2026-01-15'

-- DOES use index on created_at
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'

Implicit type cast (suppresses index):

-- If user_id is INTEGER but passed as text, implicit cast suppresses index
WHERE user_id = '42'  -- String comparison forces cast

-- Explicit match on correct type
WHERE user_id = 42    -- Integer comparison uses index

Leading wildcard on LIKE (suppresses index):

-- Does NOT use B-Tree index
WHERE email LIKE '%@example.com'

-- DOES use B-Tree index (left-anchored)
WHERE email LIKE 'alice%'

-- For suffix search, use a reverse function index or full-text search

Optimizing Aggregation Queries

Large GROUP BY aggregations often scan more rows than necessary. Pre-filter before aggregating:

-- Subquery reduces rows before aggregation
SELECT customer_id, SUM(amount) as total
FROM (
    SELECT customer_id, amount
    FROM orders
    WHERE status = 'completed'
      AND created_at >= NOW() - INTERVAL '90 days'
) recent_orders
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total DESC;

Partial aggregation for dashboard queries: For dashboards that update frequently with the same base query, materialized views or scheduled aggregation tables cache the expensive computation:

-- Refresh materialized view for dashboard (can be refreshed concurrently)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_summary;

Selecting Only Required Columns

SELECT * retrieves all columns including potentially large TEXT, JSONB, or BYTEA fields. For queries returning many rows, this adds significant data transfer overhead:

-- Slow: retrieves all columns including large bio and settings JSONB fields
SELECT * FROM users WHERE country = 'US' LIMIT 1000;

-- Fast: retrieves only required columns
SELECT id, email, display_name, created_at FROM users
WHERE country = 'US' LIMIT 1000;

This change matters most for wide tables (30+ columns) or tables with large variable-length fields. For tables with a handful of small columns, the difference is negligible.

Performance Optimization Checklist

Check	Query	Action
Slow queries	pg_stat_statements ORDER BY total_exec_time	Analyze with EXPLAIN, add indexes
Sequential scans on large tables	pg_stat_user_tables WHERE seq_scan > idx_scan	Design compound or partial index
Dead tuple bloat	n_dead_tup / n_live_tup > 10%	VACUUM ANALYZE table_name
Unused indexes	pg_stat_user_indexes WHERE idx_scan = 0	Drop after confirming not needed
N+1 from ORM	High call count with parameterized id query	Add eager loading
Connection count	pg_stat_activity	Deploy PgBouncer

Conclusion

Database performance optimization is systematic, not intuitive. The path from observation to resolution follows a consistent sequence: measure query performance with pg_stat_statements, identify specific slow queries, analyze their execution plans with EXPLAIN ANALYZE, design indexes that match the access patterns, and verify improvement with before/after measurements.

The highest-ROI optimization in most underperforming applications is not hardware upgrades or caching layers — it is adding a compound index on the most frequently executed slow query. A single covering index can reduce a 500ms query to under 5ms. This improvement compounds across every request that runs that query.

---

Author: Smart Maple Database Engineering Team Updated: April 2026