Why SQLite Uses Bytecode

Original Article ↗ Hacker News Discussion ↗

SQLite’s choice to execute SQL queries via a custom bytecode virtual machine, rather than walking an abstract syntax tree like PostgreSQL or MySQL, sparks broad comparisons of performance, memory use, explainability, and portability in database engines. Commenters highlight that high‑level bytecode can be more cache‑friendly, easier to introspect (e.g., SQLite’s detailed EXPLAIN output), and well suited to prepared statements and incremental execution, while tree‑based plans can expose more parallelism and are common in larger client‑server systems. The conversation widens to virtual machines in other domains—languages, games, operating systems, and fonts—framing bytecode as a powerful but often underappreciated abstraction layer.

Bytecode vs Tree-Based Execution in Databases

  • SQLite compiles SQL to high-level register-based bytecode instead of walking an AST / tree of iterator objects.
  • Other systems (PostgreSQL, MySQL, SQL Server, etc.) generally use tree-of-objects or Volcano-style iterator trees; some provide textual, XML, or JSON explain outputs that reflect this.
  • Several commenters argue bytecode is more cache-friendly, compact, and easier to execute incrementally than pointer-heavy trees.
  • Others note that for query plans (joins, scans), register vs iterator trees may perform similarly; bytecode shines more for evaluating scalar expressions and type-heavy logic.

Performance, JIT, and Optimization

  • SQLite’s profiling reportedly shows bytecode dispatch is a small fraction of runtime; most time is in B‑trees, comparisons, and record decoding, so JITing bytecode to native code might only yield a few percent at best.
  • Some databases (e.g., PostgreSQL) have JIT compilation via LLVM, but users report mixed or negative real-world gains due to compile overhead and cost estimation issues.
  • There’s discussion of template / copy‑and‑patch JITs and vectorized, precompiled operator blocks used by other engines; applicability to SQLite is considered limited given its “lite”, highly portable goals.

Explain Plans and Representation

  • SQLite’s bytecode makes it easy to represent a plan as a dense table; commenters compare this to tree-based systems that output more complex textual or graphical plans.
  • Some find SQLite’s EXPLAIN too low-level and want an intermediate level showing cardinalities, index choices, and optimizer rationale.
  • Others show examples of tools that visualize tree plans (Postgres, SAP HANA, Dolt) and argue trees are fine when you have GUIs, less so in terminals.

AST-Free Parsing and Compilation

  • Multiple replies explain how parsers can emit bytecode directly via syntax-directed translation without ever materializing an AST, using parser actions and patching of forward jumps.
  • This saves an extra traversal and memory but makes rewrites/optimizations harder; trees are favored when you need many transformations, bytecode when you mostly execute.

VMs and Bytecode in Other Contexts

  • Commenters list many non-language uses of VMs: games (classic adventure titles), office apps (Word/Multiplan p‑code), OS and CPU microcode, database systems like MonetDB, regex engines, fonts (TeX/TrueType), eBPF, DWARF, RAR, and more.
  • Consensus: bytecode VMs are a broadly useful abstraction layer, often underrated outside of full programming languages.

APIs, Extensibility, and Alternative Frontends

  • SQLite’s VM is intentionally not a stable public API; opcodes change between releases. Some still speculate about generating bytecode directly or building alternative DSLs that compile to it.
  • Others stress that exposing the VM would freeze internals and complicate evolution, and that prepared statements already give persistent, reusable compiled form.