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

161–170 of 229 posts

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

#161

Post by jtbaker »

nextaccountic wrote: post #103
Humphrey wrote: post #62

Earlier quoted context omitted.

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, sc…

That's neat, but.. is it just for cartographic data? I want something like a db with indexes

Look into using duckdb with remote http/s3 parquet files. The parquet files are organized as columnar vectors, grouped into chunks of rows. Each row group stores metadata about the set it contains that can be used to prune out data that doesn’t need to be scanned by the query engine. https://duckdb.org/docs/stable/guides/performance/indexing

LanceDB has a similar mechanism for operating on remote vector embeddings/text search.

It’s a fun time to be a dev in this space!

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

#162

Post by Xyra »

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

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

#164

Post by keepamovin »

Humphrey wrote: post #70
simonw wrote: post #68

Earlier quoted context omitted.

PMTiles is absurdly great software.

I know right! I'd never heard of HTTP Range requests until PMTiles - but gee it's an elegant solution.

Hadn't seen PMTiles before, but that matches the mental model exactly! I chose physical file sharding over Range Requests on a single db because it felt safer for 'dumb' static hosts like CF. - less risk of a single 22GB file getting stuck or cached weirdly. Maybe it would work

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

#165

Post by jacquesm »

keepamovin wrote: post #159
jacquesm wrote: post #66

Earlier quoted context omitted.

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 P…

Pretty neat project. I never thought you could do this in the first place, very much inspiring. I've made a little project that stores all of its data locally but still runs in the browser to protect against take downs and because I don't think you should store your precious data online more than you have to, eventually it all rots away. Your project takes this to the next level.

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

#166

Post by Xyra »

Paul-E wrote: post #19

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…

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.

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

#167

Post by peheje »

3eb7988a1663 wrote: post #10
carbocation wrote: post #5

That repo is throwing up a 404 for me. Question - did you consider tradeoffs between duckdb (or other columnar stores) and SQLite?

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

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

#168

Post by hyperbolablabla »

simonw wrote: post #68
Humphrey wrote: post #62

Earlier quoted context omitted.

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, sc…

PMTiles is absurdly great software.

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.

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

#169

Post by keepamovin »

ncruces wrote: post #94
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…

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.

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

#170

Post by keepamovin »

jacquesm wrote: post #165
keepamovin wrote: post #159

Earlier quoted context omitted.

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 P…

Pretty neat project. I never thought you could do this in the first place, very much inspiring. I've made a little project that stores all of its data locally but still runs in the browser to protect against take downs and because I don't think you should store your precious data online more than you have to, eventually it all rots away. Your project takes this to the next level.

Thanks, bud, that means a lot! Would like to see your versions of the data stored offline idea, it's very cool.
Post reply on HN