Simon Willison's Weblog: ben-johnson

Litestream v0.5.0 is Here

2025-10-03T15:10:21+00:00

I've been running Litestream to backup SQLite databases in production for a couple of years now without incident. The new version has been a long time coming - Ben Johnson took a detour into the FUSE-based LiteFS before deciding that the single binary Litestream approach is more popular - and Litestream 0.5 just landed with this very detailed blog posts describing the improved architecture.

SQLite stores data in pages - 4096 (by default) byte blocks of data. Litestream replicates modified pages to a backup location - usually object storage like S3.

Most SQLite tables have an auto-incrementing primary key, which is used to decide which page the row's data should be stored in. This means sequential inserts to a small table are sent to the same page, which caused previous Litestream to replicate many slightly different copies of that page block in succession.

The new LTX format - borrowed from LiteFS - addresses that by adding compaction, which Ben describes as follows:

We can use LTX compaction to compress a bunch of LTX files into a single file with no duplicated pages. And Litestream now uses this capability to create a hierarchy of compactions:

at Level 1, we compact all the changes in a 30-second time window

at Level 2, all the Level 1 files in a 5-minute window

at Level 3, all the Level 2’s over an hour.

Net result: we can restore a SQLite database to any point in time, using only a dozen or so files on average.

I'm most looking forward to trying out the feature that isn't quite landed yet: read-replicas, implemented using a SQLite VFS extension:

The next major feature we’re building out is a Litestream VFS for read replicas. This will let you instantly spin up a copy of the database and immediately read pages from S3 while the rest of the database is hydrating in the background.

Via Hacker News

Tags: sqlite, fly, litestream, ben-johnson

Quoting Ben Johnson

2023-01-26T19:36:03+00:00

[On SQLite for production concurrent writes] In general, WAL mode “just works” as Simon said. You just need to make sure you don’t have long running write transactions, although those are somewhat problematic in any database system. Don’t do stuff like starting a write txn and then calling a remote API and then committing. That’ll kill your write throughout.

— Ben Johnson

Tags: sqlite, ben-johnson

Introducing LiteFS

2022-09-21T18:56:42+00:00

Introducing LiteFS

LiteFS is the new SQLite replication solution from Fly, now ready for beta testing. It’s from the same author as Litestream but has a very different architecture; LiteFS works by implementing a custom FUSE filesystem which spies on SQLite transactions being written to the journal file and forwards them on to other nodes in the cluster, providing full read-replication. The signature Litestream feature of streaming a backup to S3 should be coming within the next few months.

Via Hacker News

Tags: databases, replication, sqlite, fly, litestream, ben-johnson

How the SQLite Virtual Machine Works

2022-09-07T20:49:10+00:00

How the SQLite Virtual Machine Works

The latest entry in Ben Johnson’s series about SQLite internals.

Tags: databases, sqlite, ben-johnson

How SQLite Scales Read Concurrency

2022-08-24T16:16:19+00:00

How SQLite Scales Read Concurrency

Ben Johnson’s series on SQLite internals continues—this time with a detailed explanation of how the SQLite WAL (Write-Ahead Log) is implemented.

Via Hacker News

Tags: databases, sqlite, ben-johnson

How SQLite Helps You Do ACID

2022-08-10T15:39:09+00:00

How SQLite Helps You Do ACID

Ben Johnson’s series of posts explaining the internals of SQLite continues with a deep look at how the rollback journal works. I’m learning SO much from this series.

Via @benbjohnson

Tags: sqlite, fly, ben-johnson

SQLite Internals: Pages & B-trees

2022-07-27T14:57:25+00:00

SQLite Internals: Pages & B-trees

Ben Johnson provides a delightfully clear introduction to SQLite internals, describing the binary format used to store rows on disk and how SQLite uses 4KB pages for both row storage and for the b-trees used to look up records.

Via @benbjohnson

Tags: algorithms, databases, sqlite, ben-johnson

SQLite Happy Hour - a Twitter Spaces conversation about three interesting projects building on SQLite

2022-03-23T22:01:07+00:00

Yesterday I hosted SQLite Happy Hour. my first conversation using Twitter Spaces. The idea was to dig into three different projects that were doing interesting things on top of SQLite. I think it worked pretty well, and I'm curious to explore this format more in the future.

Here's the tweet that initially promoted the space:

Interested in devious tricks to push the envelope of what you can do with SQLite?

Join myself, @benbjohnson, @geoffreylitt and @nschiefer on Tuesday for a Twitter Spaces conversation about @litestream, @datasetteproj and Riffle!https://t.co/ukRMVgC09u
- Simon Willison (@simonw) March 17, 2022

My co-hosts, representing the three projects, were:

Ben Johnson @benbjohnson, creator of Litestream - a tool that adds replication to SQLite built on top of the WAL mechanism.
Geoffrey Litt @hgeoffreylitt and Nicholas Schiefer @nschiefer who are working on Riffle, a project exploring the idea of driving reactive user interfaces using SQL queries - see Building data-centric apps with a reactive relational database.

