10 ms·
Show HN: Distributed SQLite on FoundationDB
Hello HN! I'm building mvsqlite, a distributed variant of SQLite with MVCC transactions, that runs on FoundationDB. It is a drop-in replacement that just needs an `LD_PRELOAD` for existing applications using SQLite.
I made this because Blueboat (https://github.com/losfair/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!
- LAC-Tech 4y agoSo the idea is that a small business could start on SQLite, and then switch over to this when it's time to scale, without re-writing it in the Postgres dialect? Regardless, it's very very cool. Would love to see it get turned into a product.
- deleted 4y ago[deleted]
- mping 4y agoFDB is a nice piece of technology if you know how to go around its constraints. Congrats on the project.
- faizshah 4y agoI love the idea of distributed SQLite but I’m having a hard time understanding which parts of FoundationDB and which parts of SQLite are available in this implementation. I’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.
- losfair 4y agoYes it's correct! mvsqlite integrates as a custom VFS underlying SQLite's query engine, and SQLite ecosystem stuff can be used on top of it.
- robertsdionne 4y ago"The SSD storage engine stores the data in a B-tree based on SQLite." XD https://apple.github.io/foundationdb/architecture.html#storage-servers https://apple.github.io/foundationdb/architecture.html#stora...
- leetrout 4y agoPer your link they are leaving sqlite. In the upcoming FoundationDB 7.0 release, the B-tree storage engine will be replaced with a brand new Redwood engine.
- zwass 4y ago(I'm on the osquery steering committee) 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!
- deleted 4y ago[deleted]
- learndeeply 4y ago> But a group of N sqlite databases is an N-writer database. And mvsqlite provides the necessary mechanisms to do serializable cross-database transactions without additional overhead. I'm confused, are these databases planned to be replicated? Or is it expected for the databases to have separate schemas?
- losfair 4y agoReplication is handled by FDB so you don't need to care about it on the application level. These databases can contain partitioned data of your application, like one DB per user, so that a transaction on only user A and another one on only user B won't conflict.
- metadat 4y agoThis sounds really cool, do you have any source code in a workable state yet or is this project still in the formulative ideation phase? If there's anything concrete so far, I'd love to take a look and/or try it out!
- losfair 4y agoIt already works! There are steps to try it in readme.
- metadat 4y agoNice, I dug a bit and found the git repository: https://github.com/losfair/mvsqlite 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).
- dilyevsky 4y agoI think foundationdb uses sqlite as its tablet kv engine? It’s sqlite all the way down
- leetrout 4y agoThey are leaving sqlite. 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#storage-servers https://apple.github.io/foundationdb/architecture.html#stora...
- dilyevsky 4y agoInteresting… doesn’t seem to have a lot of docs on this. Surprised they didn’t just go along with RocksDB
- saghm 4y ago> Surprised they didn’t just go along with RocksDB 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.
- richieartoul 4y agoIt supports RocksDB also, although I think that feature is a bit more experimental.
- transactional 4y agoThere's one set of folk working on a btree, and another set of folk are now working on a RocksDB storage engine. 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.
- fnord123 4y agoComdb2: distributed sqlite: http://comdb2.org/ http://comdb2.org/
- wener 4y agoA better link https://github.com/bloomberg/comdb2 https://github.com/bloomberg/comdb2
- cmrdporcupine 4y agoIf you've written your own multi-versioning, what does FDB bring to the table that you couldn't have gotten out of other distributed but non-transactional KV stores? E.g. Cassandra, etc. Isn't there an overhead to the MVCC aspect of FDB? And it sounds like you've had to jump hoops around things to get past its duration and size limits, as well...
- LAC-Tech 4y agoCassandra is a column store, surely that would be a very un-natural mapping for a SQL Engine backend to use? An orthogonal mapping, to be precise!
- _benedict 4y agoCassandra is not column oriented. It coined the phrase “wide column” (which has caused untold confusion), but this just meant row-oriented without a schema and unlimited numbers of dynamic “columns” in each row. This is no longer true anyway, it is simply row-oriented now in the normal sense.
- liuliu 4y agoThis really, IMHO (as someone implements things on top of SQLite too https://dflat.io https://dflat.io) pushes SQLite too far as the implementation of cross-db transactions have some big issues: https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html (the number of attached databases cannot exceed 10 or 125 (if you compile your own)) https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html (in WAL mode, there is no transactional guarantee for cross database transactions (atomic per database, but not cross database))
- losfair 4y agomvsqlite doesn't rely on the SQLite WAL or rollback journal for safety. Actually, it enforces journal_mode=memory! The atomic commit semantics is guaranteed by FDB instead. 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.
- liuliu 4y agoI am a bit out of depth here for obvious reasons (I work above SQLite, not under SQLite). But from my understanding, `journal_mode=memory` simply means it is in rollback mode, but rollback pages are not maintained on disk. Therefore, the modifications from a transaction will be applied in place in these pages. If the process that runs SQLite crashes in the middle of a transaction, these details will be leaked unless you know how to rollback these pages (through rollback journal, you can alternatively found these from FDB, but you should have no idea which one corresponding to which transaction?). 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?)).
- losfair 4y ago> Therefore, the modifications from a transaction will be applied in place in these pages. 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.
- Something1234 4y agoHaven't you heard of bedrock by expensify?
- elktea 4y agoblockchain? different solution.
- endisneigh 4y agoOne thing I've been curious about with FDB (need to find time to try this myself) is using FDB as a way to easily implement replication with consistency. 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?
- losfair 4y agoI think Kafka is enough for this use case? FDB should work too but sounds like overkill.
- endisneigh 4y agoRight, my question is more around replicating any stateful system. Isn’t Kafka not partitionable?
- gigatexal 4y agoAny chance this could get Jepsen tested? I’d donate to make that happen.
- reichardt 4y agoDoes this work similar to rqlite or dqlite from a usage standpoint, or does it solve a different use case?
- Multicomp 4y agoGood work! I don't understand the innards of this at all, but I love the design. 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.
- Witness13 4y ago
- ranjanprj 4y agoI think this is a great idea, and probably sqlite bigger sibling PostgreSQL adopts it one day.