Databases

Database Performance Optimization Complete Guide

Master database performance optimisation with PostgreSQL, MongoDB, and Redis. Learn advanced indexing, query optimisation, caching strategies, and production tuning techniques.

What you'll learn

Identify performance bottlenecks with profiling tools
Design effective indexing strategies across PostgreSQL and MongoDB
Optimise N+1 query problems
Implement multi-layer caching with Redis
Use connection pooling effectively
Monitor database health with Prometheus and Grafana

Profiling & Bottleneck Detection

SQL

1-- PostgreSQL: enable pg_stat_statements
2CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3
4-- Top 10 slowest queries
5SELECT left(query, 100) AS query_snippet,
6       calls, mean_exec_time::numeric(10,2) AS mean_ms,
7       total_exec_time::numeric(10,2) AS total_ms
8FROM pg_stat_statements
9ORDER BY mean_exec_time DESC LIMIT 10;
10
11-- MongoDB: explain a slow query
12db.courses.find({ status: "published" }).sort({ createdAt: -1 }).explain("executionStats")
13
14-- MongoDB: enable profiling for queries > 100ms
15db.setProfilingLevel(1, { slowms: 100 })
16db.system.profile.find().sort({ ts: -1 }).limit(5)

Advanced Indexing

SQL

1-- PostgreSQL: covering index (index-only scan)
2CREATE INDEX idx_orders_covering
3  ON orders (user_id, status)
4  INCLUDE (total, created_at);
5
6-- PostgreSQL: expression index
7CREATE INDEX idx_users_lower_email
8  ON users (lower(email));
9
10-- MongoDB: compound index following ESR rule (Equality, Sort, Range)
11db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })
12
13-- MongoDB: sparse index (only index docs where field exists)
14db.users.createIndex({ githubId: 1 }, { sparse: true })
15
16-- MongoDB: TTL index (auto-expire documents)
17db.sessions.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 })

Query Patterns

SQL

1-- Fix N+1: use JOIN instead of per-row queries
2-- BAD (N+1)
3SELECT * FROM orders;   -- then for each order:
4SELECT * FROM users WHERE id = ?;
5
6-- GOOD: single query
7SELECT o.*, u.name, u.email
8FROM orders o
9JOIN users u ON u.id = o.user_id
10WHERE o.status = 'pending';
11
12-- Keyset pagination (vs OFFSET — much faster on large tables)
13-- OFFSET-based (slow at high page numbers):
14SELECT * FROM courses ORDER BY id LIMIT 20 OFFSET 10000;
15
16-- Keyset (fast regardless of depth):
17SELECT * FROM courses WHERE id > $last_id ORDER BY id LIMIT 20;

Caching Architecture

Bash

1# Redis caching layers:
2# L1 — application in-memory (node-cache, LRU)
3# L2 — Redis (shared across instances)
4# L3 — Database
5
6# Write-through vs cache-aside
7# Cache-aside: app checks cache first, loads from DB on miss
8# Write-through: every write goes to cache AND DB simultaneously
9
10# Redis data structures for common patterns:
11redis.setex('user:123', 300, JSON.stringify(user))         # simple KV
12redis.zadd('leaderboard', score, userId)                    # sorted set
13redis.lpush('activity:feed', JSON.stringify(event))         # list
14redis.hset('session:abc', 'userId', '123', 'role', 'admin') # hash

Scaling Strategies

Vertical scaling — add RAM/CPU first; cheapest for most workloads.
Read replicas — route SELECT queries to replicas; write queries to primary.
Partitioning — partition large tables by time range (e.g. monthly partitions for events).
Sharding — distribute data across multiple databases by a shard key; use only when other strategies are exhausted.
CQRS — separate write (command) and read (query) models with different data stores optimised for each.

Monitoring

Bash

1# Install postgres_exporter for Prometheus
2docker run -d --name postgres_exporter   -e DATA_SOURCE_NAME="postgresql://postgres:pass@localhost:5432/postgres?sslmode=disable"   -p 9187:9187 quay.io/prometheuscommunity/postgres-exporter
3
4# Key Grafana dashboard: ID 9628 (PostgreSQL Database)
5# Alert thresholds:
6# - Connection usage > 80% of max_connections
7# - Replication lag > 30 seconds
8# - Cache hit ratio < 95%
9# - Dead tuple ratio > 10%