Show HN: Distributed SQLite on FoundationDB (github.com)
I made this because Blueboat (https://github.com/losfair/blueboat) needs a native SQL interface to persistent data. Apparently, just providing a transactional key-value store isn’t enough - it is more easy and efficient to build complex business logic on an SQL database, and it seems necessary to bring a self-hostable distributed SQL DB onto the platform. Since FoundationDB is Blueboat’s only stateful external dependency, I decided to build the SQL capabilities on top of it.
At its core, mvsqlite’s storage engine, mvstore, is a multi-version page store built on FoundationDB. It addresses the duration and size limits (5 secs, 10 MB) of FDB transactions, by handling multi-versioning itself. Pages are fully versioned, so they are always snapshot-readable in the future. An SQLite transaction fetches the read version during `BEGIN TRANSACTION`, and this version is used as the per-page range scan upper bound in future page read requests.
For writes, pages are first written to a content-addressed store keyed by the page's hash. At commit, hashes of each written page in the SQLite transaction is written to the page index in a single FDB transaction to preserve atomicity. With 8K pages and ~60B per key-value entry in the page index, each SQLite transaction can be as large as 1.3 GB (compared to FDB's native txn size limit of 10 MB).
mvsqlite is not yet "production-ready", since it hasn’t received enough testing, and I may still have a few changes to make to the on-disk format. But please ask here if you have any questions!
46 comments
[ 4.9 ms ] story [ 117 ms ] threadI’m guessing virtual table extensions work with this since you’re just replacing the storage engine? So we could in theory use FTS5 and even OSQuery and other extensions right?
However since this is using FoundationDB I’m also guessing we can’t use this as a serverless embedded DB since since you’ll probably need a foundation db cluster to use this. Is that right?
So if I understand correctly this is a SQLite query engine on top of FoundationDB with distributed transactions and we can theoretically use SQLite ecosystem stuff like FTS5 and datasette on top of it.
In the upcoming FoundationDB 7.0 release, the B-tree storage engine will be replaced with a brand new Redwood engine.
In theory osquery is "just" virtual tables, but in practice there's quite a bit more that would probably make attaching it to mvsqlite. If you have a use case in mind I would love to know!
I'm confused, are these databases planned to be replicated? Or is it expected for the databases to have separate schemas?
If there's anything concrete so far, I'd love to take a look and/or try it out!
https://github.com/losfair/mvsqlite
Would be great to add this link to the body of your story to make it easy for HNers to get to the thing :)
If no longer editable, consider emailing moderator Dang (hn@ycombinator.com).
In the upcoming FoundationDB 7.0 release, the B-tree storage engine will be replaced with a brand new Redwood engine.
https://apple.github.io/foundationdb/architecture.html#stora...
Given how often they're butting heads publicly, maybe Apple didn't want to use something developed by Facebook? It probably wouldn't normally be a concern due to the licenses of each, but it's possible that corporate politics might still be relevant.
The original preference away from RocksDB was that it doesn't play well with deterministic simulation. Any code included into FDB needs to be able to be able to run with coroutines (strongly preferably stackless ones, though sqlite's btree has a stackful coroutine shimmed under it). RocksDB is definitely not written to support coroutines, and thus trying to use it anyway results in sacrificing developers' abilities to dig into failures.
Redwood has a couple design decisions that would make it a poor general purpose btree, but a great one for FoundationDB. But RocksDB will still have write and space amplification advantages.
Regardless, it's very very cool. Would love to see it get turned into a product.
The limit on max number of open DBs looks low, but maybe short-lived attaches can be done? Like attach just before the transaction and detach after commit. This should be enough for common transactions involving just a few DBs.
More over, without WAL, SQLite to the best of my knowledge would require read-lock on every read as well, practically becomes not only single-writer, but also single-reader (you probably can break the global lock (that through VFS layer)? But it doesn't make this thing safe (i.e. you will have things in a transaction leaked?)).
With mvsqlite it is applied to the transaction's own snapshot of the database. Changes are not visible globally until transaction commit. That's what we get from page-level MVCC.
> More over, without WAL, SQLite to the best of my knowledge would require read-lock on every read as well
Reads are also MVCC. Data is fetched from a consistent and clean snapshot of the database, without uncommitted data.
What happen if there are page conflicts during the write? The transaction fails and you retry? Does SQLite have a good way of of signalling that this is the case (vs for example a network failure or other write failures).
For applications targeting upstream SQLite, mvsqlite enables pessimistic locking by default - when a transaction is promoted to EXCLUSIVE, it acquires a one-minute lock lease from mvstore. At this point we have the chance to fail gracefully and return a "database is locked" error if multiple clients want to acquire lock on the same DB. This is a best-effort mechanism to prevent conflict on commit (which causes the process to abort).
A future feature is "MVCC-aware clients". Compatible clients can opt-in to full, optimistic MVCC, and avoid pessimistic locking. After `COMMIT`, the client should call a SQLite custom function provided by mvsqlite to check whether the commit actually succeeds, and retry if not.
I wonder if SQLite should expose one layer up (on the actual pager level). A lot of attempts (such as yours, or LiteFS) try to construct the pager / txn concept from VFS layer as well, which seems to be one layer lower than actually is.
Update:
But yeah, mvsqlite as of today seems to be tied to one sqlite per mvstore so it's attaching databases to the sqlite instance is the only way to deal with multi databases. So, it will naturally be affected by SQLITE_MAX_ATTACHED.
Perhaps a potential big idea would be to have a sqlite instance per database and a "proxy" layer above sqlite instances to (1)obtain/set a global transaction ID per multi-db transaction and (2)redirect queries to each involved sqlite databse, while the serializability is guaranteed in the FoundationDB.
For example: You have 5 Postgres instances. You send the query "SELECT * FROM TABLE" to FDB, you want the result of this from any of the 5 Postgres's (first to return wins). When you insert, you want to insert into all 5 and make sure that all 5 have actually finished the transaction before telling the client.
Seems simple enough to implement via FDB?
Iirc when I first read through the book designing data centric applications, the author talked about a lot of trade-offs for data storage and replication and network connectivity issues.
the impression I left with was for my particular application that foundation DB was the best option I had for my wild dreams of web scale popularity.
the current data persistence layer I use is sqlite, which means if I use mvsqlite, that only makes it easier for me to try to use foundation DB for my someday no doubt irresistible web application.