Inside a database engine: the life of a query

Between the moment a program sends a query and the moment the first row comes back, a database engine works through the same stages every time. It parses the SQL text into a tree and checks every table and column name against its catalog. It rewrites the tree, replacing views with the tables they read. It plans the query: there are many ways to compute the same result, so it estimates the cost of the candidates and keeps the cheapest. Then it executes the plan. The executor never reads the disk itself: it asks the access methods for rows of tables and indexes, they ask the buffer manager for pages, and only a page that is not already in memory is read from storage. Meanwhile the transaction, lock and log managers keep concurrent work correct and durable.

This lesson follows one query through SQLite, which runs on this page, and through PostgreSQL, from its manual. The two engines are built very differently, and the differences explain a good deal of how each behaves.

The life of a query, stage by stage

Each stage has a different name in each engine’s own documentation, so it helps to have the map in one place:

Hellerstein, Stonebraker and Hamilton’s survey of how relational engines are built describes the same split: a relational query processor above, a transactional storage manager below, and shared components such as the catalog beside them.

The sections below take the stages in order, with something you can run for each. The examples use a small college database: four departments, eight students, five courses and fourteen enrolments.

-- A small college database: departments, students, courses and who is enrolled in what.
CREATE TABLE departments (
  dept_id INTEGER PRIMARY KEY,
  name    TEXT NOT NULL UNIQUE
);
CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  dept_id    INTEGER NOT NULL REFERENCES departments (dept_id),
  year       INTEGER NOT NULL CHECK (year BETWEEN 1 AND 4)
);
CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  code      TEXT NOT NULL UNIQUE,
  title     TEXT NOT NULL,
  credits   INTEGER NOT NULL
);
CREATE TABLE enrolments (
  student_id INTEGER NOT NULL REFERENCES students (student_id),
  course_id  INTEGER NOT NULL REFERENCES courses (course_id),
  grade      TEXT,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO departments VALUES
  (1, 'Computer Science'), (2, 'Electronics'), (3, 'Mechanical'), (4, 'Civil');
INSERT INTO students VALUES
  (1, 'Asha', 1, 3), (2, 'Ravi', 2, 3), (3, 'Meera', 1, 2), (4, 'Kiran', 3, 3),
  (5, 'Farah', 1, 3), (6, 'Joseph', 4, 2), (7, 'Lakshmi', 2, 2), (8, 'Arjun', 1, 4);
INSERT INTO courses VALUES
  (1, 'CS301', 'Database Systems', 4), (2, 'CS302', 'Operating Systems', 4),
  (3, 'EC201', 'Signals and Systems', 3), (4, 'MA201', 'Probability', 3),
  (5, 'HS101', 'Technical Writing', 2);
INSERT INTO enrolments VALUES
  (1, 1, 'A'), (1, 2, 'B'), (2, 1, 'B'), (2, 3, 'A'), (3, 1, NULL), (3, 4, 'A'), (4, 4, 'C'),
  (5, 1, 'A'), (5, 5, 'B'), (6, 4, 'B'), (7, 3, 'A'), (7, 1, NULL), (8, 2, 'A'), (8, 5, 'A');

-- Who is enrolled in Database Systems, and in which department?
SELECT s.name, d.name AS department
FROM courses AS c
JOIN enrolments AS e ON e.course_id = c.course_id
JOIN students AS s ON s.student_id = e.student_id
JOIN departments AS d ON d.dept_id = s.dept_id
WHERE c.code = 'CS301'
ORDER BY s.name;

-- How SQLite plans to run the same query.
EXPLAIN QUERY PLAN
SELECT s.name, d.name AS department
FROM courses AS c
JOIN enrolments AS e ON e.course_id = c.course_id
JOIN students AS s ON s.student_id = e.student_id
JOIN departments AS d ON d.dept_id = s.dept_id
WHERE c.code = 'CS301'
ORDER BY s.name;

-- The same query with the tables written in another order.
EXPLAIN QUERY PLAN
SELECT s.name, d.name AS department
FROM enrolments AS e
JOIN students AS s ON s.student_id = e.student_id
JOIN departments AS d ON d.dept_id = s.dept_id
JOIN courses AS c ON c.course_id = e.course_id
WHERE c.code = 'CS301'
ORDER BY s.name;

┌─────────┬──────────────────┐
│  name   │    department    │
├─────────┼──────────────────┤
│ Asha    │ Computer Science │
│ Farah   │ Computer Science │
│ Lakshmi │ Electronics      │
│ Meera   │ Computer Science │
│ Ravi    │ Electronics      │
└─────────┴──────────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                              detail                               │
├────┼────────┼─────────┼───────────────────────────────────────────────────────────────────┤
│ 6  │ 0      │ 45      │ SEARCH c USING COVERING INDEX sqlite_autoindex_courses_1 (code=?) │
│ 10 │ 0      │ 211     │ SCAN e USING COVERING INDEX sqlite_autoindex_enrolments_1         │
│ 15 │ 0      │ 45      │ SEARCH s USING INTEGER PRIMARY KEY (rowid=?)                      │
│ 18 │ 0      │ 45      │ SEARCH d USING INTEGER PRIMARY KEY (rowid=?)                      │
│ 26 │ 0      │ 0       │ USE TEMP B-TREE FOR ORDER BY                                      │
└────┴────────┴─────────┴───────────────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                              detail                               │
├────┼────────┼─────────┼───────────────────────────────────────────────────────────────────┤
│ 6  │ 0      │ 45      │ SEARCH c USING COVERING INDEX sqlite_autoindex_courses_1 (code=?) │
│ 10 │ 0      │ 211     │ SCAN e USING COVERING INDEX sqlite_autoindex_enrolments_1         │
│ 15 │ 0      │ 45      │ SEARCH s USING INTEGER PRIMARY KEY (rowid=?)                      │
│ 18 │ 0      │ 45      │ SEARCH d USING INTEGER PRIMARY KEY (rowid=?)                      │
│ 26 │ 0      │ 0       │ USE TEMP B-TREE FOR ORDER BY                                      │
└────┴────────┴─────────┴───────────────────────────────────────────────────────────────────┘

The first result is the answer: the five students enrolled in Database Systems, with their departments. The second is SQLite’s plan for that query, and the third its plan for the same query with the tables written in another order; the planning section below reads them line by line.

Parsing, and checking the names

Parsing turns text into a tree using only the grammar of SQL, so the parser knows nothing yet about your tables. The names are checked next, against the catalog. PostgreSQL’s manual describes the two steps separately: the parser builds a parse tree from the rules of the syntax, and a transformation process then looks up which tables, functions and operators the query refers to and builds a query tree. It keeps them apart because reading the catalog needs a transaction, and statements such as BEGIN and ROLLBACK must be recognised before one exists.

You can see the stages from the errors they produce:

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  year       INTEGER NOT NULL CHECK (year BETWEEN 1 AND 4)
);

