Posted by ajayvk 9 hours ago
using sqlite for static content might have other benefits, but i don't think that this is the main one
I've tried various solutions like litestream and even xdelta3 (which generates patches in excess of the actual changeset size), but I haven't found a solution I'm confident in other than backing up complete snapshots.
I can see how you'd use it for replication though.
Seeing as you need some kind of layer between web and sqlite, you might as well keep a layer between web and FS who nets you most or all of the benefits.
Use git to manage versions of the static files.
Use [git worktree][1] to have multiple separate working trees in different directories.
Use [openat()][2] in your app whenever serving a request for a static file. On each request, open the directory, i.e. `/var/www/mysite.com`, and then use `openat()` with the directory's file descriptor when looking up any files under that directory.
`/var/www/mysite.com` is a symbolic link to some working tree.
When modifying the site, make modifications to the git repository. Then check out the modified working tree into a new directory.
Create a symbolic link to the new directory and `mv` (rename) it to `/var/www/mysite.com`.
Then some time later, `rm -r` the old working tree.
[1]: https://git-scm.com/docs/git-worktree
[2]: https://pubs.opengroup.org/onlinepubs/9799919799/functions/o...
bad match -- git stores every version in the history forever. Do you really need every revision of a binary file, completely duplicated? big files, more bad
[1]: https://www.reddit.com/r/git/comments/wk2kqy/delete_files_un...
On the other hand, the state of art when it comes to performance is zero-copy from disk to NIC using io_uring and DMA, combined with NICs that support TLS & checksumming offloading and separated header and payload that can be filled independently by the OS and user-space.
I wonder if the authors of these projects ask themselves: what reasons are there to not do it like this? This thread has a few answers.
I am not claiming a SQLite database is always the right approach for serving static files. In my particular use case for Clace, I found a database to work better.
Something I want to try is using sqlite as my filesystem but just storing content hashes that would point to an S3-compatible object store, so you get the atomicity and rollbackability and all but you also get massive parallelization and multiregionality of an object store
Edit: I googled it again and find that multiple processes can read sqlite concurrently so shouldn't be a problem
https://stackoverflow.com/questions/4060772/sqlite-concurren...
but....the SQLite file is locked against reads while writing in order to achieve serializable isolation. which sort of indicates you're better off doing your database work in an offline file, then just swapping the new file for the old one that's in production. which sort of indicates....just use a tar file, or a separate directory that you swap in to update the new content.
it is so much easier to serve static files statically rather than from some program that's trying to manage live SQLite connections and achieve some weird "update concurrency" magic when this problem is not at all that hard to solve. It's fine to manage your CMS in a sqlite database but when you serve the content live and it's static, use static files.
Not with WAL: https://www.sqlite.org/wal.html