[HN Gopher] Show HN: SQLite disk page explorer
___________________________________________________________________
Show HN: SQLite disk page explorer
Author : QuadrupleA
Score : 136 points
Date : 2025-02-06 18:40 UTC (4 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| Retr0id wrote:
| Neat! It's surprisingly easy to sit down with a copy of the
| sqlite file format docs and start writing code to parse sqlite
| files (like this project does), and I'd strongly recommend it for
| all the same reasons listed in the "Why?" section of the readme
| here.
|
| https://www.sqlite.org/fileformat.html
| luizfelberti wrote:
| Upvoted for using Redbean. I've been using it recently and it has
| been absolutely amazing, the built-in functionality that it has
| exposed through the Lua interface makes it an extensively
| programmable proxy that you can sandbox the crap out of if you're
| familiar with the unixy bits of it
| hoc wrote:
| Did that for a security tool about more than ten years ago. Makes
| you love that tightly designed engine a bit more.
|
| I always thought an explorer or just an base lib would be fun.
| Great to see yours, especially with a MIT license. Tbanks for
| sharing.
| electroly wrote:
| 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 wrote:
| Do you have a url describing your protip?
| maples37 wrote:
| https://news.ycombinator.com/item?id=42965714 will work if
| you're just bookmarking for the future
| electroly wrote:
| 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).
| benediktwerner wrote:
| 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.
| electroly wrote:
| 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.
| koeng wrote:
| What are the disadvantages of using WITHOUT ROWID?
| electroly wrote:
| 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.
| ncruces wrote:
| Is this source available?
|
| Read-only or read-write? And if writeable, what's concurrency
| like?
| electroly wrote:
| 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
| grimgrin wrote:
| redbean!! redbean is so good
|
| https://redbean.dev/
|
| likely a bit outdated but:
|
| https://github.com/shmup/awesome-cosmopolitan?tab=readme-ov-...
| xrd wrote:
| Redbean is indeed so cool.
|
| I always wanted to do something using Janet in the same way
| with images and compile-time programming. Fun possibilities.
|
| https://janet.guide/compilation-and-imagination/
| snarfy wrote:
| I did not have a very good first experience with cosmopolitan.
| I followed the examples, made a very basic hello world type
| app, and it consistently crashed.
|
| I know it does work but my experience definitely turned me off
| to it. A polyglot binary seemed like a bad idea in retrospect.
| This was back when cosmopolitan was first announced so I assume
| it's better now.
| aappleby wrote:
| Tool works well.
| shlomo_z wrote:
| Thank you!
|
| As someone who isn't disciplined enough to sit through a course
| or class, this was a really good way to visualize what's going on
| under the hood, and how to structure my data more efficiently.
| simonw wrote:
| This is really neat. I posted an animated GIF screenshot here
| https://simonwillison.net/2025/Feb/6/sqlite-page-explorer/
| QuadrupleA wrote:
| Ha, honored :) When I was hacking this together the thought did
| occur, "I bet this is up SimonW's alley." Thanks for all the
| great LLM experiments & writeups.
___________________________________________________________________
(page generated 2025-02-06 23:00 UTC)