-- 1. Not SQL at all: the parser stops at the first word it cannot fit into the grammar.
SELEC name FROM students;

-- 2. Valid SQL, but the catalog has no table of that name.
SELECT name FROM student;

-- 3. A valid statement whose data breaks a rule while it runs.
INSERT INTO students VALUES (9, 'Nisha', 5);

-- 4. A statement that passes every stage.
INSERT INTO students VALUES (9, 'Nisha', 1);
SELECT student_id, name, year FROM students;

┌────────────┬───────┬──────┐
│ student_id │ name  │ year │
├────────────┼───────┼──────┤
│ 9          │ Nisha │ 1    │
└────────────┴───────┴──────┘

Parse error near line 8: near "SELEC": syntax error
Parse error near line 11: no such table: student
Runtime error near line 14: CHECK constraint failed: year BETWEEN 1 AND 4

SELEC fails in the parser, at the first word that fits no grammar rule. FROM student is perfectly good SQL, but the catalog has no table of that name, so the statement fails while SQLite prepares it; the sqlite3 shell, whose messages these mimic, reports every failure before a statement runs as a “parse error”. The CHECK failure is different: the statement compiled, started running, and the row broke a rule. The last insert gets through every stage, and the final SELECT shows its row. Telling the three failures apart tells you where to look: the text, the schema, or the data.

Rewriting views

