Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
This course depends on being able to see what the database did, and by default a database tells you almost nothing. Three of the ten modules need PostgreSQL's pg_stat_statements and auto_explain, both of which must be loaded at server start and neither of which is on by default. MongoDB's profiler is off by default too. The other requirement is stranger: the cache is deliberately small. A machine with 32GB of RAM will hold the entire practice dataset in memory, every query will be fast, and the difference between a good plan and a bad one will vanish into noise — which is exactly the condition under which you learn nothing. Constraining the cache to 256MB against roughly 1.5GB of data reproduces the situation every real production database is in, where the working set does not fit and the plan actually matters.
Every setting, what it exists for, and the verification query that tells you whether it actually took effect — because several of these fail silently if you set them in the wrong place.
-- ── Verify the extensions are actually loaded ───────────────────
-- These must be preloaded at SERVER START. Setting them in a session
-- does nothing, which is the most common way this goes wrong.
SHOW shared_preload_libraries;
-- shared_preload_libraries
-- --------------------------
-- pg_stat_statements,auto_explain
--
-- If this is empty, nothing in module 2 works. Fix it in
-- postgresql.conf (or the compose command flags) and RESTART.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT count(*) FROM pg_stat_statements; -- any number = it works
-- ── The settings that matter, and why each one ──────────────────
SELECT name, setting, unit FROM pg_settings WHERE name IN (
'shared_buffers', -- 256MB: deliberately SMALLER than the data
'effective_cache_size', -- planner's guess at total cache
'work_mem', -- per-node sort/hash memory
'random_page_cost', -- 4.0 assumes a spinning disk
'track_io_timing', -- needed for per-read latency
'auto_explain.log_min_duration'
) ORDER BY name;
-- name | setting | unit
-- -------------------------------+---------+------
-- auto_explain.log_min_duration | 200 | ms
-- effective_cache_size | 524288 | 8kB
-- random_page_cost | 4 |
-- shared_buffers | 32768 | 8kB (= 256MB)
-- track_io_timing | off |
-- work_mem | 4096 | kB
-- Turn on IO timing — several later measurements need it:
ALTER SYSTEM SET track_io_timing = on;
SELECT pg_reload_conf();
-- ── Why shared_buffers is SMALL on purpose ──────────────────────
SELECT pg_size_pretty(pg_database_size(current_database())) AS data,
(SELECT setting::bigint * 8192 FROM pg_settings
WHERE name = 'shared_buffers') / 1048576 || ' MB' AS cache;
-- data | cache
-- ---------+--------
-- 1418 MB | 256 MB
--
-- Roughly 18% of the data fits in cache. That ratio is the point.
-- Give this database 4GB of shared_buffers and every query in the
-- course becomes fast, every plan looks equally good, and you learn
-- nothing. Production databases are almost never fully cached.
-- ── On a managed database you cannot set all of these ───────────
-- Neon, Supabase, RDS and Cloud SQL each restrict a different subset.
-- What is usually available:
-- pg_stat_statements yes on all four (often already on)
-- auto_explain RDS yes; Neon/Supabase usually not
-- shared_buffers no — it is sized for your instance
-- track_io_timing usually yes
--
-- If you cannot set shared_buffers, use SCALE=large from task 5
-- instead. Making the data bigger has the same effect as making the
-- cache smaller: the working set stops fitting.