streamlining HTTP request pattern with a sidecar file

[mlin]

Hi @phiresky, I wanted to share some hacking I did recently on sqlite_web_vfs that I think would benefit this project too: helper .dbi files

The idea is to scan the main database file and collect all the btree interior pages into a small sidecar file (itself a SQLite database). The VFS is then coded to download this file before accessing the main database, and serve requests for those pages from that local copy (otherwise fall through to HTTP requests on the main database as usual).

This can largely eliminate the sequential chains of tiny range requests we see SQLite make in the course of a btree lookup -- usually an overall latency win, even if few of the pages in the sidecar file are needed for any given query (since they're all fetched at once, and can service multiple queries). IME the btree interior pages are <1% of the main database but usually spread all throughout (and vacuum doesn't try to make them contiguous, unfortunately).

[phiresky]

Thanks for the comment! Yeah, that sounds like a great idea, and it's good to see someone has implemented it and shown that it does improve the performance.

For me I don't think I'll work myself on implementing this since it adds more SQLite-specific things to the virtual FS and my preference right now is to have it stay generic to be able to expand it to support other databases (better full-text-search) or generic files.