News for hackers. Stuff that matters.
Re:SQLite Disk Page Explorer by electroly on Thursday February 06, 2025 @02:35PM (#42965714)
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.

Re:SQLite Disk Page Explorer by koeng on Thursday February 06, 2025 @02:57PM (#42965903)
What are the disadvantages of using WITHOUT ROWID?
Re:SQLite Disk Page Explorer by electroly on Thursday February 06, 2025 @03:08PM (#42966006)
The 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.
Re:SQLite Disk Page Explorer by srcreigh on Thursday February 06, 2025 @06:15PM (#42967465)
SQLite can give you an auto incremented ID on a without row id table. You just need to have a unique index on the id.
Re:SQLite Disk Page Explorer by srcreigh on Thursday February 06, 2025 @06:16PM (#42967469)
The disadvantage is that you’ll never look at Postgres the same way again.
Re:SQLite Disk Page Explorer by tehlike on Friday February 07, 2025 @07:21AM (#42971771)
Yeah, one of the things postgres is missing. Yugabyte seems to be addressing this thankfully
Re:SQLite Disk Page Explorer by srcreigh on Friday February 07, 2025 @11:44AM (#42974696)
Yep looks like it. They don't seem to use Postgres storage layer at all.
Re:SQLite Disk Page Explorer by ustad on Thursday February 06, 2025 @02:38PM (#42965735)
Do you have a url describing your protip?
Re:SQLite Disk Page Explorer by electroly on Thursday February 06, 2025 @02:48PM (#42965830)
The 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

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).

Re:SQLite Disk Page Explorer by benediktwerner on Thursday February 06, 2025 @03:01PM (#42965950)
I 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.

Re:SQLite Disk Page Explorer by electroly on Thursday February 06, 2025 @03:09PM (#42966024)
Spatial 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.
Re:SQLite Disk Page Explorer by maples37 on Thursday February 06, 2025 @02:41PM (#42965763)
https://news.ycombinator.com/item?id=42965714 will work if you're just bookmarking for the future
Re:SQLite Disk Page Explorer by ncruces on Thursday February 06, 2025 @04:33PM (#42966712)
Is this source available?

Read-only or read-write? And if writeable, what's concurrency like?

Re:SQLite Disk Page Explorer by electroly on Thursday February 06, 2025 @05:04PM (#42966961)
It 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

Re:SQLite Disk Page Explorer by simonw on Thursday February 06, 2025 @06:30PM (#42967563)
Wow, 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/

Re:SQLite Disk Page Explorer by electroly on Friday February 07, 2025 @10:41AM (#42973910)
I 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.

Re:SQLite Disk Page Explorer by ncruces on Saturday February 08, 2025 @11:48AM (#42984205)
The problem with a block/page based solution (block per page is a bad idea, IMO, because S3/GCS are cost/performance optimized for around megabyte objects) is consistency: ensuring a reader sees a consistent set of blocks, i.e. all of the same database version.

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.

Re:SQLite Disk Page Explorer by 2shortplanks on Friday February 07, 2025 @02:23AM (#42970296)
I’m really curious about the number of requests you end up making. I’d imagine that this would get expensive fast in a web app - AWS charge $0.40 per million read requests, so if you’re seeing ten request to your app server a second and that ends up making a hundred requests to S3, that’s going to come in at at about $34 a day!
Re:SQLite Disk Page Explorer by electroly on Friday February 07, 2025 @10:18AM (#42973622)
We don't use it for anything interactive or user-facing, it's too slow for that. It's for some large+cold data that is used in batch analysis jobs that need to query out a small subset of the data using relatively complicated indexed queries. We probably don't get more than a few thousand queries per day through the system which is no big deal and basically free, but migrating this cold data to S3 allowed me to save a few terabytes in our expensive SQL Server cluster which is a big deal.
Re:SQLite Disk Page Explorer by ncruces on Saturday February 08, 2025 @08:35AM (#42982852)
The read only bit is definitely a relatively simple afair.

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!

Re:SQLite Disk Page Explorer by yencabulator on Friday February 14, 2025 @03:48PM (#43052917)