Why SQLite Uses Bytecode

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.