Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: 22 GB of Hacker News in SQLite

hackerbook.dosaygo.com

Post reply on HN
Print view View original

111–120 of 229 posts

Re: Show HN: 22 GB of Hacker News in SQLite

#111

Post by piperswe »

kristianp wrote: post #84

I tried "select * from items limit 10" and it is slowly iterating through the shards without returning. I got up to 60 shards before I stopped. Selecting just one shard makes that query return instantly. As mentioned elsewhere I think duckdb can work faster by only reading the part of a parquet file it needs over http. I was getting an error that the users and user_domains tables aren't available, but you just need t…

Doesn't `LIMIT` just limit the amount of rows returned, rather than the amount read & processed?

Re: Show HN: 22 GB of Hacker News in SQLite

#112

Post by hamburglar »

yread wrote: post #49

I wonder how much smaller it could get with some compression. You could probably encode "This website hijacks the scrollbar and I don't like it" comments into just a few bits.

It might be a neat experiment to use ai to produce canonicalized paraphrasings of HN arguments so they could be compared directly and compress well.

Re: Show HN: 22 GB of Hacker News in SQLite

#113

Post by gettingoverit »

xnx wrote: post #76
3abiton wrote: post #75

Earlier quoted context omitted.

That was fast. I was looking into recent HN datasets, and they are impossible find.

Complete and continuously updated: https://play.clickhouse.com/play?user=play#U0VMRUNUIG1heCh0a...

If the last story on HN was at December 26, that is.

Re: Show HN: 22 GB of Hacker News in SQLite

#114

Post by lucb1e »

piperswe wrote: post #111
kristianp wrote: post #84

I tried "select * from items limit 10" and it is slowly iterating through the shards without returning. I got up to 60 shards before I stopped. Selecting just one shard makes that query return instantly. As mentioned elsewhere I think duckdb can work faster by only reading the part of a parquet file it needs over http. I was getting an error that the users and user_domains tables aren't available, but you just need t…

Doesn't `LIMIT` just limit the amount of rows returned, rather than the amount read & processed?

That's what it does, but if I'm not mistaken (at least in my experience with MariaDB) it'll also return immediately once it ran up to the limit and not try to process further rows. If you have an expensive subquery in the SELECT (...) AS `column_name`, it won't run that for every row before returning the first 10 (when using LIMIT 10) unless you ORDERed BY that column_name. Other components like the WHERE clause might also require that it reads every row before finding the ten matches. So mostly yes but not necessarily

Re: Show HN: 22 GB of Hacker News in SQLite

#115

Post by amitmahbubani »

meander_water wrote: post #110
simonw wrote: post #22

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.sqli…

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

> Where did you get the 22GB figure from?

The HN post title (:

Re: Show HN: 22 GB of Hacker News in SQLite

#116

Post by meander_water »

amitmahbubani wrote: post #115
meander_water wrote: post #110

Earlier quoted context omitted.

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

> Where did you get the 22GB figure from? The HN post title (:

Hah, well that's embarrassing

Re: Show HN: 22 GB of Hacker News in SQLite

#117

Post by __turbobrew__ »

nextaccountic wrote: post #51
simonw wrote: post #22

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.sqli…

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

gdal vsis3 dynamically fetches chunks of rasters from s3 using range requests. It is the underlying technology for several mapping systems.

There is also a file format to optimize this https://cogeo.org/

Re: Show HN: 22 GB of Hacker News in SQLite

#118

Post by sodafountan »

simonw wrote: post #22

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.sqli…

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?

Re: Show HN: 22 GB of Hacker News in SQLite

#119

Post by billywhizz »

simonw wrote: post #52
nextaccountic wrote: post #51

Earlier quoted context omitted.

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

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/ Looks like it's still on PyPI though: https://pypi.org/project/sqlite-s3vfs/ You can see inside it with my PyPI package explorer: https://tools.simonwillison.net/zip-wheel-explorer?p…

i played around with this a while back. you can see a demo here. it also lets you pull new WAL segments in and apply them to the current database. never got much time to go any further with it than this.

https://just.billywhizz.io/sqlite/demo/#https://raw.githubus...

Re: Show HN: 22 GB of Hacker News in SQLite

#120

Post by scsh »

3abiton wrote: post #75
m-p-3 wrote: post #71

Looks like the repo was taken down (404). That's too bad, I'd like to see the inner-working with a subset of data, even with placeholders for the posts and comments.

That was fast. I was looking into recent HN datasets, and they are impossible find.

It's available on BigQuery and is updated frequently enough(daily I think).
Post reply on HN