database-optimization

Database Optimization

EXPLAIN Analysis

Always run EXPLAIN ANALYZE before optimizing. Read the output bottom-up.

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;

Key metrics to watch:

• Seq Scan on large tables = missing index
• Nested Loop with high row count = consider hash/merge join
• Sort without index = add index on sort column
• Rows estimated vs actual divergence = stale statistics, run ANALYZE

Index Strategies

B-tree (default, most cases)

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

Use for: equality, range queries, sorting. Column order matters in composite indexes: put equality columns first, then range/sort columns.

Partial Index (PostgreSQL)

CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

Use when queries always filter on a specific condition. Dramatically smaller than full indexes.

GIN (PostgreSQL - arrays, JSONB, full-text)

CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_docs_search ON documents USING GIN (to_tsvector('english', content));

GiST (PostgreSQL - spatial, range types)

CREATE INDEX idx_locations_point ON locations USING GiST (coordinates);
CREATE INDEX idx_events_period ON events USING GiST (tsrange(start_at, end_at));

Covering Index (index-only scans)

-- PostgreSQL
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name);

-- MySQL
CREATE INDEX idx_users_email_name ON users (email, name);

N+1 Query Detection

Symptom: 1 query to fetch parent + N queries for each child.

# BAD: N+1
users = db.query(User).all()
for user in users:
    print(user.orders)  # triggers query per user

# GOOD: eager load
users = db.query(User).options(joinedload(User.orders)).all()

// BAD: N+1
const users = await User.findAll();
for (const user of users) {
  const orders = await Order.findAll({ where: { userId: user.id } });
}

// GOOD: batch load
const users = await User.findAll({ include: [Order] });