A view is stored as a query. PostgreSQL’s rewrite system replaces a reference to a view with the view’s definition, so that the rest of the engine sees only base tables. SQLite has no separate rewriter: each use of a view becomes a subquery, and while it compiles the statement it tries to flatten that subquery into the outer query, so that the planner can use the indexes of the underlying tables. Either way, what reaches the planner is a query over base tables.

Planning: choosing how to run it

The planner considers the different ways to produce the result, estimates what each one costs and keeps the cheapest. PostgreSQL’s manual gives the simplest case: a table with an index can be read with a sequential scan or through the index, and the planner prices both. The estimates rest on statistics: how many rows and pages each table has, and how values are distributed. PostgreSQL keeps them in its catalogs and updates them with ANALYZE; SQLite’s ANALYZE stores them in the table sqlite_stat1, and its documentation recommends running PRAGMA optimize to keep them current.

Read the college plan above from the top. SQLite implements joins as nested loops, and the order of the lines is the order of the loops, outermost first. Only the detail column matters; id, parent and notused are internal.

1. SEARCH c USING COVERING INDEX sqlite_autoindex_courses_1 (code=?): the outer loop finds the course whose code is CS301 through the index that the UNIQUE constraint created. Covering means the index alone holds every column of courses the query needs, so the table itself is never read.
2. SCAN e USING COVERING INDEX sqlite_autoindex_enrolments_1: for that course, SQLite reads every entry of the enrolments primary-key index. The index starts with student_id, so it cannot jump to one course_id: this line is a full scan.
3. SEARCH s USING INTEGER PRIMARY KEY (rowid=?) and SEARCH d …: each enrolment finds its student, and each student their department, with one lookup by key. A column declared INTEGER PRIMARY KEY becomes an alias for the rowid, the key of the table’s own B-tree, so the lookup needs no separate index.
4. USE TEMP B-TREE FOR ORDER BY: the rows arrive in enrolment order, so SQLite sorts them by name in a temporary B-tree before returning them.

Now compare the third result. That query starts its FROM clause with enrolments and names courses last, yet the plan is line for line the same: courses is still the outer loop. The order of the loops is the planner’s decision, not the text’s; SQLite’s optimizer documentation says it reorders inner joins whenever that lets it use better indexes, and here the unique index on code narrows courses to a single row before anything else is read. With fourteen enrolments, the full scan in line 2 costs nothing. With two million it would be the line to fix, with an index that starts with course_id, a step the module on query processing takes.

Executing: a plan becomes a program

SQLite turns every statement into a program for its own virtual machine, the bytecode engine, and EXPLAIN shows that program instead of running it. Here it is for a one-table query:

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  dept_id    INTEGER NOT NULL,
  year       INTEGER NOT NULL
);

-- The program SQLite's bytecode engine would run for this query.
EXPLAIN SELECT name FROM students WHERE dept_id = 1;

┌──────┬─────────────┬────┬────┬────┬──────────┬────┬─────────┐
│ addr │   opcode    │ p1 │ p2 │ p3 │    p4    │ p5 │ comment │
├──────┼─────────────┼────┼────┼────┼──────────┼────┼─────────┤
│ 0    │ Init        │ 0  │ 9  │ 0  │          │ 0  │         │
│ 1    │ OpenRead    │ 0  │ 2  │ 0  │ 3        │ 0  │         │
│ 2    │ Rewind      │ 0  │ 8  │ 0  │          │ 0  │         │
│ 3    │ Column      │ 0  │ 2  │ 1  │          │ 0  │         │
│ 4    │ Ne          │ 2  │ 7  │ 1  │ BINARY-8 │ 84 │         │
│ 5    │ Column      │ 0  │ 1  │ 3  │          │ 0  │         │
│ 6    │ ResultRow   │ 3  │ 1  │ 0  │          │ 0  │         │
│ 7    │ Next        │ 0  │ 3  │ 0  │          │ 1  │         │
│ 8    │ Halt        │ 0  │ 0  │ 0  │          │ 0  │         │
│ 9    │ Transaction │ 0  │ 0  │ 1  │ 0        │ 1  │         │
│ 10   │ Integer     │ 1  │ 2  │ 0  │          │ 0  │         │
│ 11   │ Goto        │ 0  │ 1  │ 0  │          │ 0  │         │
└──────┴─────────────┴────┴────┴────┴──────────┴────┴─────────┘

