One of the wacky architectural choices I made with PicoShare was to store all file data in SQLite as BLOB data. This is an unusual choice, as most apps store file uploads on the filesystem. But putting it in SQLite means I get cloud replication "for free" with @litestreamio
I still didn't love the idea of storing the redundant file size, and I considered alternatively creating a virtual SQLite table.
But we'd still have to populate the virtual table still at app load time, which would take ~10s per GB of data
https://www.sqlite.org/vtab.html
Hope you enjoyed following along! Here's the full PR with the fix: https://github.com/mtlynch/picoshare/pull/221
Creating the index is a one-line fix, and it doesn't have the drawback my original PR did of storing a redundant copy of the file size. https://github.com/mtlynch/picoshare/pull/230/files
Storing file data in SQLite leads to odd behavior like this, so I suspected the bottleneck was somewhere in my SQLite logic.
Here's the code to retrieve file metadata. I keep one table for file metadata and another table for file data, broken into 32 KB chunks.