I checked the SQLite docs. They didn't explicitly say that LENGTH reads the full blob data, but it suggested for strings, it calculated length on the fly by looking for the first null byte. I'm assuming for BLOB types, SQLite iterates through the full contents of the column
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