Awesome work.
Minor bug/suggestion: right-aligned text inputs (eg the username input on the “me” page) aren’t ideal since they are often obscured by input helpers (autocomplete or form fill helper icons).
Show HN: 22 GB of Hacker News in SQLite
201–210 of 229 posts
Re: Show HN: 22 GB of Hacker News in SQLite
#202Earlier quoted context omitted.
My only gripe is that the tile metadata is stored as JSON, which I get is for compatibility reasons with existing software, but for e.g. a simple C program to implement the full spec you need to ship a JSON parser on top of the PMTiles parser itself.
A JSON parser is less than a thousand lines of code.
And where most of CPU time will be wasted in, if you care about profiling/improving responsiveness.
Re: Show HN: 22 GB of Hacker News in SQLite
#203Earlier quoted context omitted.
I recovered it from https://archive.softwareheritage.org/browse/origin/directory... and pushed a fresh copy to GitHub here: 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 Here's a TIL I wrote up of the process: https://til.simonwillison.net/github/software-archive-recove...
Doing all this in an hour is such a good example of how absurdly efficient you can be with LLMs.
From reading the TIL, it doesn't appear as if Simon used LLM for a large portion of what he did; only the initial suggestion to check the archive, and the web tool to make his process reproducible. Also, if you read the script from his chat with Claude code, the prompt really does the heavy lifting.
Sure, the LLM fills in all the boilerplate and makes an easy-to-use, reproducible tool with loads of documentation, and credit for that. But is it not more accurate to say that Simon is absurdly efficient, LLM or sans LLM? :)
Re: Show HN: 22 GB of Hacker News in SQLite
#204Is 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.
Really? Let me know how I can help. What would you like to see fixed?
Just following the ordinary guidelines when doing responsive designs, like increasing the text size and sizes of buttons and inputs, so my fat fingers don't missklick every other try. HN has gotten better, but is still below average, hence why I thought it was some kind of aesthetic choice.
Re: Show HN: 22 GB of Hacker News in SQLite
#205Earlier quoted context omitted.
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.
Unfortunately even the videos that do contain helpful imagery are still dominated by huge sections of low entropy. For example, one of the most useful applications of video over text is appliance or automotive repair, but the ideal format would be an article interspersed with short video sections, not a video with a talking head and some ~static shaky cam taking up most of the time as the individual drones on about m…
Ay, there's the rub. Professional video makes tend to be pushed into making videos for a more general audience, and niche topics are left to first-timers who haven't developed video-making skills and (tend to) go on and on.
I've produced a few videos, and I was shocked at how difficult it was to be clear. I have the same problem with writing, but at least it's restricted in a way video making isn't. There's so many ways to make a video about something, and most of them are wrong!
Re: Show HN: 22 GB of Hacker News in SQLite
#206Earlier quoted context omitted.
thanks! i downloaded them and used python to make these
So cool! Would it be impossible fro me to use them on the Archive stats page ( https://hackerbook.dosaygo.com/?view=archive ) ? If you're okay with that any links/credit line details? Totally cool if not, just super interesting!
yes, please, that'd be cool! you can link my site grimridge.net if you'd like. here are the numbers so you can plot graphs that fit the site's style:
mean (all time): https://katb.in/yutupojerux
mean (since 2020): https://katb.in/omoyibisava
median (all time): https://katb.in/kilopofivet
median (since 2020): https://katb.in/ukefetuyuhi
Re: Show HN: 22 GB of Hacker News in SQLite
#207Don'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…
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.
Re: Show HN: 22 GB of Hacker News in SQLite
#208Earlier quoted context omitted.
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.
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!
Happy to help!
Re: Show HN: 22 GB of Hacker News in SQLite
#209Earlier quoted context omitted.
That's odd. If it was a VFS, that's not what I'd expect would happen. Maybe it's not a VFS?
What is a VFS?
https://sqlite.org/vfs.html
While designed for OS portability, you can use it to convince SQLite to read from something other than a file on disk.
Re: Show HN: 22 GB of Hacker News in SQLite
#210Earlier quoted context omitted.
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...
Your page is served over sqlitevfs with Range queries? Let's try this here.
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.