41–50 of 55 posts
this is a great project, does it support wasm? i want to use it in browser with sqlite wasm.
Nice set of experiments! I appreciate that you're running benchmarks on real object storage setups to validate rapid design variations. (Meta-note: I love how agents have recently made this kind of experimental-research work possible with much less human time investment.) I've been doing some experiments of my own in a relatively similar space, also focusing on S3/Tigris-backed SQLite on ephemeral compute, also with…
First: your prefetch solution is precisely what I have in the roadmap for the next level of optimization: B-tree introspection at the page-child level not the table level. I haven't launched it yet as I only realized the potential in the past couple days and I wanted to focus on stability before showing this to folks. I am 100% going to try to use sqlite-prefetch approach in that experiment. I'm curious what kind of results you're seeing.
On write amplification: you're right, re-uploading an entire page group when one page is dirty feels inefficient. This tradeoff is intentional because turbolite believes all PUTs are equal, and it is aimed at optimizing for queries on cold databases with bursty reads, not write-heavy workloads (though writes work too). Checkpoints are ideally infrequent due to upload latency, and the page group sizes are tunable (default 256 64KB pages = 16MB uncompressed, a few MB compressed to S3). For the use case I'm targeting, the read locality wins dominate. That being said, turbolite does let the user choose when to checkpoint locally (durable on disk) vs to S3 (atomic commit in case of disk crash or new compute).
On LTX temporal locality vs page-group spatial locality: agreed, they're different design goals. LTX optimizes for transaction-aware features like PITR. turbolite optimizes for cold query speed from object storage. You could imagine a system that does both (WAL shipping for durability + grouped pages for reads), which is roughly where my roadmap goes. In fact, WAL + immutable page groups on checkpoints gives a much faster restore time than Litestream's snapshots+WAL if only because uploading the entire snapshot on each commit is slow. I'm starting to explore embedded WAL shipping in my walrust project https://github.com/russellromney/walrust.
On frontrun vs reactive: I have very specific benchmarks for this I think you will be intrested in. The tiered-bench binary has a --plan-aware flag that toggles between prefetch schedule (reactive, similar in spirit to your sibling detection) and frontrun (EQP-based). Look at the benchmark/README.md for detail. On 100K rows, local to Tigris, EQP is:
- who-liked (one-to-many JOIN): 4.4x faster cold, 1.4x with interior cached
- scan + filter: 2.9x faster cold
- indexed filter: 2.9x faster cold
- point lookup + JOIN: 1.8x cold
- simple queries (mutual friends): 1.3x, less to gain
The wins are largest on cold index lookups and SCANs where reactive prefetch has to discover the scan through sequential misses. Frontrun knows the full plan upfront and fires everything in parallel. For single-table scans, reactive catches up quickly after 1-2 cache misses, so the gap is smaller.
Finally - I included a query tuner CLI in the repo that lets you compare different prefetch aggression levels for a given query on given data. You may be interested in using this.
It's great. Well done. 250ms is slow for a DB query though but for what you are doing and how you donit it is surprisingly fast.
- Neon startup is 500ms+
- a Fly Machine with a SQLite database on a volume takes 150ms+ to start up at minimum
- restoring a db from S3 with Litestream can take multiple seconds depending on how long from the last snapshot and how large the database is
- downloading a whole .sqlite file from S3 can be faster than Litestream restore but you still have to download the whole db file.
- One similar option is, you could save each table in a separate database and only download the db files for tables you need for a given query, then ATTACH. But this is an awkward setup even though it's simple
Sub 250ms for cold queries from S3 is impressive, but curious about the consistency of those numbers. Are you doing any prefetching of table schemas or statistics? With geographic datasets we often see huge variance in S3 latency depending on object size and region - a 10Mb spatial index file might take 400ms to fetch while smaller lookup tables stay under 100ms.
this is a great project, does it support wasm? i want to use it in browser with sqlite wasm.
Not today, but the architecture isn't fundamentally incompatible. The page grouping and seekable compression would translate well to browser fetch + range GETs. It would need a new storage backend targeting OPFS/fetch instead of S3/disk. I'm happy to discuss more if you'd like to open a Github issue - abstracting the storage API seems like a decent idea in itself.
Ovais - co-founder of Tigris here. This is very cool. I have been thinking about embedded databases running on Tigris. Specially from an agent perspective, agents can suspend and continue their sessions. Would love to collaborate.
Happy to hear from you, I'm a delighted customer. In fact I am building a database company on top of Tigris focused on exactly that use case. I'll reach out on X if that works.
It's great. Well done. 250ms is slow for a DB query though but for what you are doing and how you donit it is surprisingly fast.
Thanks! yeah it's definitely slow, but compared to other options it's quite fast: - Neon startup is 500ms+ - a Fly Machine with a SQLite database on a volume takes 150ms+ to start up at minimum - restoring a db from S3 with Litestream can take multiple seconds depending on how long from the last snapshot and how large the database is - downloading a whole .sqlite file from S3 can be faster than Litestream restore but…