Each instruction has an opcode and up to five operands, p1 to p5. The program reads like this:

• 0 Init jumps to instruction 9, where the setup code sits: 9 Transaction starts a read transaction, 10 Integer puts the constant 1 into register 2, and 11 Goto jumps back to instruction 1.
• 1 OpenRead opens a cursor on the B-tree whose root is page 2, the students table, and 2 Rewind moves it to the first row, or jumps to 8 if the table is empty.
• 3 Column copies column 2 (dept_id) of the current row into register 1, and 4 Ne jumps to 7 when it differs from register 2: that is the WHERE clause.
• 5 Column copies column 1 (name) into register 3, and 6 ResultRow hands that row to the program that asked. The engine pauses here until the caller wants the next row.
• 7 Next moves to the next row and jumps back to 3; after the last row, 8 Halt ends the program.

The comment column is empty because explanatory comments are a compile-time option of SQLite that is off by default; its documentation names the command-line shell as the kind of program that wants them, so the same EXPLAIN in a sqlite3 shell usually comes with a comment on each line. The opcodes themselves change between releases, so treat a listing as a picture of one version, not as an interface.

PostgreSQL does not turn the whole query into one program like this. Its executor walks the plan tree recursively: each node asks the nodes below it for rows, a scan node at the bottom reads a table or an index, and join, sort and aggregate nodes combine what comes up.

Access methods, buffers and storage

At the bottom of every plan, rows come out of tables and indexes, and those live in pages of a fixed size:

CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept_id INTEGER);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, title TEXT);
CREATE INDEX students_by_dept ON students (dept_id);

-- Every table and every index is a B-tree of its own, starting at its root page,
-- and all of them are in one database file.
SELECT type, name, rootpage FROM sqlite_schema ORDER BY rootpage;

-- The file is made of pages of one fixed size.
PRAGMA page_size;
PRAGMA page_count;

┌───────┬────────────────────────────┬──────────┐
│ type  │            name            │ rootpage │
├───────┼────────────────────────────┼──────────┤
│ table │ students                   │ 2        │
│ table │ courses                    │ 3        │
│ index │ sqlite_autoindex_courses_1 │ 4        │
│ index │ students_by_dept           │ 5        │
└───────┴────────────────────────────┴──────────┘
┌───────────┐
│ page_size │
├───────────┤
│ 4096      │
└───────────┘
┌────────────┐
│ page_count │
├────────────┤
│ 5          │
└────────────┘

In SQLite every table and every index is a B-tree of its own, and all of them share one database file. The rootpage column gives the page where each B-tree starts; page 1, which is not in the list, holds the file’s header and the root of sqlite_schema itself. The file is made of 4,096-byte pages, SQLite’s default, and this database needs 5 of them. The pager reads and writes those pages, caches them in memory, and also provides the rollback journal, the atomic commit and the locking of the file. Below it, the VFS, SQLite’s operating-system interface, does the actual file reads and writes, which is how the same engine runs on Unix, Windows or, in sql.js, inside a web page.

PostgreSQL stores each table and each index in a separate file, inside a folder for each database in its data directory, and splits a file into segments once it passes 1 GB. Its pages are usually 8 kB. The buffer manager keeps pages in shared buffers, a block of memory every server process can use; the default is typically 128 MB, and the manual suggests about a quarter of the memory of a dedicated database server as a starting value.

The buffer manager is one reason the same query can be quick one moment and slow the next: a page already in memory costs no disk read at all, while a page that has to come from disk costs a read before the executor can continue.

Transactions, locks and the log

The remaining parts of the engine wrap around the others. Concurrency control keeps concurrent transactions from seeing or overwriting each other’s unfinished work, with a lock manager and, in PostgreSQL, row versions as well. The log manager records changes so that a committed transaction survives a crash and an unfinished one can be undone, as the rollback journal did in the previous lesson. In SQLite, the pager does both jobs for its one file: it locks the file and keeps the journal. In PostgreSQL they are separate subsystems, and the log is a write-ahead log; the modules on concurrency control and on recovery open them up.

Embedded or client-server

The two engines on this page differ most in where they run:

