How This Course Works + Lab Setup

Stop guessing. Start measuring.

You've probably added an index because "indexes make queries faster," or rewrote a subquery as a JOIN because someone said that's faster, and then … you weren't sure. Maybe it was a bit quicker? Maybe the same? This course is built around a different approach: you measure before you change anything, and you measure after. Everything else is a hypothesis.

The good news is that PostgreSQL and MySQL both ship with world-class instrumentation built right in. EXPLAIN ANALYZE gives you actual row counts, actual timing per operator, and actual I/O statistics. pgbench and sysbench drive repeatable load so you can benchmark, not just time. Together these tools make the scientific method practical on a laptop.

The two sample datasets

All labs use one of two datasets, chosen for what they stress-test. You'll meet them in Lesson 2 and use them throughout the course.

Dataset A — Small OLTP schema (shopdb)

A normalized e-commerce database: customers, orders, order_items, and products. It's small enough that you can reason about every row, which makes it perfect for checking correctness — confirming that a plan change returns the same results, understanding how MVCC snapshot isolation affects concurrent reads, or tracing what VACUUM actually cleaned up.

Dataset B — Large, skewed dataset (eventsdb)

One hundred million event rows, with a deliberately skewed distribution on the user_id and event_type columns. A handful of users generate 40% of the events; most generate none. This skew is intentional — it's the kind of real-world distribution that breaks the optimizer's independence assumption and produces wildly wrong cardinality estimates. You'll use this dataset whenever you need to benchmark something at scale or deliberately mislead the query planner.

The standard optimization workflow

Every optimization you do in this course — and every one you'll do in production — follows the same cycle. Here it is visually:

The most violated rule in this diagram is step 3: "change one thing." When you change two things at once and performance improves, you don't know which change helped. Worse, one change may have helped and the other hurt — and you've lost both signals. Discipline here pays for itself every time.

Performance-measurement tools

EXPLAIN — the estimated plan

EXPLAIN shows you what the optimizer plans to do, without executing the query. It's fast and safe on any table size. Use it when you want to see whether the planner is choosing the right index or the right join strategy — before you commit to running a long query.

EXPLAIN ANALYZE — the actual plan

EXPLAIN ANALYZE runs the query and annotates the plan with actual row counts and timing per operator. The gap between "rows estimated" and "rows actual" is often the most important number on the page — a large discrepancy tells you that statistics are stale or the distribution is skewed in a way the optimizer didn't account for. In PostgreSQL you'll add BUFFERS to also see page reads and cache hits.

-- PostgreSQL
-- Estimated plan only (safe on any table size)
EXPLAIN
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
  AND o.created_at >= '2024-01-01';

-- Actual plan with timing and I/O statistics
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
  AND o.created_at >= '2024-01-01';

-- MySQL 8
-- Estimated plan (classic tabular format)
EXPLAIN
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
  AND o.created_at >= '2024-01-01';

