Show HN: Versioning Filesystem for SQLite

by devnull3 • 4 years ago

110 33 4 years ago

koeng• 4 years ago

How would something like this work with litestream? I imagine that the WAL file gets written the same way, but would recovering the database allow for mojo to continue working?

devnull310• 4 years ago

From what I know of litestream, it should be decoupled from VFS implementation. The versions/snapshots are not known to the database.

dkjaudyeqooe• 4 years ago

At present there is no license and to my knowledge it is very restrictive. This will change. I have not made up my mind on the exact license.

There might be something lost in translation but since there is no licence this is a copyrighted work and cannot be used without permission.

raro11• 4 years ago

You are correct

devnull310• 4 years ago

I have changed the license to MIT. Sorry for the wrong impression.

squeaky-clean• 4 years ago

That's exactly how I read that sentence. There was no license, and ironically no license is more restrictive than most licenses.

Once you release code under a certain license you can pretty much never undo that. You can change the license, but the previous version under the previous license will still be valid. So I understand wanting to take time to think about what license you will use.

wruza• 4 years ago

Why ironically? License (aka permit) is a document which gives someone a set of rights. No license no permission, that’s by default.

ctas• 4 years ago

Is there a way to provide the same functionality via extensions instead of VFS? Recently, there's been a few other interesting projects adding backup and replication to SQLite, but they all use VFS. AFAIK only one VFS can be active at a time.

devnull310• 4 years ago

I am the author. Mojo is both an extension and a VFS implementation.

AFAIK only one VFS can be active at a time.

You can specify VFS as a part of connect URI.

phil2942• 4 years ago

This seems great, also in performance, but why would you use this over making plain file copy-based backups, since SQLite is single file based? I'd assume this solution might be preferable because it saves deltas instead of absolute, but the Readme does not say so. If the main advantage here is on-the-fly selecting from different versions, granted, that is unique, even though I cannot really imagine a production use case for this.

So far, I have been using file copies and GFS backup scheme based solutions like Borg, which also does deltas in chunks, compression, and encryption. Perhaps with Borg one should better even backup SQL dumps instead of db files, I don't know.

devnull310• 4 years ago

Only modified pages go into a version data file [1]

[1] https://github.com/sudeep9/mojo/blob/main/design.md

arjvik• 4 years ago

Could you achieve the same by using BTRFS to store the db file?

rahimiali2• 4 years ago

don't you get the same benefit if you version controlled the db file with git? with git, each commit saves a diff from the previous one as a blob. the difference is that in git, in addition to the diffs, you also have to create a working copy of the db, which means you use up at least 2x the storage your system uses. in your implementation, the diff blobs are the live db, which saves you ~2x storage. is that the main benefit?

Lex-20083• 4 years ago

re: backup SQL dumps instead of db files - indeed! In my small experiment (SQL databases in a browser profile, [1]), simple `sqlite3 "$file" .dump | gzip >"$file.sql.gz"` decreased size of files to be backed up about 10 times!

I'm not sure how it compares to Borg's delta- and zstd-compression, though.

[1] http://alexey.shpakovsky.ru/en/minimizing-size-of-browser-pr...

phil2942• 4 years ago

hm but can't you do the same thing (gzipping) with the db itself?

Edit. Regardless of if you maybe even meant that, I made a small test too: A 42 MB db zipped is 8.8M, but its sql dump zipped is 4.9M, so almost half the size. Pretty good already. If you don't just `.dump` but actually output the tables in a consistently sorted manner, the size might go down even more and will enable very efficient delta-ing. Questionable value to effort ratio though...

punnerud• 4 years ago

Could this be used to have multiple writes? where every write have their own sqlite3 and read is a combination of all the files

devnull310• 4 years ago

Not sure I understood your question fully, but if there are multiple versions, then read will be over multiple files but writes go to a single active version.

remram• 4 years ago

Each writer can get its own branch but you would never be able to re-combine them.

Lex-20083• 4 years ago

regarding multiple writes - I think hardest part would be to find an sqlite version where "read is a combination of all the files".

Speaking of multiple writers, I've recently heard about "BEGIN CONCURRENT" feature of SQLite [1] - currently it lives on its separate branch, but I hope they will eventually merge it to the main branch. Not sure if it can be used in your case, but worth mentioning anyway, I think.

[1] https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

devnull310• 4 years ago

(I am the author) Some of the use-cases of mojo I could think of:

1. Rollbacking DDL changes (like create table, adding columns, etc)

2. (My use-case) Multiple readers-single writer. Readers read old versions. Writers do not block readers and vice-versa. Note: this is different than reading+writing in the same version can still happen but with database-level locking.

3. Extension of #2: The older versions read from S3 so that lamda can read.

4. Extension of #3: Readers reading from old immutable snapshots makes the database as a queue of complex changes with indexes and all the benefits of SQL.

5. Backups and replication.

Oh: I have change the license to MIT

quietbritishjim• 4 years ago

For no. 2, WAL mode has this property, why not just use that? Is it for finer control over which version the readers see?

devnull310• 4 years ago

There is a requirement where I need data which existed at timestamp T or version V. So even a WAL needs to be versioned.

TheRealPomax2• 4 years ago

Given that sqlite databases are just files, what is the benefit of this vs. version controlling the database file itself?

operator-name• 4 years ago

The main feature of the fs is versioning/snapshotting. Only one version is writable and all the old versions are immutable.

TheRealPomax2• 4 years ago

Yes, that's what version control does. `git init` followed by git commits every time you update the db would do exactly the same, so: what is the specific benefit of this approach compared to just version controlling the db file itself?

rnavi2• 4 years ago

Would love to learn more on the motivation behind the project

devnull310• 4 years ago

This is a part of a larger system which requires versioning of data. So there is a requirement of reading the data as of timestamp T. The sqlite is supposed to be metadata of that large data. Since the larger data itself is versioned I need metadata to be versioned.

Some of the use-cases are mentioned at: https://news.ycombinator.com/item?id=32629548

rnavi2• 4 years ago

Ah cool. We are defaulting to using sqlite for storing metadata too.

Happy to chat and exchange notes - my email is in the profile.

Show HN: Versioning Filesystem for SQLite

Related Stories