↓ Skip to main content
Turso: Concurrent Writes on the SQLite File Format (CMU Database Group)
  1. Archive/

Turso: Concurrent Writes on the SQLite File Format (CMU Database Group)

Author
AI Assistant
Curated for Pradithya Aria Pura · AI-written summary of the source material

SQLite in WAL mode lets many readers run alongside one writer. Turso is a Rust rewrite of SQLite that keeps the file format and the SQL dialect, and its headline addition is lifting that one-writer limit. In this CMU Database Group seminar, Mikaël Francoeur (Turso, now part of Supabase) explains how they bolted multi-version concurrency control onto a file that stock SQLite still has to be able to open. Andy Pavlo interrupts often, and his questions are what make the hour useful.

From a side project to the company’s main bet
#

GitHub star history for tursodatabase/turso from mid-2024 to late 2026

The company Turso started in 2022 with libSQL, a fork of SQLite that was easier to contribute to and added a client-server mode. libSQL still backs their commercial cloud, but it now only gets security fixes. The database the talk is about began as a hobby project: then-CTO Pekka Enberg (a former Linux kernel maintainer) rewrote SQLite, first in Zig and then in Rust, under the name Limbo. Contributors who knew how to write pagers, B-trees and bytecode engines turned up without being asked. When the company announced it as an official project at the end of 2024, the post went viral, and CEO Glauber Costa decided to make it the company’s main focus.

Francoeur says everyone now working on the database was hired from its contributor pool. It has over 370 contributors and is at version 0.8, pre-GA but already in production for some enterprise customers.

What Turso is
#

Turso features slide: SQLite-compatible, async-first, concurrent writes, Postgres-compatible (WIP), sync protocol

Compatibility goes both ways: the file format and the dialect. Pavlo asks whether that means inheriting SQLite’s portability-driven choices, like dynamic typing. Francoeur says yes, and argues the format’s ubiquity (more than a trillion SQLite databases in existence) is worth the baggage.

The other design commitments:

  • The API is async-first. step() returns a row, an “I/O pending” signal, a lock-busy result, or a yield request, so the engine never blocks a user thread and can be embedded in any runtime.
  • A Postgres compatibility layer is the current main development effort, with row-level security just added and PL/pgSQL triggers and pg_dump support in progress. This is behind Supabase’s planned lightweight “Supabase Spark” offering.
  • A push/pull sync protocol lets an embedded database on a phone or in an agent sandbox sync with a remote copy.

Why embedded databases, again
#

Slide: why use an embedded database, with Turso’s free-tier limits

The argument is about density. Agents now create databases far more often than people do. Supabase went from 10 million to 50 million Postgres databases in a year, and Francoeur says one Turso client creates 90,000 databases a day. An embedded database is “a thread and a file”, not a VM or a process, so you can afford to hand out billions of them.

How SQLite WAL mode works
#

SQLite under journal_mode=WAL: database file pages and WAL frames with readers’ read marks

Before getting to MVCC, Francoeur walks through SQLite’s WAL mode, because Turso’s design depends on it:

  • Writers never touch the database file. They append full physical pages to the -wal file, and the last frame of a transaction carries a commit marker.
  • Each reader sets a read mark at a WAL position and sees, for every page, the latest committed version at or before that mark. This is already a form of multi-versioning at page granularity.
  • A frame with no commit marker gets ignored by readers and overwritten by the next writer, which is how recovery works.
  • A checkpoint copies pages back into the main file. TRUNCATE mode does it all; PASSIVE mode backfills what it can, and the next writer restarts the WAL once everything is redundant.

He points out that this isn’t the steal/no-force WAL from Pavlo’s lectures. It’s no-steal/force and closer to shadow paging. Its limit is that only one writer can run at a time.

MVCC on top of the SQLite format
#

MVCC internals: MvccCursor wrapping the MV store and B-tree cursor, MvStore as a SkipMap of row versions

You switch modes with PRAGMA journal_mode = 'mvcc'. Turso flips the two version bytes in the database header so stock SQLite refuses the file and can’t misread it. Switching back is a constant-time header change once a checkpoint has run.

The design takes most of its ideas from Microsoft’s Hekaton paper (Larson et al., 2012):

  • Isolation drops from serializable to snapshot isolation. Concurrent transactions succeed unless they write the same row, in which case one gets a write-write conflict and retries.
  • Writers append only to a separate logical redo log (test.db-log), not to the WAL or the database file.
  • MvStore is an in-memory map from row ID to a vector of full row versions, each with begin and end timestamps, plus a monotonic clock.
  • MvccCursor works like a Gang of Four decorator. It steps the in-memory MV store and the ordinary SQLite B-tree cursor together, so committed data can live in either place.

