Show HN: 22 GB of Hacker News in SQLite

1 day ago (hackerbook.dosaygo.com)

Community, All the HN belong to you. This is an archive of hacker news that fits in your browser. When I made HN Made of Primes I realized I could probably do this offline sqlite/wasm thing with the whole GBs of archive. The whole dataset. So I tried it, and this is it. Have Hacker News on your device.

Go to this repo (https://github.com/DOSAYGO-STUDIO/HackerBook): you can download it. Big Query -> ETL -> npx serve docs - that's it. 20 years of HN arguments and beauty, can be yours forever. So they'll never die. Ever. It's the unkillable static archive of HN and it's your hands. That's my Year End gift to you all. Thank you for a wonderful year, have happy and wonderful 2026. make something of it.

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 - 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 asks you to select which shards to run the query against, there are 1636 total.

  • 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/readervf...

    And using it with range requests: 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-form...

    • 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.

      1 reply →

  • 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.

    • 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.

      1 reply →

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

    • Yes — PMTiles is exactly that: a production-ready, single-file, static container for vector tiles built around HTTP range requests.

      I’ve used it in production to self-host Australia-only maps on S3. We generated a single ~900 MB PMTiles file from OpenStreetMap (Australia only, up to Z14) and uploaded it to S3. Clients then fetch just the required byte ranges for each vector tile via HTTP range requests.

      It’s fast, scales well, and bandwidth costs are negligible because clients only download the exact data they need.

      https://docs.protomaps.com/pmtiles/

      8 replies →

    • 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/

    • This is somewhat related to a large dataset browsing service a friend and I worked on a while back - we made index files, and the browser ran a lightweight query planner to fetch static chunks which could be served from S3/torrents/whatever. It worked pretty well, and I think there’s a lot of potential for this style of data serving infra.

    • I tried to implement something similar to optimize sampling semi-random documents from (very) large datasets on Huggingface, unfortunately their API doesn't support range requests well.

    • This is pretty much well what is so remarkable about parquet files; not only do you get seekable data, you can fetch only the columns you want too.

      I believe that there are also indexing opportunities (not necessarily via eg hive partitioning) but frankly - am kinda out of my depth pn it.

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

    • 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?

  • 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

  • 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?

    • 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

      1 reply →

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'd be great if you could add it to Kiwix[1] somehow (not sure what the process is for that but 100rabbits figured it out for their site) - I use it all the time now that I have a dumb phone - I have the entirety of wikipedia, wiktionary and 100rabbits all offline.

https://kiwix.org/en/

  • what dumb phone do you use?

    and why do you want wikipedia in your pocket, but not a smartphone? where do you draw the line?

    (doing a lot of work in that area, so i am asking to learn from someone who might think alike)

    • I use the Mudita Kompakt specifically cause it allows sideloading so I can still have a few extras. Right now I have Kiwix and Libby. It works really well.

      I have a $10 a month plan from US cellular with only 2gigs so I try to keep everything offline that I can.

      Honestly it's mostly the news... so I draw the line at browser, I'll never install a browser, that's basically something I can do when I sit down at a PC. I read quite a bit and I like to have the ability to look up a word or a historical event or some reference from something I read using Kiwix and it's been great for that, just needed to add a 512gb micro sd card. And Libby I just use at the gym when I'm on the treadmill.

Similar to Single-page applications (SPA), single-table application (STA) might become a thing. Just a shard a table on multiple keys and serve the shards as static files, provided that the data is Ok to share, similar to sharing static html content.

That's pretty neat!

I did something similar. I build a tool[1] to import the Project Arctic Shift dumps[2] of reddit into sqlite. It was mostly an exercise to experiment with Rust and SQLite (HN's two favorite topics). If you don't build a FTS5 index and import without WAL (--unsafe-mode), import of every reddit comment and submission takes a bit over 24 hours and produces a ~10TB DB.

SQLite offers a lot of cool json features that would let you store the raw json and operate on that, but I eschewed them in favor of parsing only once at load time. THat also lets me normalize the data a bit.

I find that building the DB is pretty "fast", but queries run much faster if I immediately vacuum the DB after building it. The vacuum operation is actually slower than the original import, taking a few days to finish.

[1] https://github.com/Paul-E/Pushshift-Importer

[2] https://github.com/ArthurHeitmann/arctic_shift/blob/master/d...

  • Holy cow, I didn't know getting reddit was that straightforward. I am building public readonly-SQL+vector databases optimized for exploring high-quality public commons with Claude Code (https://exopriors.com/scry), I so cannot wait until some funding source comes in and I can upgrade to a $1500/month Hetzner server and pay the ~$1k to embed all that.

  • You could check out SQLite's auto_vacuum which reclaims space without rebuilding the entire db https://sqlite.org/pragma.html#pragma_auto_vacuum

    • I haven't tested that, so I'm not sure if it would work. The import only inserts rows, it doesn't delete, so I don't think that is the cause of fragmentation. I suspect this line in the vacuum docs:

      > The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

      means SQLite does something to organize by rowid and that this is doing most of the work.

      Reddit post/comment IDs are 1:1 with integers, though expressed in a different base that is more friendly to URLs. I map decoded post/comment IDs to INTEGER PRIMARY KEYs on their respective tables. I suspect the vacuum operation sorts the tables by their reddit post ID and something about this sorting improves tables scans, which in turn helps building indices quickly after standing up the DB.

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 to change the shard filter to the user stats shard.

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

    • That depends on the query. SQLite tries to use LIMIT to restrict the amount of reading that it does. It is often successful at that. But some queries, by their very nature, logically require reading the whole input in order to compute the correct answer, regardless of whether or not there is a LIMIT clause.

    • 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

    • The limit clause isn't official/standard ansi sql, so it's up to the rdbms to implement. Your assumption is true for bigquery (infamously) but not true for things like snowflake, duckdb, etc.

That repo is throwing up a 404 for me.

Question - did you consider tradeoffs between duckdb (or other columnar stores) and SQLite?

  • No, I just went straight to sqlite. What is duckdb?

    • One interesting feature of DuckDB is that it can run queries against HTTP ranges of a static file hosted via HTTPS, and there's an official WebAssembly build of it that can do that same trick.

      So you can dump e.g. all of Hacker News in a single multi-GB Parquet file somewhere and build a client-side JavaScript application that can run queries against that without having to fetch the whole thing.

      You can run searches on https://lil.law.harvard.edu/data-gov-archive/ and watch the network panel to see DuckDB in action.

      1 reply →

    • DuckDB is an open-source column-oriented Relational Database Management System (RDBMS). It's designed to provide high performance on complex queries against large databases in embedded configuration.

      It has transparent compression built-in and has support for natural language queries. https://buckenhofer.com/2025/11/agentic-ai-with-duckdb-and-s...

      "DICT FSST (Dictionary FSST) represents a hybrid compression technique that combines the benefits of Dictionary Encoding with the string-level compression capabilities of FSST. This approach was implemented and integrated into DuckDB as part of ongoing efforts to optimize string storage and processing performance." https://homepages.cwi.nl/~boncz/msc/2025-YanLannaAlexandre.p...

    • It is very similar to SQLite in that it can run in-process and store its data as a file.

      It's different in that it is tailored to analytics, among other things storage is columnar, and it can run off some common data analytics file formats.

  • Maybe it got nuked by MS? The rest of their repo's are up.

    • Hey jacquesm! No, I just forgot to make it public.

      BUT I did try to push the entire 10GB of shards to GitHub (no LFS, no thanks, money), and after the 20 minutes compressing objects etc, "remote hang up unexpectedly"

      To be expected I guess. I did not think GH Pages would be able to do this. So have been repeating:

        wrangler pages deploy docs --project-name static-news --commit-dirty=true
      

      on changes and first time CF Pages user here, much impressed!

      3 replies →

  • While I suspect DuckDB would compress better, given the ubiquity of SQLite, it seems a fine standard choice.

    • the data is dominated by big unique TEXT columns, unsure how that can much compress better when grouped - but would be interesting to know

      1 reply →

  • Not the author here. I’m not sure about DuckDB, but SQLite allows you to simply use a file as a database and for archiving, it’s really helpful. One file, that’s it.

Similar in spirit to a recent tool I recently posted Show HN on, https://exopriors.com/scry. You can use Claude Code to SQL+vector query HackerNews and many other high quality public commons sites, exceptionally well-indexed and usually 5+ minute query timeout limits, so you can run seriously large research queries, to rapidly refine your worldview (particular because you can do easily to EXHAUSTIVE exploration).

  • This looks cool but can you make a "Google Search Box" page where I don't have to sign in but can use it? It's just a bit of friction and I feel unbothered to overcome it. It's not personal to you - it's just how I feel about anything that looks unknown and interesting I just want to try, not have to sign up. For now. You know?

  • I like your concept of indexing high quality sources for RAG. For many queries we might not need the usual search engines.

threw some heatmaps together of post volume and average score by day and time (15min intervals)

story volume (all time): https://ibb.co/pBTTRznP

average score (all time): https://ibb.co/KcvVjx8p

story volume (since 2020): https://ibb.co/cKC5d7Pp

average score (since 2020): https://ibb.co/WpN20kfh

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.

The query tab looks quite complex with all these content shards: https://hackerbook.dosaygo.com/?view=query

I have a much simpler database: https://play.clickhouse.com/play?user=play#U0VMRUNUIHRpbWUsI...

  • Does your database also runs offline/locally in the browser? Seems to be the reason for the large number of shards.

    • You can run it locally, but it is a client-server architecture, which means that something has to run behind the browser.

What a reminder on how text is so much more efficient than video, its crazy! Could you imagine the same amount of knowledge (or dribble) but in video form? I wonder how large that would be.

  • That's what's so sad about youtube. 20 minute videos to encode a hundred words of usable content to get you to click on a link. The inefficiency is just staggering.

    • Youtube can be excellent for explanations. A picture's worth a thousand words, and you can fit a lot of decent pictures in a 20 minute video. The signal-to-noise can be high, of course.

      1 reply →

  • Average high quality 1080p60 video has bitrate of 5Mbps, which is equivalent to 120k English words per second. With average English speech being 150wpm, we end up with text being 50 thousand times more space efficient.

    Converting 22GB of uncompressed text into video essay lands us at ~1PB or 1000TB.

  • Right? 20 years, probably 10s millions of human hours of interactions, and it’s only as much as a couple DVDs.

  • one could use a video llm to generate the video, diagrams or the stills automatically based on the text. except when it's boardgames playthroughs or programming i just transcribe to text, summarise and read youtube video's.

    • How do you read youtube videos? Very curious as I have been wanting to watch PDF's scroll by slowly on a large TV. I am interested in the workflow of getting a pdf/document into a scrolling video format. These days NotebookLM may be an option but I am curious if there is something custom. If I can get it into video form (mp4) then I can even deliver it via plex.

      1 reply →

    • Can be nice to pull a raw transcript and have it formatted as HTML (formatting/punctuation fixes applied).

      Best locally of course to avoid “I burned a lake for this?” guilt.

      1 reply →

Site does not load on Firefox console error says 'Uncaught (in promise) TypeError: can't access property "wasm", sqlite3 is null'

Guess its common knowledge that SharedArrayBuffer (SQLite wasm) does not work with FF due to Cross-Origin Attacks (i just found out ;).

Once the initial chunk of data loads the rest load almost instantly on Chrome. Can you please fix the GitHub link (current 404) would like to peak at the code. Thank you!

  • Damn. Will try to fix for FF.

    edit: I just tested with FF latest, seems to be working.

    • Strange now the first few days load (getting a new error) 'Ignoring inability to install OPFS sqlite3_vfs: Cannot install OPFS: Missing SharedArrayBuffer and/or Atomics. The server must emit the COOP/COEP response headers to enable those. See https://sqlite.org/wasm/doc/trunk/persistence.md#coop-coep'

      But when go back to the 26th none of the shards will load, error out.

      Using Windows 11, FF 146.0.1

      Since you tested it seems its just a me problem and thanks for fixing the GitHub link

      1 reply →

Wonder if you could turn this into a .zim file for offline browsing with an offline browser like Kiwix, etc. [0]

I've been taking frequent "offline-only-day" breaks to consolidate whatever I've been learning, and Kiwix has been a great tool for reference (offline Wikipedia, StackOverflow and whatnot).

[0] https://kiwix.org/en/the-new-kiwix-library-is-available/

  • Oh this should TOTALLY be available to those who are scrolling through sources on the Kiwix app!

Is it a thing that the design is almost unusable on a mobile phone? The tech making this possible is beyond cool, but it's just presented in such a brutal way for phone users, even though fixing it would be super simple.

Neat. I keep wanting to build something like this for GitHub audit logs, but at ~5 tb, probably a little much

It's really a shame that comment scores are hidden forever. Would the admins consider publishing them after stories are old enough that voting is closed? It would be great to have them for archives and search indices and projects like this.

  • I wrote to hn@ and asked for this as a feature request:

    "1. Delayed Karma Display. I understand why comment karma was hidden. I don't see the harm in un-hiding karma after some time. If not 24 hours, then 72-168 hours. This would help me read through threads with 1300 comments."

    This was last January. While I asked for a few more features, it is the only one that seems essential as HN grows with massive threads.

  • Fear not. I have a collaborative project designed to address this.

    • They're referring to scores on individual COMMENTS - this information isn't available via the HN Firebase API.

      The only way you could theoretically extract everyone's comment scores (at least the top level ones) would be like this if you're a complete madman:

      1. Wait 48 hours so the article is effectively dead

      2. Post a new comment using an account called ThePresident

      3. Create a swarm of a thousand shill user accounts called Voter1, Voter2, etc.

      4. Use a single account at a time and upvote ThePresident

      5. Recheck the page to see if ThePresident has moved above a user(s) post

      6. Record the score for that user and assign it to the tracked story's history

      7. Repeat from (4)

      1 reply →

Did anyone get a copy of this before it was pulled? If GitHub is not keen, could it be uploaded to HuggingFace or some other service which hosts large assets?

I have always known I could scrape HN, but I would much rather take a neat little package.

Is there a public dump of the data anywhere that this is based upon, or have they scraped it themselves?

Such as DB might be entertaining to play with, and the threadedness of comments would be useful for beginners to practise efficient recursive queries (more so than the StackExchange dumps, for instance).

This is pretty neat! The calendar didn't work well for me. I could only seem to navigate by month. And when I selected the earliest day (after much tapping), nothing seemed to be updated.

Nonetheless, random access history is cool.

  • Cna you let me know? I'm sure there's some weirdness lurking there and I want to smooth it out. Calendar is essential.

Suddenly occurs to me that it would be neat to pair a small LLM (3-7B) with an HN dataset

  • Does the SQLite version of this already exist somewhere? The github link on the footer of the page fails for me.

22gb for mostly text? tried loading the site, it's pretty slow. curious how the query performance is with this much data in sqlite

Apparently the comment counts are only the top-level comments?

It would be nice for the thread pages to show a comment count.

  • Yes, because comments in a thread can span shards. It’s just a bit too heavy to add comment counts of an entire thread. So I give a low bound ha ha

Is this updated regularly? 404 on GitHub as the other comment.

With all due respect it would be great if there is an official HN public dump available (and not requiring stuff such as BigQuery which is expensive).

  • The BQ dataset is only ~17GB and the free tier of BQ lets you query 1TB per month. If you're not doing select * on every query you should be able to do a lot with that.

Beautiful !

2026 prayer: for all you AI junkies—please don’t pollute H/N with your dirty AI gaming.

Don’t bot posts, comments, or upvote/downvote just to maximize karma. Please.

We can’t identify anymore who’s a bot and who’s human. I just want to hang out with real humans here.

Alas, HN does not belong to us, and the existence of projects like this are subject to the whims of the legal owners of HN.

From the terms of use [0]:

"""

Commercial Use: Unless otherwise expressly authorized herein or in the Site, you agree not to display, distribute, license, perform, publish, reproduce, duplicate, copy, create derivative works from, modify, sell, resell, exploit, transfer or upload for any commercial purposes, any portion of the Site, use of the Site, or access to the Site. The buying, exchanging, selling and/or promotion (commercial or otherwise) of upvotes, comments, submissions, accounts (or any aspect of your account or any other account), karma, and/or content is strictly prohibited, constitutes a material breach of these Terms of Use, and could result in legal liability.

"""

[0] https://www.ycombinator.com/legal/#tou

  • But is this really a commercial use? There doesn’t seem to be any intention of monetising this so I guess it doesn’t as specify commercial?

> Community, All the HN belong to you. This is an archive of hacker news that fits in your browser.

> 20 years of HN arguments and beauty, can be yours forever. So they'll never die. Ever. It's the unkillable static archive of HN and it's your hands

I'm really sorry to have to ask this, but this really feels like you had an LLM write it?

  • I doubt it. "hacker news" spelled lowercase? comma after "beauty"? missing "in" after "it's"? i doubt an LLM would make such syntax mistakes. it's just good writing, that's also possible these days.

    • > it's just good writing, that's also possible these days.

      As someone reskilling into being a writer, I really do not think that is "good writing".

  • There's a thing in soccer at the moment where a tackle looks fine in realtime but when the video referee shows it to the onpitch referee, they show the impact in slo-mo over and over again and it always looks way worse.

    I wonder if there's something like this going on here. I never thought it was LLM on first read, and I still don't, but when you take snippets and point at them it makes me think maybe they are

  • Even if so, would it have mattered? The point is showing off the SQLite DB.

    But it didn’t read LLM generated IMO.

  • > I'm really sorry to have to ask this, but this really feels like you had an LLM write it?

    Ending a sentence with a question mark doesn’t automatically make your sentence a question. You didn’t ask anything. You stated an opinion and followed it with a question mark.

    If you intended to ask if the text was written by AI, no, you don’t have to ask that.

    I am so damn tired of the “that didn’t happen” and the “AI did that” people when there is zero evidence of either being true.

    These people are the most exhausting people I have ever encountered in my entire life.