6 ms·

Don't miss how this works. It's not a server-side application - this code runs entirely in your browser using SQLite compiled to WASM, but rather than fetching

by simonw 11mo ago

Don't miss how this works. It's not a server-side application - this code runs entirely in your browser using SQLite compiled to WASM, but rather than fetching a full 22GB database it instead uses a clever hack that retrieves just "shards" of the SQLite database needed for the page you are viewing. I watched it in the browser network panel and saw it fetch: https://hackerbook.dosaygo.com/static-shards/shard_1636.sqlite.gz https://hackerbook.dosaygo.com/static-shards/shard_1635.sqlite.gz https://hackerbook.dosaygo.com/static-shards/shard_1634.sqlite.gz As I paginated to previous days. It's reminiscent of that brilliant SQLite.js VFS trick from a few years ago: https://github.com/phiresky/sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs - only that one used HTTP range headers, this one uses sharded files instead. The interactive SQL query interface at https://hackerbook.dosaygo.com/?view=query https://hackerbook.dosaygo.com/?view=query asks you to select which shards to run the query against, there are 1636 total.

maxloh 11mo ago
I am curios why they don't use a single file and HTTP Range Requests instead. PMTiles (a distribution of OpenStreetMap) uses that.
- keepamovin 11mo ago
  This would be a neat idea to try. Want to add a PR? Bench different "hackends" to see how DuckDB, SQLite shards, or range queries perform?
tehlike 11mo ago
Vfs support is amazing.
nextaccountic 11mo ago
Is there anything more production grade built around the same idea of HTTP range requests like that sqlite thing? This has so much potential
- simonw 11mo ago
  There was a UK government GitHub repo that did something interesting with this kind of trick against S3 but I checked just now and the repo is a 404. Here are my notes about what it did: https://simonwillison.net/2025/Feb/7/sqlite-s3vfs/ https://simonwillison.net/2025/Feb/7/sqlite-s3vfs/ Looks like it's still on PyPI though: https://pypi.org/project/sqlite-s3vfs/ https://pypi.org/project/sqlite-s3vfs/ You can see inside it with my PyPI package explorer: https://tools.simonwillison.net/zip-wheel-explorer?package=sqlite-s3vfs#sqlite_s3vfs.py https://tools.simonwillison.net/zip-wheel-explorer?package=s...
  - simonw 11mo ago
    I recovered it from https://archive.softwareheritage.org/browse/origin/directory/?origin_url=https://github.com/uktrade/sqlite-s3vfs https://archive.softwareheritage.org/browse/origin/directory... and pushed a fresh copy to GitHub here: https://github.com/simonw/sqlite-s3vfs https://github.com/simonw/sqlite-s3vfs This comment was helpful in figuring out how to get a full Git clone out of the heritage archive: https://news.ycombinator.com/item?id=37516523#37517378 https://news.ycombinator.com/item?id=37516523#37517378 Here's a TIL I wrote up of the process: https://til.simonwillison.net/github/software-archive-recovery https://til.simonwillison.net/github/software-archive-recove...
    - AceJohnny2 11mo ago
      didn't you do something similar for Datasette, Simon?
      - simonw 11mo ago
        Nothing smart with HTTP range requests yet - I have https://lite.datasette.io https://lite.datasette.io which runs the full Python server app in the browser via WebAssembly and Pyodide but it still works by fetching the entire SQLite file at once.
        AceJohnny2 11mo ago
ncruces 11mo ago
A read-only VFS doing this can be really simple, with the right API… This is my VFS: https://github.com/ncruces/go-sqlite3/blob/main/vfs/readervfs/reader.go https://github.com/ncruces/go-sqlite3/blob/main/vfs/readervf... And using it with range requests: https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readervfs#example-package-Http https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readerv... And having it work with a Zstandard compressed SQLite database, is one library away: https://pkg.go.dev/github.com/SaveTheRbtz/zstd-seekable-format-go/pkg https://pkg.go.dev/github.com/SaveTheRbtz/zstd-seekable-form...
- pdyc 11mo ago
  this does not caches the data right? it would always fetch from network? by any chance do you know of solution/extension that caches the data it would make it so much more efficient.
  - ncruces 11mo ago
    The package I'm using in the HTTP example can be configured to cache data: https://github.com/psanford/httpreadat?tab=readme-ov-file#caching https://github.com/psanford/httpreadat?tab=readme-ov-file#ca... But, also, SQLite caches data; you can simply increase the page cache.
