Performance Without Magic:
How Open Source Databases Scale

1. The Performance Stack
2. Indexing Done Right
3. Memory Management
4. I/O: The Real Bottleneck
5. OS / Kernel Tuning

6. Connection Management
7. Busting the Myths
8. Reading Query Plans
9. When to Buy Hardware
10. The Decision Framework

Without index (Seq Scan)

-- Reads EVERY row
SELECT * FROM orders
WHERE customer_id = 42;

-- Cost: O(n)  →  table grows = pain

With index

CREATE INDEX idx_orders_customer
  ON orders (customer_id);

-- Cost: O(log n)  →  scales cleanly

Composite index order matters

-- Query filters status first, then date
-- GOOD:
CREATE INDEX ON events (status, created_at);

-- BAD for this query:
CREATE INDEX ON events (created_at, status);

PG Partial index

-- Index ONLY unprocessed rows
CREATE INDEX ON jobs (created_at)
  WHERE status = 'pending';

PG Covering index (Index-Only Scan)

CREATE INDEX ON orders (customer_id)
  INCLUDE (total, status);
-- No heap access needed!

MY Covering index (same idea)

CREATE INDEX idx_cover
  ON orders (customer_id, total, status);
-- InnoDB: leaf stores all values

Virtual Memory

# Don't swap under databases!
vm.swappiness = 1          # (not 0, keep swapper alive)

# Dirty page write-back
vm.dirty_ratio = 15        # max dirty pages before blocking writes
vm.dirty_background_ratio = 3   # background flush threshold

# Huge pages (PostgreSQL)
vm.nr_hugepages = <size/2MB>
# saves TLB pressure on large shared_buffers

Network & File Descriptors

# Max open files
fs.file-max = 2097152
# For the DB process ulimit:
# nofile = 65535

# TCP tuning (high-concurrency)
net.core.somaxconn = 65535
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_fin_timeout = 15

# NUMA: pin buffer pool to NUMA node
numactl --cpunodebind=0 --membind=0 \
  mysqld

Disable THP

# Check
cat /sys/kernel/mm/transparent_hugepage/enabled
# Disable (add to rc.local / systemd unit)
echo never > \
  /sys/kernel/mm/transparent_hugepage/enabled
echo never > \
  /sys/kernel/mm/transparent_hugepage/defrag

I/O Scheduler

# Check current scheduler
cat /sys/block/nvme0n1/queue/scheduler
# [none] mq-deadline kyber bfq

# NVMe/SSD: use 'none' or 'mq-deadline'
echo mq-deadline > \
  /sys/block/nvme0n1/queue/scheduler

# HDD: bfq or mq-deadline

PG EXPLAIN ANALYZE BUFFERS

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 42;

-- Key things to look for:
-- "Seq Scan"   → missing index?
-- "Rows=10000 vs actual rows=3"
--    → stale statistics, run ANALYZE
-- "Buffers: shared hit=0 read=8000"
--    → cache miss → I/O bound
-- "Sort Method: external merge  Disk"
--    → raise work_mem

MY EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT * FROM orders
WHERE customer_id = 42;

-- Look for:
-- "access_type": "ALL"  → Seq Scan
-- "rows": 500000        → bad estimate
-- "Using filesort"      → no useful index for ORDER BY
-- "Using temporary"     → temp table → slow

-- Enable slow query log!
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;

1. Measure before you tune. Use EXPLAIN ANALYZE, pg_stat_statements, slow query log.
2. Fix the query first. A missing index can be 1000× faster than any config change.
3. Right-size memory. Buffer pool hit rate should be ≥ 95%.
4. Connections are threads / processes. Use a connection pooler.
5. Storage is often the real bottleneck. NVMe for OLTP, always.

6. OS tuning is not optional. THP, swappiness, I/O scheduler matter.
7. Maintenance keeps performance. VACUUM, ANALYZE, statistics.
8. Hardware buys headroom,
   not fixes. Buy it when software is maxed out.
9. The stack matters. Tune the right layer, in the right order.