• SQLite is a library. It is linked into your program, and a query is a function call inside your own process: no server, no network, no separate login. Any number of connections may read a database file at once, but only one may write at a time.
• PostgreSQL is a server. Its manual describes a supervisor process, the postmaster, that listens for connections and starts a new backend process for each one. The client sends the query as plain text; the backend parses, plans and executes it and sends the rows back. Backends share data through shared memory, which is where the shared buffers live.

Running one process per connection is one of three process models that the Hellerstein, Stonebraker and Hamilton survey describes. The others are one thread per connection inside a single server process, which is what MySQL’s manual describes (a dedicated thread for each client connection, reused through a thread cache), and a process pool, where a bounded set of processes takes turns serving requests. With processes, the operating system keeps one worker’s memory bugs away from the others, although the buffer pool and the lock table still sit in memory they all share; threads need less memory each and switch faster, which matters with thousands of connections.

The process model can even decide what is possible. PGlite, PostgreSQL compiled to WebAssembly, cannot start new processes, so its documentation explains that it runs PostgreSQL’s single-user mode in one process and allows one user and one connection at a time.

Courses also describe applications by tiers. In a one-tier application, the program and its data run together, like a phone app with an embedded SQLite database. In a two-tier, or client-server, system the client program talks to the database server directly. In a three-tier system, the client talks to a middle tier, such as an application server, and only the middle tier talks to the database; many web applications have four, with a web server in front of the application server.

Reading a PostgreSQL plan

PostgreSQL does not run on this page, but its plans are easy to read once you know the shape. EXPLAIN prints one line per node of the plan tree, with the children indented below their parent. The bottom nodes are scans, such as Seq Scan (every row), Index Scan (through an index) and Index Only Scan (from the index alone, the equivalent of SQLite’s covering index). Above them sit join nodes such as Nested Loop, Hash Join and Merge Join, and nodes for sorting and aggregation.

Each line ends with four estimates in parentheses: cost= a start-up cost and a total cost, rows= the number of rows the node is expected to return, and width= their average size in bytes. Costs are in arbitrary units, conventionally the cost of reading one page in sequence, and a node’s cost includes the costs of its children. The manual shows how a plain sequential scan is priced: the pages read times seq_page_cost (1.0 by default) plus the rows read times cpu_tuple_cost (0.01). For an enrolments table of 2,000,000 rows in 12,500 pages, that is 12,500 × 1.0 + 2,000,000 × 0.01 = 32,500 units.

EXPLAIN ANALYZE runs the query for real and adds the actual time and the actual number of rows of every node. The manual’s advice is to look first at whether the estimated row counts are close to the actual ones: when they are far apart, the planner chose its plan from wrong numbers, and better statistics are the first thing to try.

Which engine are you running?

The examples on this page run in your browser with sql.js, which is SQLite compiled to WebAssembly. Ask it which SQLite it contains:

-- Which SQLite is answering? Run this first in any new environment.
SELECT sqlite_version() AS sqlite_version;

┌────────────────┐
│ sqlite_version │
├────────────────┤
│ 3.49.1         │
└────────────────┘

Now the same question from Python, on the computer that recorded this lesson’s runs:

"""Which SQLite does Python's sqlite3 module use? The one it was built against."""
import platform
import sqlite3

print("Python:", platform.python_version())
print("SQLite library inside the sqlite3 module:", sqlite3.sqlite_version)

db = sqlite3.connect(":memory:")
print("The same, asked in SQL:", db.execute("SELECT sqlite_version()").fetchone()[0])

Python: 3.14.8
SQLite library inside the sqlite3 module: 3.51.0
The same, asked in SQL: 3.51.0

They differ, and neither is the newest SQLite that sqlite.org’s release history lists. Each program carries the SQLite it was built with: sql.js 1.14.2 bundles SQLite 3.49.1, while Python’s sqlite3 module uses the SQLite library it was linked against, here the 3.51.0 that comes with macOS. Your own computer may print other numbers again. Versions matter because features, error messages and plans change between releases; SQLite’s own documentation says that the output of EXPLAIN and EXPLAIN QUERY PLAN may change from one release to the next.

