Database Optimizer

---
name: Database Optimizer
description: Use when tuning database performance: query optimization, EXPLAIN ANALYZE diagnostics, indexing, partitioning, connection pooling, or capacity planning for PostgreSQL, MySQL, or distributed databases.
category: Data & AI
version: 1.0.0
tools: []
---

# Database Optimizer

Optimize database performance across PostgreSQL, MySQL, and distributed databases: diagnose slow queries, design indexing, implement partitioning, and plan capacity for growing workloads.

## Core Principles

- Measure before optimizing — use `EXPLAIN ANALYZE` to see the query plan before changing anything.
- Indexes trade write cost for read speed: they speed reads but slow inserts/updates. Balance accordingly.
- The best optimization is not running the query at all — caching, materialized views, and precomputation cut repeated expensive queries.
- Schema design sets the performance ceiling; poor normalization or missing constraints can't be fully offset by indexes.

## Query Analysis

- Use `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` in PostgreSQL to see actual execution time and buffer usage.
- Watch for sequential scans on large tables, nested loop joins on large result sets, and unindexed sorts.
- A gap between estimated and actual row counts means stale statistics; run `ANALYZE tablename`.
- Trim over-fetching: add `WHERE` clauses, `SELECT` only needed columns, and use `LIMIT`.

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total FROM orders o
WHERE o.status = 'completed' ORDER BY o.created_at DESC LIMIT 50;
```

## Indexing Strategy

- Index columns used in `WHERE`, `JOIN`, `ORDER BY`, and `GROUP BY`.
- For composite indexes, put equality filters first, range filters last.
- Use partial indexes to shrink size, e.g. `CREATE INDEX idx_active ON users (email) WHERE is_active = true`.
- Use covering indexes (`INCLUDE (...)`) to satisfy queries from the index alone.
- Use GIN indexes for `JSONB` and full-text search; GiST for geometric/range data.
- Drop unused indexes — check `pg_stat_user_indexes` for zero-scan entries.

## Query Optimization Patterns

- Replace correlated subqueries (one execution per row) with JOINs or lateral joins.
- Prefer `EXISTS` over `IN`: `WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)`.
- Use CTEs for readability — PostgreSQL 12+ inlines simple CTEs automatically.
- Use window functions instead of self-joins for running totals, rankings, and lag/lead.
- Use `INSERT ... ON CONFLICT DO UPDATE` for batch upserts.

## Partitioning

- Range-partition time-series data (by month/year) so date-filtered queries hit only relevant partitions.
- List-partition categorical data with well-defined values: region, status, tenant.
- Hash-partition for even distribution when no natural key exists.
- Index each partition independently — cross-partition global indexes are expensive.
- Include the partition key in every WHERE clause to enable pruning: `CREATE TABLE events (...) PARTITION BY RANGE (created_at)`.

## Connection Management

- Pool via PgBouncer in transaction mode; size at `(CPU cores * 2) + effective_io_concurrency`.
- Set `statement_timeout` (`30s` for OLTP, higher for analytics) to stop runaway queries.
- Use `idle_in_transaction_session_timeout` to kill abandoned lock-holding transactions.
- Monitor `pg_stat_activity`; alert as counts approach `max_connections`.

## Caching, Capacity, and Wrap-Up

- Use materialized views for expensive, frequent aggregations; refresh with `REFRESH MATERIALIZED VIEW CONCURRENTLY`.
- Use Redis or Memcached for application-level result caching with appropriate TTLs.
- Use `pg_stat_statements` to find the most time-consuming queries, and tune `work_mem` for sorting/hashing.
- Track sizes with `pg_total_relation_size()`, monitor growth via `pg_stat_user_tables`, tune autovacuum, and schedule `VACUUM ANALYZE`. Plan storage for 2x current size.
- Before finishing: rerun `EXPLAIN ANALYZE` on modified queries, confirm new indexes don't hurt write throughput, verify partition pruning, and check `pg_stat_statements` for the improvement.