Hacker News Reader

Comment by simonw | Hacker News Reader

I did some experiments around this idea a few years ago, partly inspired by the "35% Faster Than The Filesystem" article: https://www.sqlite.org/fasterthanfs.html

Here are the notes I made at the time: https://simonwillison.net/2020/Jul/30/fun-binary-data-and-sq...

I built https://datasette.io/plugins/datasette-media as a plugin for serving static files from SQLite via Datasette and it works fine, but honestly I've not used it much since I built it.

A related concept is using SQLite to serve map tiles - this plugin https://datasette.io/plugins/datasette-tiles does that, using the MBTiles format which it turns out is a SQLite database full of PNGs.

If you want to experiment with SQLite to serve files you may find my "sqlite-utils insert-files" CLI tool useful for bootstrapping the database: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...

View on HN

In terms of performance, the content hash based file name approach is easier to implement with SQLite storage. The content hash has to be generated just once during file upload, not every time the web server is restarted (or using a build step which does actual file renames). It is possible to do it dynamically for file system files (see https://github.com/benbjohnson/hashfs for a embedFS implementation), but the database makes it a bit easier.

This is pretty cool, but if you're rewriting resource names server side then you need some machinery to rewrite references to those resources. I see the example you linked shows the root HTML being generated server-side from a template so content hash names can be injected into the template. Looks reasonable. Not so great for dealing with things like `import` calls in javascript (for deferred loading of script modules), if those need to be rewritten as well.

Your JS bundler (if you use one!) might be effectively rewriting imports anyway so it can be convenient to do the content-hash-named-files rewrites in the same place. But not everyone wants to use a bundler for that.

Clace can be used to build hypermedia driven UI's, using Go templates and HTMX. https://clace.io/docs/develop/templates/#static-function gives the content hashed file name, the templates have to use that function. See https://github.com/claceio/apps/blob/f5566cea6061ec85ea59495... for an example, that game is running live at https://cowbull.co/

Clace can also run containers, where the UI is served by the container. For example, Streamlit/Gradio based apps. In that case, Clace acts like an app server and reverse proxy to the container, no file name rewrites are done.

Clace includes esbuild for being able to import npm packages as ES modules https://clace.io/docs/develop/javascript/. There is no support for any JS bundling.

Is there a way to sendfile from a SQLite database like xsendfile?

requests-cache caches requests in SQLite by (date,URI) IIRC. https://github.com/requests-cache/requests-cache/blob/main/r...

/? pyfilesystem SQLite: https://www.google.com/search?q=pyfilesystem+sqlite

/? sendfile mmap SQLite: https://www.google.com/search?q=sendfile+mmap+sqlite

https://github.com/adamobeng/wddbfs :

> webdavfs provider which can read the contents of sqlite databases

There's probably a good way to implement a filesystem with Unix file permissions and xattrs extended file attribute permissions atop SQLite?

Would SQLite be faster or more convenient than e.g. ngx_http_memcached_module.c ? Does SQLite have per-cell ACLs either?

Not all you asked but Sqlite archive can be mounted using fuse, https://sqlite.org/sqlar/doc/trunk/README.md

FUSE does local filesystems, and there are also HTTP remote VFS for sqlite.

sqlite-wasm-http is based on phiresky/sql.js-httpvfs, "a read-only HTTP-Range-request based virtual file system for SQLite. It allows hosting an SQLite database on a static file hoster and querying that database from the browser without fully downloading it." "but uses the new official SQLite WASM distribution." [1][2]

[2] sql.js-httpvfs: https://github.com/phiresky/sql.js-httpvfs

[1] sqlite-wasm-http: https://www.npmjs.com/package/sqlite-wasm-http

[3] sqlite3vfshttp: https://github.com/psanford/sqlite3vfshttp

HTTP Range requests: https://developer.mozilla.org/en-US/docs/Web/HTTP/Range_requ...

Using sqlite keeps a persistent open database connection, so you only have to send requests for content that sqlite has probably cached. When reading static files, you open, read, and close a file with every request, meaning more context switches, even though the filesystem layer will have cached the file content. If you want to speed this up, the appropriate solution is to add a caching front-end, not to turn everything into a database. It will be faster than using sqlite and easier to maintain and troubleshoot.

Well you can store metadata in SQLite. You can update metadata and files in a transaction. It already has a caching layer built in (I think 8mb by default, that might need some tuning for this use case). It is thread safe (I mean it is a giant rw mutex but whatever) Does not sound that bad to just use it instead of building a caching layer that might be marginally faster than sqlite. And if you restart your caching frontend thing, it will need to rebuild the cache using tons of syscalls. SQLite cache will fill up easier as it has less context switches.

One major downside I see would be backing up the database. You can do granular backups on a filesytem. Even rsync would work fine. You’d need to basically snapshot the entire thing here.

sqlite-rsync is on the road.

I have no idea why this comment was dead and I vouched for it.

Just recently the `sqlite3-rsync` tool was added to SQLite and it does point-in-time backup, effectively.

Isn't anything that avoids too many syscalls and copying between userspace and kernel 35% faster? Including filesystems that run completely on user space (not FUSE, FUSE calls go trough kernel)

In some ways, it seems surprising that avoiding open/read/write syscalls and caching in user space is only 35% faster.

My hypothesis is that the scenarios the SQLite team has setup don’t particularly exploit the fact that the SQLite metrics become more favorable the more files are accessed.

I took a look at the code, and the file based code is basically the worst case for the file system, accessing about as many files as it can.

Additionally, it's doing a lot of unnecessary work. It's using buffered I/O to do a full file read, and throwing away the buffer immediately, which is an expensive way to add an unnecessary memcpy. It's opening by full path instead of openat, which incurs a path lookup penalty.

I think the file based code can be quite a bit faster, if you put a bit of effort in.

I think in serious use, the gap would narrow, not grow.

Reply on news.ycombinator.com