Pavlo pushes on version-chain ordering and where index lookups land. The answer is that versions are packed contiguously in a vector next to the row ID, so oldest-to-newest versus newest-to-oldest matters less than it would with a linked chain.

Recovery and checkpointing
#

MVCC recovery: truncate the WAL, then replay the logical log above persistent_tx_ts_max into the MV store

Recovery runs in two steps. First, finish any interrupted checkpoint by truncating the WAL; in MVCC mode the WAL should be empty during normal operation. Then replay the logical log into the MV store, skipping every entry at or below persistent_tx_ts_max, a header field that works like an LSN for “already checkpointed”.

Checkpoint under exclusive lock: select latest versions, apply to B-tree pages, commit to WAL, copy to DB, fsync, clear log, truncate WAL

A checkpoint, triggered by default when the logical log reaches 4 MB, works like a memtable flush:

  1. Take the latest committed version of every live row from the MV store.
  2. Apply those changes to physical SQLite B-tree pages and record the highest timestamp seen.
  3. Commit those pages to the WAL in the normal SQLite way and fsync.
  4. Copy the WAL into the database file and fsync.
  5. Clear the logical log, truncate the WAL, and clear the MV store.

When it finishes, everything is back “in SQLite land”, and the file is a normal SQLite database apart from the header bytes. Garbage collection also runs incrementally on the commit path: each commit cleans up a slice of the MV store, which keeps memory bounded between checkpoints.

Known limitations
#

MVCC limitations: stop-the-world checkpoint, stores full versions, no version spilling

Francoeur lists three:

  • The checkpoint holds a global exclusive lock, so concurrent transactions get a busy error for a few milliseconds. Fuzzy checkpointing is being tried.
  • Versions are stored as full rows, not deltas. He says no customer has asked for deltas.
  • Versions don’t spill to disk, so a transaction can’t be larger than the MV store’s memory. Building an index on a million-row table is the workload that strains this most. Large BLOBs are also held inline in memory, whereas SQLite would use overflow pages.

Staying compatible
#

Testing slide: sqltest DSL, SQLite TCL tests, cross-check-integrity, fault injection, fuzzers, Elle

During Q&A, Pavlo asks how they decide when to diverge from SQLite. The answer is almost never, and only by adding things (full-text search syntax is the one exception). They enforce this mechanically. They run SQLite’s TCL test suite and pass 95 to 97% of the relevant files. They differentially fuzz outputs against SQLite. Their test DSL has a @cross-check-integrity decorator that hands the resulting file to real SQLite and runs PRAGMA integrity_check, since SQLite stores its schema as SQL text and a single wrong character produces “database disk image is malformed”.

He ends with a slide on agent-driven optimization: a sequence of 15 agent-written commits made SELECT 1 2.4 times faster. His point is that writing the engine in Rust makes memory-safety bugs far less likely when changes land at that pace.

Key takeaways
#

  1. Turso is compatible with SQLite at both the file-format and dialect level, and treats any divergence as additive, with SQLite’s own integrity check run against its output in CI.
  2. SQLite’s WAL mode already gives page-level multi-versioning for readers, but it allows only one writer. Turso’s MVCC mode exists to remove that limit.
  3. Concurrent writes come from a Hekaton-style design: snapshot isolation, an in-memory row-version store, and a separate logical redo log that leaves the SQLite WAL empty during normal operation.
  4. A checkpoint flushes the version store back into ordinary B-tree pages, like an LSM memtable flush, which is why an MVCC database can go back to being a plain SQLite file.
  5. Flipping the SQLite version bytes in the header is a cheap way to make sure stock SQLite rejects a file it would otherwise misread.
  6. The current costs are a short stop-the-world checkpoint, full-row versions, and transactions bounded by memory. Building indexes on large tables is the workload that hits the last one first.
  7. The case for embedded databases now is per-database cost. Agents create databases at rates that VM- or process-per-database architectures can’t serve cheaply.

Source
#

  • Talk: “Turso: An SQLite-compatible, Open-Contribution RDBMS”
  • Speaker: Mikaël Francoeur (Turso / Supabase), hosted by Andy Pavlo
  • Channel: CMU Database Group (seminar series)
  • Duration: 53:07
  • Video: https://www.youtube.com/watch?v=-RFqqK1QJZc

Related