Again with the sandwiches: assume we’ve got a SQLite database of sandwich ratings, and we’ve backed it up with Litestream to an S3 bucket. Now, on our local host, load up AWS credentials and an S3 path into our environment. Open SQLite and:
Overview
Litestream VFS is a new SQLite plugin that enables querying databases directly from S3-compatible object storage without downloading the entire database. Built on the LTX file format, it supports instant point-in-time recovery (PITR) through SQL pragmas and can function as a near-realtime read replica by polling backup files, all while maintaining Litestream's philosophy of working alongside unmodified SQLite applications.
What You'll Learn
1
How to query a remote SQLite database directly from S3 using Litestream VFS without downloading the entire database
2
How to perform instant point-in-time recovery (PITR) using SQL pragmas with relative or absolute timestamps
3
Why LTX compaction drastically accelerates database restores by skipping redundant page versions
4
How Litestream VFS implements a near-realtime read replica by polling S3 backup files
5
How the SQLite VFS plugin interface abstracts the OS layer to enable remote page reads via S3 Range requests
Prerequisites & Requirements
- Basic understanding of SQLite databases and SQL queries
- Familiarity with object storage concepts (S3 buckets, file paths)
- Litestream v0.5 or later configured with S3-compatible object storage
- AWS credentials and S3 path configured in environment
- Understanding of database backup and restore concepts(optional)
Key Questions Answered
How does Litestream VFS let you query a SQLite database without downloading the whole file?
Litestream VFS fetches only the LTX index trailers (about 1% of each LTX file) from S3 to build a lookup table of every database page. When SQLite needs a specific page, the VFS uses S3 Range header requests to download only that exact block. An LRU cache stores frequently accessed 'hot' pages, minimizing redundant S3 calls.
What is LTX compaction and how does it speed up Litestream restores?
LTX compaction eliminates redundant database page versions from backup sets. When restoring, instead of processing every intermediate version of a page (e.g., five versions of page 5), the algorithm reads backwards from the end of the sequence, keeping only the most recent version of each page. This is especially effective for tables with auto-incrementing primary keys, which generate many intermediate page writes.
How do you perform point-in-time recovery with Litestream VFS?
After loading the Litestream VFS shared library and opening a database with the litestream VFS, use the SQL pragma `PRAGMA litestream_time = '5 minutes ago'` with either relative timestamps or absolute ones like `2000-01-01T00:00:00Z`. Litestream then starts from the most proximal snapshot and determines the minimal set of LTX files from each compaction level to reconstruct the database at that exact point in time.
Does Litestream VFS replace SQLite or require application changes?
No, Litestream VFS is a plugin that works with your existing SQLite installation — it is not a new version of SQLite. You load it as a shared library (`.load litestream.so`) and open databases with the `?vfs=litestream` parameter. The original Litestream Unix program still handles the write side; the VFS handles only the read side. You don't have to use VFS to use other Litestream features.
How does Litestream's tiered backup compaction system work?
Litestream takes daily full snapshots and maintains multiple levels of changesets. L0 files are uploaded every second but only retained until compacted to L1. Level 1 uses 30-second intervals, scaling up to 1-hour intervals at the highest level. For PITR restores, the system starts from the nearest snapshot and selects the minimal set of LTX files from each level to reach the target timestamp.
Can Litestream VFS work as a near-realtime database replica?
Yes. Because Litestream backs up to the L0 layer once per second, the VFS code can poll the S3 path and incrementally update its page index. This creates a near-realtime replica that doesn't require streaming the entire database to your machine first. The incremental index update means only new or changed page references need to be processed.
What is the SQLite VFS interface and how does Litestream use it?
The VFS (Virtual File System) is SQLite's plugin interface for abstracting the bottom-most OS layer. Every SQLite installation already uses a default VFS module. Litestream overrides only the read-related methods: when SQLite issues a Read() call with a byte offset, the VFS translates this into a page number, looks up the real location in its index, and fetches the page from S3 using Range requests.
What is LTX and how does it relate to Litestream and LiteFS?
LTX is Fly.io's SQLite data-shipping file format that ships ordered sets of pages rather than raw individual pages. It was originally built for LiteFS, which uses a FUSE filesystem for transaction-aware replication. Litestream v0.5 integrates LTX to enable compaction, efficient PITR restores, and the new VFS functionality, all without requiring FUSE.
Key Statistics & Figures
LTX index trailer size relative to LTX file
~1%
Each LTX trailer occupies about 1% of its LTX file, enabling lightweight index building without downloading full backup files
L0 backup frequency
1 second
Litestream uploads L0 files every second, enabling near-realtime replica functionality
L1 compaction interval
30 seconds
Default time interval for the lowest non-ephemeral compaction level
Highest level compaction interval
1 hour
Default time interval at the highest compaction level
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Sqlite
Core embedded database that Litestream backs up and the VFS plugin extends
Backup/Replication Tool
Litestream
Continuous backup and restore system for SQLite databases to object storage
File Format
Ltx
SQLite data-shipping file format that enables ordered page sets, compaction, and efficient PITR
Object Storage
S3
Remote storage backend for Litestream backups, queried directly via Range requests by the VFS
Distributed Filesystem
Litefs
FUSE-based filesystem for transaction-aware SQLite replication; original context where LTX was developed
Plugin Interface
Sqlite Vfs
SQLite's Virtual File System plugin API used to implement remote page reads from object storage
Key Actionable Insights
1Use Litestream VFS for quick ad-hoc queries against production data without SSHing into production servers. Load the shared library, point it at your S3 backup path, and run SQL queries directly from your development machine. This eliminates the need for full database restores just to check a few values.Particularly useful for debugging production issues or running sanity checks where you need to query live data without touching production infrastructure.
2Leverage PITR pragmas with relative timestamps like '5 minutes ago' for instant disaster recovery verification. When accidental data modifications occur (e.g., missing WHERE clause on UPDATE/DELETE), you can immediately query the pre-disaster state to assess damage and plan recovery.This is instantaneous compared to traditional full database restores, making it practical for incident response and data auditing workflows.
3Understand that Litestream VFS handles only the read side of SQLite operations. The standard Litestream Unix daemon still manages writes and backups. This separation means you can adopt VFS for read-only use cases (analytics, debugging, replicas) without changing your write path at all.This architectural decision means VFS adoption is incremental — you can start using it alongside your existing Litestream setup without any migration risk.
4Take advantage of LTX compaction's tiered architecture when planning backup retention policies. The L0 layer captures changes every second but compacts quickly to L1 (30-second intervals), while higher levels use progressively larger time windows up to 1 hour. Configure retention based on your PITR precision requirements at different time horizons.The tiered approach means you get second-level granularity for recent changes while keeping storage costs manageable for longer-term retention through automatic compaction.
5Consider using Litestream VFS as a near-realtime read replica by having it poll the S3 path for L0 updates. Since Litestream backs up once per second, the VFS can incrementally update its page index to stay current — without streaming the entire database to the replica machine.This is especially valuable for ephemeral or edge compute environments where spinning up lightweight read replicas quickly matters more than having a full local copy.
Common Pitfalls
1
Forgetting a WHERE clause on UPDATE or DELETE statements in production, which modifies all rows in a table instead of the intended subset. The article demonstrates this with `UPDATE sandwich_ratings SET stars = 1` accidentally setting every sandwich to 1 star.
Litestream VFS with PITR pragmas provides a safety net by allowing you to instantly query the pre-disaster state, but prevention through careful SQL review remains important.
2
Assuming Litestream VFS replaces your entire SQLite setup or requires application-level changes. The VFS is a read-only plugin that works alongside the existing Litestream daemon — it does not handle writes, and your existing backup workflow remains unchanged.
The article emphasizes that Litestream is designed to work with unmodified SQLite applications. VFS is an optional addition for read-side features like PITR queries and remote replicas.
3
Attempting to use Litestream VFS for write operations. The VFS subclasses only enough of the SQLite VFS interface to handle reads — finding LTX backups and issuing queries. The write side is still handled by the Litestream Unix program running alongside your application.
Understanding this read/write separation is critical for correct deployment architecture. Writes must go through the primary database with Litestream running, while VFS handles remote reads.
Related Concepts
Point-in-time Recovery (pitr)
Sqlite Vfs Plugin Interface
Object Storage Range Requests
Lru Cache
Database Page-level Replication
B-tree Page Structure
Fuse Filesystem
Data Compaction Algorithms
Incremental Backup Strategies
Read Replicas
Ephemeral Compute Environments