Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
Performance work without a before is storytelling. The single most common failure in this kind of project is that someone adds an index, observes that the query now takes forty milliseconds, and reports a speedup — without ever having recorded what it took beforehand under comparable conditions. The fix is to capture the baseline deliberately, once, while the database is in its known starting state with no indexes and fresh statistics. That snapshot is what makes every later claim checkable, and it is worth more than any single optimisation because it converts the rest of the course from a sequence of tips into a sequence of measurements. Capturing it correctly means warming the cache first, running each query several times, and recording a percentile rather than one number.
A baseline script that warms, repeats and records both timing and plan for the five queries the course returns to, written to a file you will diff against later.
# baseline.sh — run ONCE, now, before you create a single index.
#
# $ set -a && source .env && set +a
# $ ./baseline.sh > baseline-$(date +%Y%m%d).txt
set -euo pipefail
echo "# baseline $(date -u +%FT%TZ) SCALE=$SCALE"
echo
timed () {
local t0 t1
t0=$(date +%s%N)
psql "$PG_URL" -qtAc "$1" > /dev/null
t1=$(date +%s%N)
awk -v a="$t0" -v b="$t1" 'BEGIN { printf "%.3f", (b-a)/1000000000 }'
}
run_pg () {
local name="$1" sql="$2"
# Warm: three throwaway runs so we measure a warm cache, not a cold
# one. Module 2 task 7 explains why this matters more than anything
# else in benchmarking.
for _ in 1 2 3; do psql "$PG_URL" -qtAc "$sql" > /dev/null; done
# Measure: five runs, keep the median.
local t1 t2 t3 t4 t5
t1=$(timed "$sql"); t2=$(timed "$sql"); t3=$(timed "$sql")
t4=$(timed "$sql"); t5=$(timed "$sql")
printf '%s\n' "$t1" "$t2" "$t3" "$t4" "$t5" | sort -n | awk -v n="$name" \
'NR==3 {printf " %-28s median %ss\n", n, $1}'
echo " plan:"
psql "$PG_URL" -qXc "EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) $sql" \
| sed 's/^/ /'
echo
}
echo "## postgres"
run_pg "orders by user+status" \
"SELECT * FROM orders WHERE user_id = 8812 AND status = 'refunded'"
run_pg "orders by status" \
"SELECT count(*) FROM orders WHERE status = 'paid'"
run_pg "user order history" \
"SELECT * FROM orders WHERE user_id = 8812 ORDER BY created_at DESC LIMIT 20"
run_pg "orders joined to users" \
"SELECT count(*) FROM orders o JOIN users u ON u.id = o.user_id WHERE u.plan = 'enterprise'"
run_pg "events last 30 days" \
"SELECT count(*) FROM events WHERE created_at > now() - interval '30 days'"
echo "## mongodb"
mongosh "$MONGO_URL" --quiet baseline.js