Litestream VFS: Instant Point-in-Time Recovery for SQLite Databases

Litestream is an open-source backup and restore system designed for SQLite databases. It runs seamlessly anywhere, providing a robust solution for data durability. Building upon this, Litestream VFS offers an innovative way to interact with SQLite databases directly from object storage, enabling instantaneous point-in-time recovery.

Imagine you have a SQLite database, perhaps storing sandwich ratings, backed up using Litestream to an S3 bucket. On your local machine, after configuring AWS credentials and the S3 path in your environment, you can open SQLite and access this remote database:

$ sqlite3
SQLite version 3.50.4 2025-07-30 19:33:53
sqlite> .load litestream.so
sqlite> .open file:///my.db?vfs=litestream

With Litestream VFS, SQLite operates directly from the backup files stored in S3. You can query it as if it were a local database:

sqlite> SELECT * FROM sandwich_ratings ORDER BY RANDOM() LIMIT 3;
22|Veggie Delight|New York|4
30|Meatball|Los Angeles|5
168|Chicken Shawarma Wrap|Detroit|5

A remarkable feature is that the entire database doesn't need to be downloaded to execute this query. Litestream VFS works by running SQLite live off an object storage URL, integrating as a shared library within your application or the SQLite shell.

Consider a scenario where a production database update goes wrong. For example, an UPDATE statement is executed without a WHERE clause:

sqlite> UPDATE sandwich_ratings SET stars = 1;

This inadvertent action could disastrously set all sandwich ratings to 1 star. However, with Litestream VFS, you can easily revert to a previous state:

sqlite> PRAGMA litestream_time = '5 minutes ago';
sqlite> select * from sandwich_ratings ORDER BY RANDOM() LIMIT 3;
30|Meatball|Los Angeles|5
33|Ham & Swiss|Los Angeles|2
163|Chicken Shawarma Wrap|Detroit|5

This demonstrates querying the database from a specific point in time using relative or absolute timestamps (e.g., '2000-01-01T00:00:00Z'). This is instantaneous Point-in-Time Recovery (PITR), expressed simply through SQL and SQLite pragmas. Litestream VFS simplifies querying production datasets, performing sanity checks against historical data, and conducting quick analyses without the overhead of full database restores or direct server access.

How It Works

Litestream v0.5 incorporates LTX, our custom SQLite data-shipping file format. While earlier versions of Litestream transferred entire raw SQLite pages to and from object storage, LTX efficiently ships ordered sets of pages. Initially developed for LiteFS (a FUSE filesystem for transaction-aware replication), LTX has now been integrated into Litestream to enhance its capabilities without the complexities of FUSE.

A significant advantage of LTX is "compaction." When restoring a database from object storage, the goal is to retrieve only the most recent versions of changed database pages, discarding all intermediate versions. For instance, if a restore requires pages 1, 2, 3, 4, and 5, and the backup data sequence might be 1 2 3 5 3 5 4 5 5, LTX ensures only the latest version of each page (the rightmost occurrence) is retrieved. This drastically accelerates restore operations by skipping redundant pages, which are common in SQLite datasets, especially with autoincrementing primary keys.

LTX compaction is not limited to entire databases; it can also compact sets of LTX files. This is fundamental to how PITR restores function with Litestream.

Litestream employs daily full snapshots, supplemented by "levels" of changesets representing database page groups from progressively smaller time windows. By default, these intervals range from 1 hour at the highest level down to 30 seconds at level 1. Level 0 is a special layer where files are uploaded every second but are retained only until compacted to Level 1.

For a PITR restore, the process begins with the most recent proximal snapshot. Then, the minimal set of LTX files from each level is determined to precisely reach the specified restoration time.

An additional optimization involves LTX trailers, which contain a small index tracking each page's offset within the file (approximately 1% of the LTX file size). By fetching only these index trailers, a comprehensive lookup table of every database page can be constructed. Modern object storage providers support fetching slices of files, allowing for individual page reads directly from S3.

How It’s Implemented

SQLite's "VFS" (Virtual File System) interface provides a plugin mechanism to abstract the underlying operating system interactions. Litestream VFS leverages this interface to provide its unique functionality.

Crucially, Litestream was designed to operate alongside unmodified SQLite applications, acting as a standard Unix program for backups and PITR restores. However, to enable fast, PITR-style queries directly from S3, Litestream's VFS module must be loaded and registered. This is the only change required; Litestream VFS acts as a plugin, not a replacement for your existing SQLite library.

The VFS library only handles the read operations of SQLite. The "write" side is still managed by Litestream running as a normal Unix program. The VFS subclasses just enough functionality to locate LTX backups and facilitate queries. When SQLite requires reading a page into memory, it issues a Read() call through the Litestream VFS library. The traditional byte offset in this call is an illusion. Instead, Litestream VFS uses the page size and requested page number to perform a lookup on its pre-built page index. This index provides the remote filename, the "real" byte offset within that file, and the page size. This information allows Litestream VFS to utilize the S3 API's Range header to download precisely the required data block.

To minimize S3 calls, Litestream VFS incorporates an LRU (Least Recently Used) cache. This is highly effective because most databases exhibit a "hot" page phenomenon, where a small percentage of pages (e.g., inner branch pages, leftmost leaf pages) are frequently updated and queried.

Furthermore, Litestream VFS can poll the S3 path and incrementally update its index because Litestream performs backups into the L0 layer every second. This capability results in a near-realtime replica of the database. A significant benefit is that the entire database does not need to be streamed to your machine before use.

Litestream maintains backup files for every state of your database, with single-second resolution, for as long as desired. If a DELETE statement is executed without a WHERE clause, restoring the database state to an hour, day, or week ago is simply a matter of adjusting the LTX indices managed by Litestream. This ability to query databases without fully fetching them offers rapid startup times, which is invaluable in an era of ephemeral servers and cloud-native applications. If your database is backed up to object storage with Litestream, you can always quickly issue a query regardless of your environment.

A core principle behind Litestream is to deliver powerful features like instant PITR reading directly from object storage while maintaining a simple, understandable underlying mechanism. Litestream is a robust solution for production use, even relied upon for critical components of Fly.io's APIs. Its design principles aim for simplicity, allowing users to grasp its fundamentals easily, primarily because the heavy lifting is expertly handled by SQLite itself.