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.
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.
That's really impressive! As someone who's worked with SQLite internals in the past, I know this kind of work and tinkering is no small feat. On a related note, I recently came across sqlite-repr [1] (built with Dioxus), which offers an interesting take on SQLite visualizations as well.
You can hover to get the page types (title attributes) and learn the color scheme that way, although it's not super clear.
And yeah, large DBs get slow; pages are iterated "server side" which can take a while when there are hundreds of thousands of them. Ideal would probably be some live-scrolling page-range API call, although maybe a crude "show next 10K pages" or something would work too.
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.
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.
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.
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.
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 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.
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.
Ideally, I'd rather use WAL mode with some kind of (1) optimized append for the log, and (2) check pointing that doesn't need to read back the entire database. I can do (1) easily on GCS, but not (2); (2) is probably easier in S3, not sure about (1).
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).
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.
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!
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.
> 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).
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.
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.
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
This is really neat. I posted an animated GIF screenshot here https://simonwillison.net/2025/Feb/6/sqlite-page-explorer/
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.
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
Indeed, the file format is easy to read! It took me a few hours in one sitting to implement a reader.
I have some visualization tools too, but with way less features than the OP's software: https://www.nayuki.io/page/sqlite-database-file-visualizatio...
By the way, I just used this today, it was useful.
That's really impressive! As someone who's worked with SQLite internals in the past, I know this kind of work and tinkering is no small feat. On a related note, I recently came across sqlite-repr [1] (built with Dioxus), which offers an interesting take on SQLite visualizations as well.
[1] https://torymur.github.io/sqlite-repr/
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.
This looks cool, nice job.
Maybe add a color-legend to the front page? I didn't know what the colors represented at first.
It's kind of choking on a larger db (3.6GB, 942719 pages) - maybe you can paginate the pages.
You can hover to get the page types (title attributes) and learn the color scheme that way, although it's not super clear.
And yeah, large DBs get slow; pages are iterated "server side" which can take a while when there are hundreds of thousands of them. Ideal would probably be some live-scrolling page-range API call, although maybe a crude "show next 10K pages" or something would work too.
Nice project, unfortunately when I run the executable it throws the error: "failed to run lua code: /index.lua:3: module 'utils' not found:".
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.
I recently wrote an explainer for the sqlite file format with some helpful diagrams. This might help.
https://blog.jabid.in/2024/11/24/sqlite.html
The sqlite source includes a CLI tool called showdb that is quite nice for poking around in database files
Neat. Being learning about databases and this is very helpful!
Tool works well.
A true masterpiece:)
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.
What are the disadvantages of using WITHOUT ROWID?
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.
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.
The disadvantage is that you’ll never look at Postgres the same way again.
Yeah, one of the things postgres is missing. Yugabyte seems to be addressing this thankfully
Yep looks like it. They don't seem to use Postgres storage layer at all.
Is this source available?
Read-only or read-write? And if writeable, what's concurrency like?
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
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/
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.
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.
Ideally, I'd rather use WAL mode with some kind of (1) optimized append for the log, and (2) check pointing that doesn't need to read back the entire database. I can do (1) easily on GCS, but not (2); (2) is probably easier in S3, not sure about (1).
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!
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!
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.
You might like https://github.com/superfly/litefs or https://github.com/benbjohnson/litestream
Do you have a url describing your protip?
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).
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.
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.
https://news.ycombinator.com/item?id=42965714 will work if you're just bookmarking for the future
redbean!! redbean is so good
https://redbean.dev/
likely a bit outdated but:
https://github.com/shmup/awesome-cosmopolitan?tab=readme-ov-...
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.
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/
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