https://www.sqlite.org/withoutrowid.html
In particular, they say:
> WITHOUT ROWID tables will work correctly (that is to say, they provide the correct answer) for tables with a single INTEGER PRIMARY KEY. However, ordinary rowid tables will run faster in that case. Hence, it is good design to avoid creating WITHOUT ROWID tables with single-column PRIMARY KEYs of type INTEGER.
This has not proven correct in my testing, but perhaps other applications are different. IMO, you _should_ use WITHOUT ROWID on your tables with single-column PRIMARY KEYs of type INTEGER. With a 100ms seek time on S3 requests it's obvious that the WITHOUT ROWID table with monotonic IDs is benefitting from spatial locality and rowid tables are not. I suspect when giving their advice, they are not considering queries of contiguous ranges of ids (which happens naturally more often than you'd think when JOINs are involved).
Presumably, it is faster on a saner filesystem.
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.
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!
Protip: Use WITHOUT ROWID with monotonic IDs if you don't want your rows sprayed randomly around the file! That one change was the difference between SQLite-on-S3 being unusably slow and being fast enough. WITHOUT ROWID tables let you manage the physical clustering.