Twitter Spaces recorded audio only lasts for 30 days, so I've exported the audio and shared it on SoundCloud as well.

Simon Willison · SQLite Happy Hour - 22nd March 2022

Collaborative notes from the session

Something I've observed in Twitter Spaces I've joined in the past is that they can really benefit from a dedicated back-channel, to share links and allow audience participation without people needing to first request to speak.

A trick I've used with online talks I've given in the past is to start a collaborative Google Doc to collect shared notes and questions. I tried this for the Twitter Space, and it worked really well!

You see that document here. During the session the document was open for anyone to edit - I've locked it down now that the session has ended.

I've duplicated the final form of the document at the bottom of this post.

Something I really like about this format is that it allows for additional material to be posted later. I spent some time adding more detailed answers to the questions about Datasette after the session had ended.

Thoughts for if I do this again

This was my first time hosting a space, and I learned a lot along the way.

Firstly, this kind of thing works the best when there is a back and forth between the participants.

My original idea was to have each project talk for ten minutes, then spend five minutes on discussion between the panel before moving on to the next project - and 15 minutes of open discussion at the end.

My co-hosts suggested we try to make it more conversational, interrupting each other as we went along. We did that, and it worked much better: these conversations are far more interesting as a conversation than a monolog.

I still don't have a great feel for when to interrupt people in an audio-only conversation, since unlike an in-person panel there are no visual clues to go off!

Techology: it turns out Twitter Spaces has wildly different functionality on web v.s. mobile apps. We spent the first five minutes making sure all of our speakers could talk! We really should have done a tech rehearsal first, but I wasn't sure how to do that without accidentally broadcasting it to the world - maybe setup burner Twitter accounts for testing?

Presenting audio-only is itself a challenge: I'm used to leaning on visual demos when I explain what Datasette is in a talk, and not having those to fall back on was challenging. I had jotted down notes on the main points I wanted to hit which certainly helped, but I think there's a whole new presenting skill here that I need to work harder to develop.

Exporting the recorded audio from Twitter was frustrating but possible. I wrote some notes on how I did that in this TIL.

Our collaborative notes in full

SQLite Happy Hour Twitter Space

22nd March 2022 - 12:30pm PT / 1:30pm MT / 3:30pm ET

Welcome to the SQLite Happy Hour! This hour-long session will feature three projects that are doing interesting things with SQLite. Each project will provide a ten minute overview, followed by five minutes of discussion from the panel. The last 15 minutes of the hour will be an open discussion and general Q&A.

This document is open for anyone to edit. Please feel free to drop notes and questions in as we go along.

The recording of the space is available here: https://twitter.com/i/spaces/1ypKdEXvkMLGW

Riffle

Geoffrey Litt @geoffreylitt, Nicholas Schiefer @nschiefer

Riffle asks: what if you wrote your whole UI as a query over a local database? So far, we've built a prototype using SQLite and React. More background in this paper:

Building data-centric apps with a reactive relational database

Research project goal is to make development simpler, as opposed to the ongoing trend of more complexity.

Riffle looks at having a database-centric mechanism at the heart of the view. Declarative queries could make apps easier to understand and debug.

SQLite is the tool used for the prototype.

Local first architecture: Ink & Switch have been promoting this. Return to a world where you local client device serves as a source of truth - you can access data offline etc - and when the network is available your data gets synced to the cloud.

The prototype: a reactive layer that uses SQLite as a state management backend for React, using https://sql.js.org/ which compiles SQLite in WASM. Also built prototypes of desktop apps using https://github.com/tauri-apps/tauri - like Electron but using the system web browser instead of bundling its own.

Since they control the writes, they can re-execute every query after any writes happen. SQLite is so fast that this works fine, queries all take under a ms and even with a thousand queries you can still run them all.

ALL UI state is in the database - there's no local React component state - literally everything is in the database. This means all UI state is persistent by default.

IndexedDB is used for the in-browser persistence. The Tauri desktop app stores to a file on disk. Maybe SQL.js could do that with the new Chrome filesystem API stuff too?

Questions about Riffle:

Will Riffle target vanilla JS, or Node.js?
- It's running client-side, so vanilla JS
From Stephen: What about browser-native UI state like scroll position, URL path, query string, multiple independent browser tabs, etc?
- Great question. We do some syncing of browser-native state to put it in the DB: eg, to support virtualized list rendering we update scroll state in the DB with an event handler. But there's definitely some browser state that isn't being captured reliably. In the purest world, the pixels on your screen would be produced by a DB query :)
From Predrag Gruevski: Would "query the queries" be a viable approach for narrowing the set of queries that need to be re-executed after a given write? Simple example: if table X gets modified, query for all queries that have table X in a FROM clause, then re-execute them.
- yeah, that's roughly the direction we're headed. It's a little trickier than that if you start having subqueries / materialized view, but good general idea
From Longwei Su: Right now, each db update will cause a whole refresh. Is there any plan to refine the binding? So that any db update will only trigger UI component that "subscribe" to this section of the data. Sqlite have trigger, which can have callback on record update. How to construct that "publisher"-> "subscriber" mapping from sql query?

