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.
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
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
Michael Lynch's personal Mastodon instance