phiresky is a programmer who writes about SQLite performance, hosting databases on static sites, ripgrep tooling, and typed SQL libraries.
HN commenters call his SQLite-on-GitHub-Pages technique brilliant, and cite his TypeScript SQL library overview when discussing ORMs and query builders.
I'll start off by quoting the winning submission.
Libraries have been trying to collect humanity’s knowledge almost since the invention of writing. In the digital age, it might actually be possible to create a comprehensive collection of all human writing that meets certain criteria. That’s what shadow libraries do - collect and share as many books as possible.
One shadow library, Anna’s Archive (which I will not link here directly due to copyright concerns), recently posed a question: How could we effectively visualize 100,000,000 books or more at once? There’s lots of data to view: Titles, authors, which countries the books come from, which publishers, how old they are, how many libraries hold them, whether they are available digitally, etc. - https://phiresky.github.io/blog/2025/visualizing-all-books-in-isbn-space/
Basically, legally gray online book repositories such as Anna's Archive, who was the creator of this bounty, are trying to collect a lot of books. The question quickly arises - how many books are there?
The best way to track books is by using ISBN, international standard book number, basically the personal id of any given books, given to books by an international agency. Now that you know which books exist, you can check which books your repository already has and which ones are missing.
But ISBN covers the space of over 2 billion possible existing books. That's a lot. So, Anna's Archive has created a contest to display this space in the cleanest way…
Yeah, I guess you could equivalently put a SQLite database with a view or virtual table in S3 which would give you the same level of indirection and API abstraction provided by this mechanism.
Where DuckDB will have an advantage is in the processing speed for OLAP type queries. I don't know what the current state of SQLite Virtual Tables for parquet files on S3 is, but DuckDB has a number of optimisations for it like only reading the required columns and row groups through range queries. SQLite has a row oriented processing model so I suspect that it wouldn't do that in the same way unless there is a specific vtable extension for it.
You can get a comparable benefit for data in a sqlite db itself with the project from the following blogpost but that wouldn't apply to collections of parquet files: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/
Seems to still require a server which the static site client-side JS continues to call out to? Not really a 'static site' then.
Occurs to me that you could probably go much further towards a truly static site by borrowing from https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ - store the user credentials publicly in a readable way, and use a sufficiently slow hash like scrypt to make the hashes non-bruteforceable. Writes can be done async or maybe through a system like IPFS or Zeronet, or just stored locally and visible to the user only... I'm not entirely sure where you'd want such a scheme, but if you can make a BBS using the SQlite approach (https://ansiwave.net/blog/semantic-web.html) I'm sure there's a niche or variant somewhere which might be interesting.
This blog post explains how it works: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/
Disclaimer: I wrote that article and was somewhat involved in the other sqlite over ipfs project this is forked from.
Yes, for MB size files just downloading the whole thing will be faster and much easier - even if running in the browser. I'd say the boundary is somewhere around 10MB compressed (~20-200MB uncompressed). Looks like the sqlite dump used here is ~400MB in size, ~180MB compressed. Loading that in the browser would probably work but it wouldn't be too great.
I would have appreciated some more details on the benchmark. SQLite has notoriously slow writes in the default journal mode, but proper configuration and WAL/WAL2 mode[0] should be a starting point for any comparison.
It's the first time I've heard of Isar[1] though. I'm always surprised at how many solid-but-underused Apache projects are out there, chugging along.
[0] https://phiresky.github.io/blog/2020/sqlite-performance-tuning/
[1] https://isar.dev/
One note that the article makes is that reading from S3 requires a full read of the file. As an alternate approach is to use HTTP range headers to read only part of a file and minimise how much data goes over the wire. I'm not sure if the 'kiwi' file format would support that (and this is only for reads I believe).
A nice demo of this for sqlite is here - https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/