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

Hosting SQLite databases on GitHub Pages or any static file hoster

phiresky.github.io

Post reply on HN
Print view View original

101–110 of 252 posts

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#101

Post by detaro »

CyberRabbi wrote: post #88
brandmeyer wrote: post #57

Earlier quoted context omitted.

My team has a few TB of data in SQLite files that are themselves dozens of GB each. We're using them as a replacement for leveldb's sstables, but with the structure of full SQL. It is highly effective.

Do you think your team’s usage of SQLite is representative of the average SQLite user?

Where has it been suggested that this is the best solution for "the average SQLite user", instead of a tool you can use if it fits your requirements? To take your 10MB number, the article starts by mentioning you can probably just download the entire thing if you aren't above that exact same number.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#102

Post by throwawayboise »

watermelon0 wrote: post #50
WJW wrote: post #43

Earlier quoted context omitted.

So assuming no country has a name longer than 98 characters and that all country codes are 2 characters, that is over 500% overhead? Are you missing a /s in your post?

Have you actually read the article? SQLite is unmodified, and thinks it runs on a virtual file system, which fetches file chunks via HTTP range headers. It's REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.

Do most static site hosters support range requests?

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#103

Post by cookguyruffles »

phiresky wrote: post #56
WJW wrote: post #43

Earlier quoted context omitted.

So assuming no country has a name longer than 98 characters and that all country codes are 2 characters, that is over 500% overhead? Are you missing a /s in your post?

Since random accesses across the internet are really slow, for this kind of fairly small table (where SQLite stores the row data inline within the B-Tree of the table) it basically fetches the whole content for each row - so even if you query only the long_name and country_code column, it will in fact fetch the data of all 29 columns in that table. If you want it to fetch less data for querying a subset of columns, y…

Nothing to add to the conversation, just wanted to say I absolutely adore this. Years ago I used to think it'd be nice if search engines simply exposed all their shards to clients and let clients do all the results merging and suchlike. Of course that's probably a terrible idea in practice for performance, but this library is definitely implementing the spirit in a very practical way!

It also reminded me of a vague inverse of this hack. In old versions of Qemu (possibly it is still implemented, but I have vague memories it got ripped out), you could point Qemu at a directory on disk and it'd produce an emulated floopy disk drive with a virtual FAT12 image containing the directory contents. AFAIK it didn't keep the actual data in memory, I guess all it needed was file sizes to know how to build a virtual memory mapping that contained the filesystem metadata + proxied reads from the underlying files for data sectors. I look forward to seeing your implementation of this concept in a virtualized SQLite file GraphQL proxy ;)

edit: insane, it still exists and apparently supports write mode?! https://en.wikibooks.org/wiki/QEMU/Devices/Storage#Virtual_F...

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#104

Post by dspillett »

throwawayboise wrote: post #102
watermelon0 wrote: post #50

Earlier quoted context omitted.

Have you actually read the article? SQLite is unmodified, and thinks it runs on a virtual file system, which fetches file chunks via HTTP range headers. It's REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.

Do most static site hosters support range requests?

Most web servers do out of the box, so I would assume most do. Basically all unless they have some reason to turn range processing off or are running a custom/experimental/both server that have implemented the feature (yet).

Not supporting range requests would be a disadvantage for any service hosting large files. Resuming failed long downloads wouldn't work so users might not be happy and there would be more load on your bandwidth and other resources as the AU falls back to performing a full download.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#105

Post by CyberRabbi »

detaro wrote: post #101
CyberRabbi wrote: post #88

Earlier quoted context omitted.

Do you think your team’s usage of SQLite is representative of the average SQLite user?

Where has it been suggested that this is the best solution for "the average SQLite user", instead of a tool you can use if it fits your requirements? To take your 10MB number, the article starts by mentioning you can probably just download the entire thing if you aren't above that exact same number.

I made two claims:

> this would be unusable over high latency links.

That is objectively true

> SQLite databases of pure data usually aren’t over 10MB in size.

No one here has refuted this point.

Any other counterargument is addressing a claim I did not make.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#106

Post by f430 »

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

mind blown. how is this possible???

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#107

Post by cxr »

phiresky wrote: post #6
spamalot159 wrote: post #3

This is really cool! I wonder what the restrictions are and if we would ever be able to write to a SQLite db like this in the future. This could push more to the front end without needing to write apis.

The main restriction is that the DB really needs well fitting indexes, otherwise querying is really slow and fetches a lot of data. Regarding writing: You could of course implement a writing API with POST requests for changing pages of the database - but then you would lose most of the benefits of this (not requiring any special kind of server). I also thought about implementing a kind of overlay filesystem, where ch…

> I also thought about implementing a kind of overlay filesystem, where chunks that are written to the file are stored in a local storage so the modified data is available locally while still reading everything else from the remote database.

If ever the intent were to involve eventually persisting those changes, then it would be worthwhile looking at remoteStorage, which works like this.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#110

Post by pfundstein »

OOPMan wrote: post #100
watermelon0 wrote: post #50

Earlier quoted context omitted.

Have you actually read the article? SQLite is unmodified, and thinks it runs on a virtual file system, which fetches file chunks via HTTP range headers. It's REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.

It's impressive on one hand. On the other it's still a lot of overhead.

I would say it's less overhead than downloading the entire db to query it locally...? What is your suggestion for accessing a static database with less overhead?
Post reply on HN