Cover

CMU Database Group · 1:25:57 · Watch on YouTube

Summary

The final lecture of CMU 15-445/645 (Fall 2024) opens with course logistics and a rapid review of every exam topic since the midterm, then spends the bulk of its time on how modern analytical (OLAP) database systems actually get their speed. Andy Pavlo first establishes the two foundational techniques — writing branchless code that beats the CPU's branch predictor, using SIMD to process many values per instruction, and specializing code either by compiling each query to machine code or by stitching together pre-compiled primitives — and then walks through six real systems (BigQuery, Snowflake, Redshift, Yellowbrick, Databricks/Photon, and DuckDB) showing which combination of those techniques each one picked and why. The lecture closes with a live demo of TabDB, a SQLite-in-the-browser database that stores its rows in browser tab titles, and the practical advice to reach for Postgres and then DuckDB long before any cloud data warehouse.

Topic breakdown

  1. Course Logistics and Final Exam — deadlines, exam mechanics, what to bring (0:30)
  2. Final Exam Topic Review — optimization, transactions, recovery, distributed DBs (7:24)
  3. Branchless Code and SIMD — why the "wasteful" scan wins on modern CPUs (15:48)
  4. Query Compilation vs Pre-Compiled Primitives — the two code-specialization camps (27:22)
  5. Google BigQuery (Dremel) — the shuffle service and its dynamic optimizations (32:45)
  6. Snowflake — built from scratch, adaptive aggregation, borrowed idle nodes (39:27)
  7. Amazon Redshift — the fleet-wide compilation cache and hardware pushdown (46:05)
  8. Yellowbrick — custom drivers, kernel bypass, no syscalls after boot (54:26)
  9. Databricks and Photon — escaping the JVM, horizontal expression fusion (1:02:02)
  10. DuckDB — single-node, embedded, and the "most data isn't big" argument (1:09:06)
  11. TabDB (Live Demo) — SQLite storing rows in browser tab titles (1:15:43)
  12. Closing Advice and Q&A — Postgres first, backups, managed services, MotherDuck (1:18:55)

How the systems compare

flowchart TD
  F["Foundations:<br/>branchless code + SIMD<br/>+ code specialization"]
  F --> C1["Holistic compilation<br/>(codegen the whole query)"]
  F --> C2["Pre-compiled primitives<br/>(stitch kernels at runtime)"]

  C1 --> RS["Redshift<br/>+ fleet-wide compilation cache<br/>+ Nitro predicate pushdown"]
  C1 --> YB["Yellowbrick<br/>custom drivers, kernel bypass"]
  C2 --> SF["Snowflake<br/>push-based, flexible compute"]
  C2 --> DB["Databricks Photon<br/>pull-based, horizontal fusion"]
  C2 --> DD["DuckDB<br/>single-node, embedded"]
  F --> BQ["BigQuery<br/>vectorized + explicit shuffle service"]

  RS -.-> Both["Redshift uses BOTH camps"]

Key takeaways

  • A branchless selection scan performs identically at every predicate selectivity, while the branching version peaks in cost near 50% selectivity where branch prediction fails most — doing more work is faster on a modern CPU (20:43, 21:12).
  • SIMD collapses the same work into far fewer instructions: adding two 8-element 32-bit arrays goes from 8 scalar adds to 2 SIMD instructions with 128-bit registers; most engines target AVX2/AVX-256 with hand-written intrinsics (23:37, 27:22).
  • Compiling a query with GCC costs roughly 500 ms to 1 second, which is why pre-compiled primitives exist — the space of query/type patterns is theoretically infinite but practically small (29:51, 30:48).
  • An Amazon paper reports ~96% of Redshift queries are literally identical (same SQL, same parameters), and Redshift's compilation cache hits 99.95% locally with ~87–89% of the remainder served by a cache shared across all customers — safe because a compiled plan carries no customer data (49:10, 50:26, 51:12, 51:34).
  • Amazon acquired the ParAccel source license for $20 million and turned it into Redshift, a billion-dollar-a-year product (46:26, 46:52).
  • Yellowbrick's extreme approach — never calling a syscall after boot, custom UDP/NVMe/S3 drivers, kernel bypass, an allocator claimed 100× faster than malloc — produces results that are better but not 10× better, and the explicit verdict is that a new database company should not start there (57:04, 59:36, 1:01:37).
  • Databricks' first C++ Photon prototype was slower than the Scala codegen engine it replaced, but won on hiring pool and iteration speed — engineers who know C++ and databases vastly outnumber those who know JVM internals (1:06:08, 1:06:52).
  • Analysis of published Redshift and Snowflake query data found most queries touch under ~1 GB, which is the whole basis for DuckDB's single-node bet — and the lecturer's advice is Postgres first, then DuckDB, good enough for nine out of ten people (1:11:09, 1:11:38, 1:19:57).