Capstok — learn by doing

Why this matters

You cannot learn query performance on a thousand rows. With a small table every plan is fast, the planner picks a sequential scan for everything because a sequential scan genuinely is the right answer, and every change you make looks like noise. The threshold where this stops being true is roughly the point where the table no longer fits comfortably in memory and where an index actually changes the number of pages read — for the shapes in this course, a few million rows. So the first thing to build is a dataset big enough to hurt, small enough to regenerate in a couple of minutes, and identical in both engines so that every comparison later is a comparison of engines rather than of data. Skew matters too: real data is not uniform, and a uniformly random dataset hides exactly the estimation failures module 7 is about. This generator gives some customers thousands of orders and most of them one.

Demo

One compose file brings up Postgres 16 and MongoDB 7. One generator script fills both with the same users, orders and events. Run it once now; every task from here on assumes it exists.

Try it yourself

Bring the stack up and run both seed scripts. Confirm the row counts with SELECT count(*) FROM orders and db.orders.countDocuments() — both should be 2,000,000.
Check the skew is real: SELECT user_id, count(*) c FROM orders GROUP BY 1 ORDER BY c DESC LIMIT 5, and the same with db.orders.aggregate on userId. The top user should have hundreds of orders, not ten.
Measure the on-disk size of each: SELECT pg_size_pretty(pg_total_relation_size('orders')) and db.orders.stats().storageSize. Note that MongoDB's is smaller and remember why when module 8 covers compression.
Confirm no indexes exist beyond the primary keys: \d orders in psql, db.orders.getIndexes() in mongosh. Every index in this course gets added by you, on purpose.
Run SELECT count(*) FROM orders WHERE status = 'refunded' twice and time both. The second run is faster; that gap is cache, and it is the reason task 7 exists.

References

# docker-compose.yml — both engines, side by side, no cloud account.
#
# Memory settings are deliberately small. You want the working set to
# NOT fit entirely in cache, because "it was cached" is the single most
# common reason a benchmark lies to you.

services:
  pg:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: qp
      POSTGRES_DB: qp
    ports: ["5432:5432"]
    command:
      - postgres
      # Enable the extension that records every query's total time.
      - -c
      - shared_preload_libraries=pg_stat_statements,auto_explain
      # 256MB buffer pool against ~1.5GB of data: cache misses are real.
      - -c
      - shared_buffers=256MB
      # Log the plan of anything over 200ms. Module 1 task 5 explains.
      - -c
      - auto_explain.log_min_duration=200ms
      - -c
      - auto_explain.log_analyze=on
      - -c
      - auto_explain.log_buffers=on
      # Track every statement, not just the top level.
      - -c
      - pg_stat_statements.track=all

  mongo:
    image: mongo:7
    ports: ["27017:27017"]
    command:
      - mongod
      # 256MB WiredTiger cache, same reasoning as shared_buffers above.
      - --wiredTigerCacheSizeGB
      - "0.25"
      # Log any operation slower than 200ms.
      - --slowms
      - "200"
      # Profile level 1 = slow ops only. Task 4 covers the trade-off.
      - --profile
      - "1"

Build the Dataset That Makes Slowness Visible

Why this matters

Demo

Try it yourself

References