Funny you posted this now, I just recently researched Sqlite internals for my own project. Maybe you will find it interesting: https://twitter.com/high_byte/status/1607853384123703296
Interesting blog, I love that you keep consistency writing for so long.
It does amaze me the gaps that people have, though.
Then again, I have rarely run into performance issues with database engines. Either the ones I've had contact with (SQLite, MSSQL, Postgres) are very good at what they do (which I suspect they are), or my requirements were too puny for them to break a sweat (which I also suspect to be the case).
I did use MSSQL Studio's query analyzer once to fix a performance issue and was able to get the query in question from nearly an hour down to under a second. triumphant grin I don't remember the details of the query, but the solution was to use a hash join.