Earlier quoted context omitted.
Uploaded here: https://github.com/Quantum-Nomad/sqlite-s3vfs
Thanks for that, though actually it turns out I had them after all - I needed to run: git push --tags origin
All the better :)
Show HN: 22 GB of Hacker News in SQLite
91–100 of 229 posts
Re: Show HN: 22 GB of Hacker News in SQLite
#92That'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…
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.
Re: Show HN: 22 GB of Hacker News in SQLite
#93I 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…
That's odd. If it was a VFS, that's not what I'd expect would happen. Maybe it's not a VFS?
Re: Show HN: 22 GB of Hacker News in SQLite
#94Don'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 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...
Re: Show HN: 22 GB of Hacker News in SQLite
#95Earlier quoted context omitted.
Because anything that even slightly differs from the standard American phrasing of something must be "LLM generated" these days.
Sometimes I want to write more creatively, but then worry I’ll be accused of being an LLM. So I dumb it down. Remove the colorful language. Conform.
Fuck 'em.
Always write what you want, however you want to write it. If some reader somewhere decides to be judgemental because of — you know — an em dash or an X/Y comparison or a complement or some other thing that they think pins you down as being a bot, then that's entirely their own problem. Not yours.
They observe the reality that they deserve.
Re: Show HN: 22 GB of Hacker News in SQLite
#96Don'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
I want to see a bittorrent version :P
Re: Show HN: 22 GB of Hacker News in SQLite
#97It'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.
Re: Show HN: 22 GB of Hacker News in SQLite
#98Earlier quoted context omitted.
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 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.
Re: Show HN: 22 GB of Hacker News in SQLite
#99Alas, 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 commerc…
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?
Re: Show HN: 22 GB of Hacker News in SQLite
#100Earlier quoted context omitted.
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.
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.
yes, yt-dlp can download the transcript, and if it's not available i can get the audio file and run it through parakeet locally.