-- Actual plan with timing (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
  AND o.created_at >= '2024-01-01';

pgbench — repeatable load for PostgreSQL

pgbench is bundled with every PostgreSQL installation. It drives a TPC-B-like workload (or any SQL you write) at a specified number of clients and transactions, then reports transactions per second (TPS) and latency percentiles. You'll use it whenever you need to compare two configurations under sustained load rather than a single-query timer.

# pgbench — initialize and run
# Initialize the built-in benchmark schema at scale factor 10 (~1.5 million rows)
pgbench -i -s 10 shopdb

# Run for 30 seconds with 4 clients, 2 threads — report TPS and latency
pgbench -c 4 -j 2 -T 30 -P 5 shopdb

# Run a custom script instead of the built-in workload
pgbench -c 4 -j 2 -T 30 -f my_query.sql shopdb

sysbench — repeatable load for MySQL (and PostgreSQL)

sysbench is the standard benchmarking tool for InnoDB. It ships with a rich set of OLTP scripts — point lookups, range scans, read/write mixes — and can be pointed at PostgreSQL too. You'll use it in the MySQL-specific labs to run a controlled workload and measure the impact of buffer-pool sizing, index changes, and schema design choices.

# sysbench — initialize and run (MySQL)
# Prepare: create the sysbench tables (1 million rows)
sysbench oltp_read_write \
  --db-driver=mysql \
  --mysql-host=127.0.0.1 \
  --mysql-user=root \
  --mysql-password=secret \
  --mysql-db=eventsdb \
  --tables=4 \
  --table-size=1000000 \
  prepare

# Run: 60-second mixed read/write with 8 threads
sysbench oltp_read_write \
  --db-driver=mysql \
  --mysql-host=127.0.0.1 \
  --mysql-user=root \
  --mysql-password=secret \
  --mysql-db=eventsdb \
  --tables=4 \
  --table-size=1000000 \
  --threads=8 \
  --time=60 \
  run

Reading execution plans — the absolute basics

You'll spend a full section (Section 6) becoming fluent in plan reading. For now, you need just enough to get through the early labs. Here are the three numbers that matter most on every plan node.

1. Estimated rows vs actual rows

Every plan node shows rows=N (estimated) and, after ANALYZE, actual rows=M. When N and M differ by more than 10×, the optimizer was working with bad information — stale statistics, correlated columns, or a skewed distribution. That bad estimate ripples up the tree and can cause the optimizer to choose the wrong join algorithm or the wrong index.

2. Cost

PostgreSQL shows costs as cost=startup..total in abstract units (roughly 8 KB page reads). The optimizer picks the plan with the lowest total cost. The absolute number isn't useful on its own — comparing two plans' costs for the same query is what matters.

3. Actual time

After ANALYZE, each node shows actual time=first_row..last_row ms. The most expensive node (highest time) is your starting point. Everything else is secondary.

In PostgreSQL, plan nodes are nested and executed from the innermost outward. The bottom node runs first, feeds its output upward, and the topmost node returns your result set. In MySQL's tabular output, rows are listed in roughly the order the optimizer processes them — the row with id=1 drives the join; higher IDs are inner tables.

Lab setup: Docker (recommended)

The fastest path to a working lab is Docker. The commands below give you both databases running on their default ports in under five minutes, with data directories mounted on the host so they survive container restarts.

# PostgreSQL 16 — Docker
# Pull and start PostgreSQL 16
docker run -d \
  --name pg16 \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=shopdb \
  -p 5432:5432 \
  -v pg16_data:/var/lib/postgresql/data \
  postgres:16

# Verify it's running
docker exec -it pg16 psql -U postgres -c "SELECT version();"

# Connect from the host (requires psql installed locally)
psql -h 127.0.0.1 -U postgres -d shopdb

# MySQL 8 — Docker
# Pull and start MySQL 8
docker run -d \
  --name mysql8 \
  -e MYSQL_ROOT_PASSWORD=secret \
  -e MYSQL_DATABASE=shopdb \
  -p 3306:3306 \
  -v mysql8_data:/var/lib/mysql \
  mysql:8

# Verify it's running
docker exec -it mysql8 mysql -uroot -psecret \
  -e "SELECT version();"

# Connect from the host (requires mysql client locally)
mysql -h 127.0.0.1 -uroot -psecret shopdb

Native installation (alternative)

If you prefer a native install: PostgreSQL packages are at postgresql.org/download and MySQL at dev.mysql.com/downloads. Both offer installers for macOS, Windows, and all major Linux distributions. Accept the defaults — you can tune configuration later in Section 3 and Section 14.

Loading the sample datasets

The course repository includes two loading scripts: load-shopdb.sql (small OLTP) and load-eventsdb.sql (large, skewed). Both are idempotent — running them twice won't break anything. After loading, run the verification queries below to confirm the row counts are correct.

# PostgreSQL — load & verify
# Load both datasets
psql -h 127.0.0.1 -U postgres -d shopdb \
  -f load-shopdb.sql

psql -h 127.0.0.1 -U postgres -d eventsdb \
  -f load-eventsdb.sql

# MySQL — load & verify
# Load both datasets
mysql -h 127.0.0.1 -uroot -psecret shopdb \
  < load-shopdb.sql

mysql -h 127.0.0.1 -uroot -psecret eventsdb \
  < load-eventsdb.sql

-- Verify row counts (both engines)
-- shopdb: small OLTP
SELECT 'customers'  AS tbl, COUNT(*) FROM customers
UNION ALL
SELECT 'orders',          COUNT(*) FROM orders
UNION ALL
SELECT 'order_items',     COUNT(*) FROM order_items
UNION ALL
SELECT 'products',        COUNT(*) FROM products;

-- eventsdb: large, skewed
SELECT COUNT(*) FROM events;   -- expect ~100 000 000

Your first EXPLAIN ANALYZE — both engines side by side

Once the datasets are loaded, run your first annotated plan on both engines. Notice what's the same (node types, the concept of estimated vs actual rows) and what's different (output format, I/O statistics, default join strategy). You'll spend Section 6 decoding every line — for now, just observe.

-- PostgreSQL 16
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name,
       COUNT(o.id)        AS order_count,
       SUM(o.total)       AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01'
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 10;

-- MySQL 8
EXPLAIN ANALYZE
SELECT c.name,
       COUNT(o.id)        AS order_count,
       SUM(o.total)       AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01'
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 10;

Copy the output of each into a text file — that's your baseline. Every optimization you make in this course will be compared against a baseline exactly like this one.

How the rest of the course is structured

Every lesson follows the same shape you're experiencing right now: a hook, a conceptual explanation, at least one dual-engine SQL example, a diagram or interactive widget, and a lab that ends with a number you record. The labs are cumulative — the schema you set up today is the one you'll be tuning in Sections 4, 6, 7, and beyond.

The course is organized into nine parts, following the exact path a query travels through the database engine. You start with storage bytes (Part II), climb through indexing and query execution (Parts III–IV), tackle joins and advanced optimization (Part V), descend into transactions and durability (Part VI), scale up (Part VII), and finish with engine-specific tuning and a capstone project (Parts VIII–IX). The bonus covers modern engines — LSM-trees, columnar stores, and cloud-native architectures — for those who want to go further.

You don't need to read sections in strict order, but the arc is deliberate. Concepts build on each other: understanding buffer pools (Section 3) makes index efficiency (Section 4) intuitive; understanding cardinality estimation (Section 7) makes the execution-plan labs (Section 6) much more productive.