It worked! Page load time dropped to 8ms.
I was confident that the SUM(LENGTH(chunk)) line was causing the latency.
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
Does calling LENGTH cause SQLite to read the full BLOB data into memory? That would certainly explain why it took 9 seconds to calculate the length of a 1.1 GB file.