5 ms·
It was revelatory to write an SQLite virtual filesystem module for a storage layer with extremely high latency (S3). Every seek matters. This would have helped;
by electroly 2y ago
It was revelatory to write an SQLite virtual filesystem module for a storage layer with extremely high latency (S3). Every seek matters. This would have helped; I looked at the reads executed by SQLite and tried to intuit what was going on inside the file.
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.
- ustad 2y agoDo you have a url describing your protip?
- maples37 2y agohttps://news.ycombinator.com/item?id=42965714 https://news.ycombinator.com/item?id=42965714 will work if you're just bookmarking for the future
- electroly 2y agoThe only thing I really have is the SQLite docs, and some of the advice in here does not match my own testing and I would recommend the opposite. https://www.sqlite.org/withoutrowid.html 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).
- benediktwerner 2y agoI mean, you might still be right but I'd rather suspect that they didn't consider somebody running SQLite over S3 with 100ms latency ... Presumably, it is faster on a saner filesystem.
- electroly 2y agoSpatial locality matters on all storage systems, the timescales are just different magnitudes. The filesystem cache and making the most from each page you read from disk still makes a difference, you're just not measuring the delay with your wristwatch when it's local SSDs.
- koeng 2y agoWhat are the disadvantages of using WITHOUT ROWID?
- electroly 2y agoThe main advantage is also its biggest disadvantage: you CAN control the clustering of the rows... and you MUST control the clustering of the rows. SQLite won't give you an auto-incremented ID on a WITHOUT ROWID table. You have to assign the IDs yourself and SQLite won't help you.
- srcreigh 2y agoSQLite can give you an auto incremented ID on a without row id table. You just need to have a unique index on the id.
- srcreigh 2y agoThe disadvantage is that you’ll never look at Postgres the same way again.
- ncruces 2y agoIs this source available? Read-only or read-write? And if writeable, what's concurrency like?
- electroly 2y agoIt was for work so I can't share it, but it's read-only and assumes an immutable database which puts it into the realm of a weekend project. I generate the database files in a batch process with regular SQLite, then upload it to S3 and query it using the S3 VFS. If you pull up the S3 API and the SQLite VFS API it's pretty straightforward to see how to glue them together for a simple read-only VFS. I do byte range requests to pull out the pages that SQLite requests, and a readahead cache helps reduce the number of requests. 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 https://github.com/uktrade/sqlite-s3vfs
- simonw 2y agoWow, that thing is really clever. 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/ https://simonwillison.net/2024/Nov/26/s3-conditional-writes/
- electroly 2y agoI think that works, but I think they'll need to use it to implement an external lock rather than coordinating writes to a single object as those comments suggest. They use an object per page, not an object per database, so they have to synchronize updates to multiple objects instead of just one. But a global writer lock is what SQLite does locally, anyway. It's a good idea. 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.
- deleted 2y ago[deleted]