So start every lab, and every bug report, with the version: SELECT sqlite_version() in SQLite, or SELECT version() in PostgreSQL, which returns a string describing the server (server_version_num gives the same as a number that programs can compare). PostgreSQL ships a new minor release of each supported major version at least every three months, with fixes only, and its project recommends always running the latest minor release of your major version.

Exercises

-- The students enrolled in CS302, with the course title. Fix the three mistakes.
SELEC s.name, c.title
FROM enrolment AS e
JOIN students AS s ON s.student_id = e.student_id
JOIN courses AS c ON c.course_id = e.course_id
WHERE c.code = CS302;

# Sample tests (public): each test builds the three tables in a fresh database, runs the learner's query and compares
# the result with the expected rows (the order of rows does not matter; column names must match).
tests:
  - name: lists the two students enrolled in CS302
    setup: |
      CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
      CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, title TEXT NOT NULL);
      CREATE TABLE enrolments (student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, PRIMARY KEY (student_id, course_id));
      INSERT INTO students VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera'), (8, 'Arjun');
      INSERT INTO courses VALUES (1, 'CS301', 'Database Systems'), (2, 'CS302', 'Operating Systems');
      INSERT INTO enrolments VALUES (1, 1), (1, 2), (2, 1), (3, 1), (8, 2);
    expected:
      columns: [name, title]
      rows:
        - [Asha, Operating Systems]
        - [Arjun, Operating Systems]
  - name: returns only CS302 when several courses share students
    setup: |
      CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
      CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, title TEXT NOT NULL);
      CREATE TABLE enrolments (student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, PRIMARY KEY (student_id, course_id));
      INSERT INTO students VALUES (4, 'Kiran'), (5, 'Farah'), (6, 'Joseph'), (7, 'Lakshmi');
      INSERT INTO courses VALUES (10, 'CS302', 'Operating Systems'), (11, 'MA201', 'Probability'), (12, 'CS303', 'Compilers');
      INSERT INTO enrolments VALUES (4, 10), (4, 11), (5, 12), (6, 10), (7, 11), (7, 12);
    expected:
      columns: [name, title]
      rows:
        - [Kiran, Operating Systems]
        - [Joseph, Operating Systems]

Interview questions

Fresher · What happens to a SQL query between the moment it is sent and the first row coming back?

The engine parses the text into a tree and checks the table and column names against its catalog. It rewrites the tree, replacing views with their underlying tables. The planner then considers different ways to run the query, such as a full scan or an index, and different join orders, estimates their costs from statistics and picks the cheapest. The executor runs that plan: it asks the access methods for rows of tables and indexes, which get pages from the buffer manager, which reads from disk only the pages that are not already in memory. Locks and the log keep concurrent transactions correct and changes durable. In PostgreSQL this happens in a server process per connection; in SQLite it happens inside the application, and the plan becomes bytecode for SQLite’s virtual machine.

Fresher · What is the difference between an embedded database engine and a client-server engine?

An embedded engine such as SQLite is a library inside the application: queries are function calls, there is no server or network, the database is an ordinary file, and access is controlled by the operating system’s file permissions. SQLite allows one writer at a time per file. A client-server engine such as PostgreSQL runs as server processes of its own; applications connect over a network and send SQL text, and the server manages users, privileges and many concurrent writers. PostgreSQL starts a process for each connection, while MySQL uses a thread per connection. Embedded engines suit data that belongs to one application or device; client-server engines suit data that many programs and people share.

Key takeaways

• Every query is parsed, checked against the catalog, rewritten, planned and executed; errors from each stage look different, and tell you whether to look at the text, the schema or the data.
• The planner chooses among plans by estimated cost, using statistics; EXPLAIN QUERY PLAN in SQLite and EXPLAIN in PostgreSQL show its choice.
• SQLite compiles a statement into bytecode for its virtual machine; PostgreSQL’s executor walks a plan tree.
• Below the executor, access methods read tables and indexes, the buffer manager caches pages, and storage holds them in files: one file for a whole SQLite database, one file per table and index in PostgreSQL.
• SQLite is a library inside your program; PostgreSQL is a server with one process per connection, and MySQL uses a thread per connection.
• Always check the version you are running: the SQLite in your browser, in Python and on your system can all differ.