News for hackers. Stuff that matters.
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