Is this source available?
Read-only or read-write? And if writeable, what's concurrency like?
Read-only or read-write? And if writeable, what's concurrency like?
There are some open source codebases that do similar things, but take it all the way with write support: https://github.com/uktrade/sqlite-s3vfs
Suggestion: since having multiple writers will corrupt the database, it's worth investigating if the recent (November 2024) S3 conditional writes features might allow you to prevent that from accidentally happening: https://simonwillison.net/2024/Nov/26/s3-conditional-writes/
I see some challenges, though. Can we implement a reader-writer lock using conditional writes? I think we need one so that readers don't have to take the writer lock to ensure they don't read some pages from before an update happening concurrently and some pages from after. If it's just a regular lock, oops--now we only allow a single reader at a time. I wonder if SQLite's file format makes it safe to swap the pages out like this and let the readers see a skewed list of pages and I'm just worrying too much.
A different superpower we could exploit is S3 object versions. If you require a versioned bucket, then readers can keep accessing the old version even in the face of a concurrent update. You just need a mechanism for readers to know what version to use.
Under concurrency, but also under a crash.
Similarly, the big issue with locks (exclusive or reader/writer) is not so much safety, but liveness.
It seems a bit silly to need to have (e.g.) rollback journals on top of object storage like S3.
Similarly, the problem with versioning is that for both S3/GCS you don't get to ask for the same version of different objects: there's no such thing.
So you'd need some kind of manifest, but then you don't need versioning, you can use object per version.
Ideally, I'd rather use WAL mode with some kind of (1) optimized append for the log, and (2) check pointing that doesn't need to read back the entire database. I can do (1) easily on GCS, but not (2); (2) is probably easier in S3, not sure about (1).
My Go driver supports it, through a combination of a VFS that can take any io.ReaderAt, and something that implements a io.ReaderAt with http Range requests (possibly with some caching).
https://github.com/ncruces/go-sqlite3/blob/main/vfs/readervf...
This is compatible with S3, but also GCS, and really any thing that supports http Range for GET.
I've wanted to implement something writeable with concurrency. Not that it'll be great, but because people tend to do worse anyway (like run SQLite on top of something like GCS FUSE, and we can definitely do better than that), so why not?
I've also worked with GCS concurrency features and locks, so I'd like to try and do better. But it's hard to be compatible with multiple cloud providers.
So thanks for that!