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

81–90 of 252 posts

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

#81

Post by phiresky »

BMorearty wrote: post #64

This is fantastically creative. And the author does a great job of starting out by describing why this is useful. And then using SQLite to insert and update DOM elements? Holy cow, icing on the cake. Unlike the first part, there’s no explanation of why you’d want to do that. But by that point I was so drawn in that I didn’t care and was just enjoying the ride.

Yeaah I felt like at that point the article was already long enough so I didn't bother describing the DOM part too much - even though I spent more time implementing that than I did implementing the rest ;)

Basically SQLite has a virtual table mechanism [1] where you have to define a few functions that figure out how to scan your "fake" table / which indices to use and then how to read / write the actual data. I hook into this mechanism and redirect the request to DOM functions like querySelector() etc. Then there's the issue about SQLite being fully synchronous, but I have to run it in a WebWorker - and the WebWorker can't actually access the DOM and it can only communicate asynchronously with the main thread... So I have to do some weird stuff with SharedArrayBuffer and Atomics.wait to make that work [2].

[1] https://www.sqlite.org/vtab.html [2] https://github.com/phiresky/sql.js-httpvfs/blob/master/src/v...

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

#82

Post by tehbeard »

formerly_proven wrote: post #77
tehbeard wrote: post #68

Earlier quoted context omitted.

Lunr needs the full index on client though, right? Being able to use fts-5 without the penalty of having to pull down the whole index make it work much better at larger scales, even with the penalty of additional network requests.

This could be genuinely interesting for tools like e.g. sphinx-doc, which currently has a client-side search that does indeed ship the entire index to the client.

There's probably a dataset size tradeoff with small enough number of documents, but even then this could have the option of if db is < x MB total, fetch all in async task and then use that.

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

#83

Post by rakoo »

CyberRabbi wrote: post #38
formerly_proven wrote: post #35

Earlier quoted context omitted.

This particular demo page actually makes queries against an almost 700 MB large (fills one CD!) SQLite database. Because the amount of data read is almost negligible (few hundred kB), performance is limited by latency (as you say). However, high-latency links also tend to be slower, so downloading the entire database a-priori would almost always be much slower. For example, on a 1 megabit/s link with 300 ms RTT, one…

Right but that is an artificially created demo by the author to justify the solution being presented (no offense). The question is how common are ~GB large SQLite databases in the real world relative to databases that are ~MB large? In my experience SQLite databases of millions of rows of raw tabular data tend to compress very well into dozens of megabytes. Indeed SQLite is often touted as a file format for applicati…

I'm running magnetico (https://github.com/boramalper/magnetico) on my VPS. I currently have an index of 1.6M magnet links stored in a 5GB database.

SQLite is most interesting not when the database is small, but when there are very few writes and all you do is reading. You can also look at https://datasette.io/ and see how SQLite is perfect for representing a lot of datasets and querying them

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

#84

Post by tehbeard »

CyberRabbi wrote: post #37
mcculley wrote: post #26

Earlier quoted context omitted.

That is hilariously wrong for a lot of use cases. I will find this very handy for some SQLite databases I have that are several GBs in size. I am looking right now at using this contribution.

It’s not hilariously wrong or wrong at all that over high latency links this would be virtually unusable. It’s certainly possible that people are using SQLite databases with sizes on the order of gigabytes but in my experience those are the exception not the rule.

Over high latency links most anything interesting is virtually unusable, so stop using it as a high horse to stand upon.

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

#85

Post by phiresky »

brandmeyer wrote: post #69
phiresky wrote: post #46

Earlier quoted context omitted.

Huh, that's actually kind of a worst case I didn't think about: Since you're doing a reverse table scan my "sequential access" detection doesn't kick in. If you do the same query but with a forward scan it should fetch roughly the same amount of data but only do like 5 HTTP requests since the request size doubles for every sequential access. e.g.: select country_code, long_name from wdi_country where rowid >= 164 ord…

> I’ve set the page size to 1 KiB for this database. > That’s because I implemented a pre-fetching system that tries to detect access patterns through three separate virtual read heads and exponentially increases the request size for sequential reads. > Since you're doing a reverse table scan my "sequential access" detection doesn't kick in. You know, starting off with the default 4kB page size naturally adds some re…

That's true, but it also means that random access will always use at least that amount of data even if it only has to fetch a tiny amount. I did a few (non-scientific) benchmarks on a few queries and 1kB seemed like an OK compromise.

And note that the request chunk size is bound to the SQLite page size, and to change that page size you have to rewrite the whole DB. So it can't be set on the fly unless you have multiple copies of the database.

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

#87

Post by crazygringo »

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

Not all webservers support/enable it, so YMMV.

But as long as you're dealing with a known server that does, then gravy!

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

#88

Post by CyberRabbi »

brandmeyer wrote: post #57
CyberRabbi wrote: post #38

Earlier quoted context omitted.

Right but that is an artificially created demo by the author to justify the solution being presented (no offense). The question is how common are ~GB large SQLite databases in the real world relative to databases that are ~MB large? In my experience SQLite databases of millions of rows of raw tabular data tend to compress very well into dozens of megabytes. Indeed SQLite is often touted as a file format for applicati…

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?

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

#90

Post by pjc50 »

phiresky wrote: post #85
brandmeyer wrote: post #69

Earlier quoted context omitted.

> I’ve set the page size to 1 KiB for this database. > That’s because I implemented a pre-fetching system that tries to detect access patterns through three separate virtual read heads and exponentially increases the request size for sequential reads. > Since you're doing a reverse table scan my "sequential access" detection doesn't kick in. You know, starting off with the default 4kB page size naturally adds some re…

That's true, but it also means that random access will always use at least that amount of data even if it only has to fetch a tiny amount. I did a few (non-scientific) benchmarks on a few queries and 1kB seemed like an OK compromise. And note that the request chunk size is bound to the SQLite page size, and to change that page size you have to rewrite the whole DB. So it can't be set on the fly unless you have multip…

1kb fits in most IP MTU sizes, so that seems reasonable.
Post reply on HN