Comments for Riffle:

From Jesse - http://web.dev/file-system-access/ isn't a very rich api - I think you could persist to it, but I don't think you can seek/update/.../all the posix stuff sqlite probably needs
Hasura documented how they do reactive queries with Postgres, might be useful for minimising refetch overhead?

Datasette

Simon Willison @simonw

Datasette is an open source multi-tool for exploring and publishing data. It explores SQLite as a read-only mechanism for publishing structured data online in as flexible a manner as possible, and aims to build an ecosystem of plugins that can handle a wide range of exploratory data analysis challenges.

Video introduction here: https://simonwillison.net/2021/Feb/7/video/

Questions about Datasette:

How does it compares with https://github.com/dinedal/textql, it seems the same but instead of sqlite binaries, just raw csv files which are more ubiquitous, and easier to view and edit with with office software (msf excel, libreoffice calc) ?
- sqlite-utils memory provides similar functionality: https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/
Does Datasette need to worry about SQLite's Defense Against the Dark Arts security guidelines?
- Yes, absolutely! I've put a lot of work in there. Most importantly, Datasette enforces a time limit on queries, which cuts them off if they take more than a second.
The SQLite3 docs are sometimes light on examples for the tricky stuff (e.g., enabling WAL). What's your best sort of info beyond the official docs?
- I've been publishing my own notes here: https://til.simonwillison.net/sqlite
- The SQLite Forum is amazing - I ask questions on there and often get a reply from the maintainers within a few hours: https://sqlite.org/forum/forummain
From Predrag Gruevski: Regarding learning curve, is a GraphQL web IDE (with syntax highlighting / autocomplete etc.) sufficiently user-friendly for folks more comfortable with a spreadsheet than a CLI tool or SQL?
- Probably not! GraphQL requires thinking like a programmer too. I'm interested in helping people who aren't yet ready to learn any kind of programming language
- I have a plugin for Datasette that adds GraphQL with the GraphiQL user interface - demo here: datasette-graphql-demo.datasette.io
- Thanks! Would love to compare notes on this -- my experience from working with analysts at my employer was that they were able to master GraphiQL very quickly. In a sense, it was more intimidating than actually difficult, so working with them directly to get them over the initial difficulty hump via examples and targeted exercises made a huge positive impact.

Litestream

Ben Johnson @benbjohnson

Litestream adds replication to SQLite, allowing databases to be cheaply replicated to storage systems such as S3. Litestream also now implements live read-replication, where many read replicas can be run against a single leader database.

https://www.sqlite.org/np1queryprob.html - Many Small Queries Are Efficient in SQLite

Questions about Litestream:

What does the planned hot standby feature look like, especially regarding durability guarantees during fail-over?
- BJ: Hot standby is a tough issue to generalize. The database-as-a-service version of Litestream that's coming will handle this but it's not necessarily planned for Litestream)
Will DBaaS be hosted, OSS, or both?
- It'll be both
From Longwei Su: I assume offline update will be commit locally then sync with the online storage. If there is a offline commit that conflict with the online version(that already committed in). How to resolve the conflict?
Not sure if this relates to Litestream but; how big is sql.js --- how much does it cost (in kilobytes) to load sqlite in the browser?
- BJ: I think sql.js is 1.2MB so the cost depends on how much your provider charges for bandwidth
- Thanks! Meant "cost" in the sense of bytes transferred over wire --- this answers it :)

GraphQL

https://github.com/simonw/help-scraper is scraping GraphQL schemas

Tags: sqlite, my-talks, twitter, datasette, litestream, ben-johnson, geoffrey-litt

Quoting Ben Johnson

2021-02-11T20:50:21+00:00

Litestream runs continuously on a test server with generated load and streams backups to S3. It uses physical replication so it'll actually restore the data from S3 periodically and compare the checksum byte-for-byte with the current database.

— Ben Johnson

Tags: testing, litestream, ben-johnson

Why I Built Litestream

2021-02-11T19:25:26+00:00

Why I Built Litestream

Litestream is a really exciting new piece of technology by Ben Johnson, who previously built BoltDB, the key-value store written in Go that is used by etcd. It adds replication to SQLite by running a process that converts the SQLite WAL log into a stream that can be saved to another folder or pushed to S3. The S3 option is particularly exciting—Ben estimates that keeping a full point-in-time recovery log of a high write SQLite database should cost in the order of a few dollars a month. I think this could greatly expand the set of use-cases for which SQLite is sensible choice.

Tags: replication, scaling, sqlite, ben-johnson