- keepamovin 11mo ago
  Your page is served over sqlitevfs with Range queries? Let's try this here.
  - ncruces 11mo ago
    I did a similar VFS in Go. It doesn't run client-side in a browser. But you can use it (e.g.) in a small VPS to access a multi-TB database directly from S3.
    - keepamovin 11mo ago
      That is cool. Maybe i look at the go code
meander_water 11mo ago
I love this so much, on my phone this is much faster than actual HN (I know it's only a read-only version). Where did you get the 22GB figure from? On the site it says: > 46,399,072 items, 1,637 shards, 8.5GB, spanning Oct 9, 2006 to Dec 28, 2025
- amitmahbubani 11mo ago
  > Where did you get the 22GB figure from? The HN post title (:
  - meander_water 11mo ago
    Hah, well that's embarrassing
  - keepamovin 11mo ago
    22GB is non-gzipped.
sodafountan 11mo ago
The GitHub page is no longer available, which is a shame because I'm really interested in how this works. How was the entirety of HN stored in a single SQLite database? In other words, how was the data acquired? And how does the page load instantly if there's 22GB of data having to be downloaded to the browser?
- keepamovin 11mo ago
  You can see it now, forgot to make it public. - 1. download_hn.sh - bash script that queries BigQuery and saves the data to *.json.gz - 2. etl-hn.js - does the sharding and ID -> shard map, plus the user stats shards. - 3. Then either npx serve docs or upload to CloudFlare Pages. The ./toool/s/predeploy-checks.sh script basically runs the entire pipeline. You can do it unattended with AUTO_RUN=true
  - sodafountan 11mo ago
    Awesome, I'll take a look
keepamovin 11mo ago
Thanks! I'm glad you enjoyed the sausage being made. There's a little easter egg if you click on the compact disc icon. And I just now added a 'me' view. Enter your username and it will show your comments/posts on any day. So you can scrub back through your 2006 - 2025 retrospective using the calendar buttons.
- oblosys 11mo ago
  I almost got tricked into trying to figure out what was Easter eggy about August 9 2015 :-) There's a clarifying tooltip on the link, but it is mostly obscured by the image's "Archive" title attribute.
  - keepamovin 11mo ago
    Oh, shit that was the problem! You solved the bug! I was trying to figure out why the right tooltip didn't display. A linked wrapped in an image wrapped in an easter egg! Or something. Ha, thank you. Will fix :) edit: Fixed! Also I just pushed a new version with a Dec 29th Data Dump, so ... updates - yay!
    - oblosys 11mo ago
      Happy to help!
keepamovin 11mo ago
A recent change is I added date spans to the shard checboxes on query view so it's easier to zero dates you want if you have that in mind. Because if your copy isn't local all those network pulls take a while. The sequence of shards you saw when you paginated to days is faciliated by the static-manifest which maps HN item ID ranges to shards, and since IDs are increasing and a pretty good proxy of time (a "HN clock"), we can also map the shards that we cut up by ID to the time spans their items cover. An in memory table sorted by time is created from the manifest on load so we can easily look up which shard we need when you pick a day. Funnily enough, this system was thrown off early on by a handful of "ID/timestamp" outliers in the data: items with weird future timestamps (offset by a couple years), or null timestamps. To cleanse our pure data from this noise, and restore proper adjacent-in-time shard cuts we just did a 1/99 percentile grouping and discarded the outliers leaving shards with sensible 'effective' time spans. Sometimes we end up fetching two shards when you enter a new day because some items' comments exist "cross shard". We needed another index for that and it lives in cross-shard-index.bin which is just a list of 4-byte item IDs that have children in more than 1 shard (2-bytes), which occurs when people have the self-indulgence to respond to comments a few days after a post has died down ;) Thankfully HN imposes a 2 week horizon for replies so there aren't that many cross-shard comments (those living outside the 2-3 days span of most, recent, shards). But I think there's still around 1M or so, IIRC.
dzhiurgis 11mo ago
Is it possible to implement search this way?

by simonw 11mo ago

maxloh 11mo ago

I am curios why they don't use a single file and HTTP Range Requests instead. PMTiles (a distribution of OpenStreetMap) uses that.

keepamovin 11mo ago
This would be a neat idea to try. Want to add a PR? Bench different "hackends" to see how DuckDB, SQLite shards, or range queries perform?

tehlike 11mo ago

Vfs support is amazing.

nextaccountic 11mo ago

Is there anything more production grade built around the same idea of HTTP range requests like that sqlite thing? This has so much potential

simonw 11mo ago
There was a UK government GitHub repo that did something interesting with this kind of trick against S3 but I checked just now and the repo is a 404. Here are my notes about what it did: https://simonwillison.net/2025/Feb/7/sqlite-s3vfs/ https://simonwillison.net/2025/Feb/7/sqlite-s3vfs/ Looks like it's still on PyPI though: https://pypi.org/project/sqlite-s3vfs/ https://pypi.org/project/sqlite-s3vfs/ You can see inside it with my PyPI package explorer: https://tools.simonwillison.net/zip-wheel-explorer?package=sqlite-s3vfs#sqlite_s3vfs.py https://tools.simonwillison.net/zip-wheel-explorer?package=s...
- simonw 11mo ago
  I recovered it from https://archive.softwareheritage.org/browse/origin/directory/?origin_url=https://github.com/uktrade/sqlite-s3vfs https://archive.softwareheritage.org/browse/origin/directory... and pushed a fresh copy to GitHub here: https://github.com/simonw/sqlite-s3vfs https://github.com/simonw/sqlite-s3vfs This comment was helpful in figuring out how to get a full Git clone out of the heritage archive: https://news.ycombinator.com/item?id=37516523#37517378 https://news.ycombinator.com/item?id=37516523#37517378 Here's a TIL I wrote up of the process: https://til.simonwillison.net/github/software-archive-recovery https://til.simonwillison.net/github/software-archive-recove...
  - AceJohnny2 11mo ago
    didn't you do something similar for Datasette, Simon?
    - simonw 11mo ago
      Nothing smart with HTTP range requests yet - I have https://lite.datasette.io https://lite.datasette.io which runs the full Python server app in the browser via WebAssembly and Pyodide but it still works by fetching the entire SQLite file at once.
      - AceJohnny2 11mo ago

ncruces 11mo ago

A read-only VFS doing this can be really simple, with the right API… This is my VFS: https://github.com/ncruces/go-sqlite3/blob/main/vfs/readervfs/reader.go https://github.com/ncruces/go-sqlite3/blob/main/vfs/readervf... And using it with range requests: https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readervfs#example-package-Http https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readerv... And having it work with a Zstandard compressed SQLite database, is one library away: https://pkg.go.dev/github.com/SaveTheRbtz/zstd-seekable-format-go/pkg https://pkg.go.dev/github.com/SaveTheRbtz/zstd-seekable-form...

pdyc 11mo ago
this does not caches the data right? it would always fetch from network? by any chance do you know of solution/extension that caches the data it would make it so much more efficient.
- ncruces 11mo ago
  The package I'm using in the HTTP example can be configured to cache data: https://github.com/psanford/httpreadat?tab=readme-ov-file#caching https://github.com/psanford/httpreadat?tab=readme-ov-file#ca... But, also, SQLite caches data; you can simply increase the page cache.
keepamovin 11mo ago
Your page is served over sqlitevfs with Range queries? Let's try this here.
- ncruces 11mo ago
  I did a similar VFS in Go. It doesn't run client-side in a browser. But you can use it (e.g.) in a small VPS to access a multi-TB database directly from S3.
  - keepamovin 11mo ago
    That is cool. Maybe i look at the go code

meander_water 11mo ago

I love this so much, on my phone this is much faster than actual HN (I know it's only a read-only version). Where did you get the 22GB figure from? On the site it says: > 46,399,072 items, 1,637 shards, 8.5GB, spanning Oct 9, 2006 to Dec 28, 2025

amitmahbubani 11mo ago
> Where did you get the 22GB figure from? The HN post title (:
- meander_water 11mo ago
  Hah, well that's embarrassing
- keepamovin 11mo ago
  22GB is non-gzipped.

sodafountan 11mo ago

The GitHub page is no longer available, which is a shame because I'm really interested in how this works. How was the entirety of HN stored in a single SQLite database? In other words, how was the data acquired? And how does the page load instantly if there's 22GB of data having to be downloaded to the browser?

keepamovin 11mo ago
You can see it now, forgot to make it public. - 1. download_hn.sh - bash script that queries BigQuery and saves the data to *.json.gz - 2. etl-hn.js - does the sharding and ID -> shard map, plus the user stats shards. - 3. Then either npx serve docs or upload to CloudFlare Pages. The ./toool/s/predeploy-checks.sh script basically runs the entire pipeline. You can do it unattended with AUTO_RUN=true
- sodafountan 11mo ago
  Awesome, I'll take a look

keepamovin 11mo ago

Thanks! I'm glad you enjoyed the sausage being made. There's a little easter egg if you click on the compact disc icon. And I just now added a 'me' view. Enter your username and it will show your comments/posts on any day. So you can scrub back through your 2006 - 2025 retrospective using the calendar buttons.

oblosys 11mo ago
I almost got tricked into trying to figure out what was Easter eggy about August 9 2015 :-) There's a clarifying tooltip on the link, but it is mostly obscured by the image's "Archive" title attribute.
- keepamovin 11mo ago
  Oh, shit that was the problem! You solved the bug! I was trying to figure out why the right tooltip didn't display. A linked wrapped in an image wrapped in an easter egg! Or something. Ha, thank you. Will fix :) edit: Fixed! Also I just pushed a new version with a Dec 29th Data Dump, so ... updates - yay!
  - oblosys 11mo ago
    Happy to help!

keepamovin 11mo ago

A recent change is I added date spans to the shard checboxes on query view so it's easier to zero dates you want if you have that in mind. Because if your copy isn't local all those network pulls take a while. The sequence of shards you saw when you paginated to days is faciliated by the static-manifest which maps HN item ID ranges to shards, and since IDs are increasing and a pretty good proxy of time (a "HN clock"), we can also map the shards that we cut up by ID to the time spans their items cover. An in memory table sorted by time is created from the manifest on load so we can easily look up which shard we need when you pick a day. Funnily enough, this system was thrown off early on by a handful of "ID/timestamp" outliers in the data: items with weird future timestamps (offset by a couple years), or null timestamps. To cleanse our pure data from this noise, and restore proper adjacent-in-time shard cuts we just did a 1/99 percentile grouping and discarded the outliers leaving shards with sensible 'effective' time spans. Sometimes we end up fetching two shards when you enter a new day because some items' comments exist "cross shard". We needed another index for that and it lives in cross-shard-index.bin which is just a list of 4-byte item IDs that have children in more than 1 shard (2-bytes), which occurs when people have the self-indulgence to respond to comments a few days after a post has died down ;) Thankfully HN imposes a 2 week horizon for replies so there aren't that many cross-shard comments (those living outside the 2-3 days span of most, recent, shards). But I think there's still around 1M or so, IIRC.

dzhiurgis 11mo ago

Is it possible to implement search this way?