---
title: "How the SQLite Virtual Machine Works"
description: "SQLite processes SQL through a parse-optimize-execute pipeline that turns query text into executable virtual-machine instructions. It tokenizes characters, parses them into an AST such as a Select struct, and uses table statistics to choose between a full table scan and index-based access. The source describes sqlite_stat1's coarse index and duplicate-value counts alongside sqlite_stat4's distributed samples, then traces bytecode that opens the persons table, compares favorite_color with \"blue\", and returns matching columns. Registers hold intermediate values, cursors iterate through tables, and sqlite3_step() resumes execution after each returned row; the VM has 186 opcodes as of writing, while bind parameters allow prepared statements to skip repeated parsing and optimization."
---

# How the SQLite Virtual Machine Works

[Fly.io](https://yomu.fyi/company/fly-io) · Sep 7, 2022

**Type:** Explainer

## Summary

SQLite processes SQL through a parse-optimize-execute pipeline that turns query text into executable virtual-machine instructions. It tokenizes characters, parses them into an AST such as a Select struct, and uses table statistics to choose between a full table scan and index-based access. The source describes sqlite\_stat1's coarse index and duplicate-value counts alongside sqlite\_stat4's distributed samples, then traces bytecode that opens the persons table, compares favorite\_color with "blue", and returns matching columns. Registers hold intermediate values, cursors iterate through tables, and sqlite3\_step() resumes execution after each returned row; the VM has 186 opcodes as of writing, while bind parameters allow prepared statements to skip repeated parsing and optimization.

## Context

The material explains how SQLite parses, optimizes, and executes SQL, including how declarative queries become execution plans and how statement reuse can affect application performance.

## Approach / What changed

It uses a sandwich-making-machine analogy to introduce tokenization, AST construction, query planning, and execution, then follows SQLite structures, sqlite\_stat1 and sqlite\_stat4 statistics, index-versus-table-scan decisions, and a bytecode trace using registers, cursors, and VM opcodes.

## Takeaways

- SQLite's parser tokenizes SQL and builds an AST represented by structures such as Select before query planning begins.
- sqlite\_stat1 stores coarse index statistics, while sqlite\_stat4 stores samples distributed across an index to help estimate value uniqueness.
- The VM executes bytecode through registers and cursors; bind parameters let prepared statements reuse compiled work and avoid repeated parsing and optimization.

**Tags:** [Databases](https://yomu.fyi/topic/databases), [Performance](https://yomu.fyi/topic/performance), [SQL](https://yomu.fyi/topic/sql), [SQLite](https://yomu.fyi/topic/sqlite)

- Source: [Fly.io](https://fly.io/blog/sqlite-virtual-machine)
- Source URL: https://fly.io/blog/sqlite-virtual-machine
- Ingested by Yomu: 2026-09-02T11:59:57.043Z

[Read original post](https://fly.io/blog/sqlite-virtual-machine)