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

241–250 of 252 posts

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

#241

Post by no-s »

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.

> It's REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.

the harsh reality is that doing sensible queries that only reference and return the data actually needed always makes things faster. Even with server DBMS. Oh, how many times have I lamented the naive "select *" for forcing all the row contents even when there was index coverage for the actually needed data.

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

#242

Post by winrid »

philshem wrote: post #183
winrid wrote: post #180

Doesn't the webserver have to seek from the beginning of the ~600mb file to the range you want, unless the file is in memory?

No, see here, for example https://news.ycombinator.com/item?id=27018194

That doesn't answer the question.

Yes the web server supports range requests. Yes it only returns 50kb.

But what mechanism is letting it scan to just those ranges in the binary file. Doesn't the file system make you seek to the starting block and then read from there?

The point is, while it looks very efficient, there might be a crap ton of IO going on for your little 50kb response.

EDIT: probably the webserver is doing an fseek(), and performance will vary based on file system impl. This is something I will need to benchmark.

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

#243

Post by OOPMan »

pfundstein wrote: post #110
OOPMan wrote: post #100

Earlier quoted context omitted.

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?

Since it's a static database and the queries against it are most likely going to be static, just pre-run the queries and store the results statically in a more space-efficient format. When you're on a dogshit internet connection in a 3rd world country 50kb can actually be pretty unpleasant. Try rate limiting your internet to EDGE rated to see what I mean.

I'm not saying the whole thing isn't impressive, just that the concept itself is one of those "because I can" rather "because I should" things, which kinda devalues it a whole lot.

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

#244

Post by londons_explore »

kelnos wrote: post #233
pjc50 wrote: post #90

Earlier quoted context omitted.

1kb fits in most IP MTU sizes, so that seems reasonable.

Do most HTTP responses have less than ~500 bytes of headers? I guess specifically here, GH pages' responses. It looks like one of the requests made to the DB included a little over 700 bytes of response status line and headers, so that would probably end up spilling into more than one response packet, unfortunately.

With http/3 header compression, I assume the answer is yes.

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

#246

Post by diedyesterday »

This hack reminds me of Godel's hacking of an axiomatic mathematical system, to prove and plant his incompleteness theorem;

This is really a great hack: using a system in a unexpected and creative way for which it wan not originally deigned, but which is also very useful and nice.

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

#247

Post by mohanmca »

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…

Amazing use of SQLite! Thanks!

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

#248

Post by rewq4321 »

simonw wrote: post #47
bbkane wrote: post #45

Earlier quoted context omitted.

Would it be possible to use a datasette frontend with this as a "backend" and statically host the whole thing?

Not easily - Datasette is written in Python, so you could try running it in WebAssembly (like Mozilla did for Jupyter with https://hacks.mozilla.org/2019/03/iodide-an-experimental-too... ) but it would be enough work that it might be easier to reimplement a subset of Datasette directly in JavaScript.

I've got no experience with Datasette, but if it's all in pure python, then you can use Pyodide[0] to run it within a wasm module. I was surprised how easy it was - took about 15 minutes and I had a Python package working perfectly in the browser. The Python package is about 3x faster than the JS/wasm version, but I'm guessing that performance gap will narrow when we get wasm-gc.

[0] https://github.com/pyodide/pyodide

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

#249

Post by hinkley »

dathinab wrote: post #95
tlb wrote: post #74

The question I had is answered by this line of code: xhr.setRequestHeader("Range", "bytes=" + from + "-" + to); I am a little surprised you can just do that. In https://github.com/phiresky/sql.js-httpvfs/blob/master/src/l...

Range headers are a pretty standard tools to e.g. continue interrupted downloads and similar. Any well designed system, especially if it has static sources and is server cached should support it. Surprisingly many web-frameworks don't support it out of the box, or don't support it well. Either way gh-pages are static content and probably with some server side regional caches, so I'm not surprised it works.

Range headers are also how DownloadThemAll works.

If you're pulling a single TCP stream across a crowded network, you get maybe 1/40th of the available bandwidth. If you do four range requests instead, you might see >3/40th of the bandwidth.

This place we were contracting at, the managers were widely rumored to stream sports games at their desks, and our release cycle happened to fall on a game day. My poor coworker was downloading our installer every week, and the ETA was over 40 minutes. "Are you using DTA?" "No, what's that?" ETA: 12 minutes.

12 minute pauses in the middle of a manual process are a lot easier to stomach than 40 minutes. Especially if something goes wrong and you have to do it twice.

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

#250

Post by hinkley »

phiresky wrote: post #215
yoz wrote: post #174

As everyone else has been saying, this is amazing work. It sounds like the biggest issue with loading the page is the initial sql.js download - it's about 1.2MB, is that right? Might it be feasible to easily strip down SQLite so that it only compiles the parts for read-only use? The browser version is obviously somewhat read-only but that's because of the sandbox. I'm talking about excluding the code for CREATE, UPDA…

The actual transferred data for the sqlite code should only be 550kB (gzip compression). Stripping out the write parts is a good idea. SQLite actually has a set of compile time flags to omit features [1]. I just tried enabling as many of those as possible, but it didn't seem to reduce wasm size much, though I might be doing something wrong. There's also no easy flags to disable CREATE / UPDATE / INSERT . [1] https://…

I smell a series of pull requests...
Post reply on HN