The blog post isn't about Fly.io, it's about SQLite. Ben works here, but exclusively on Litestream (we sort of "acquired" Litestream some months ago). He's not writing about our product; he's writing about his project, and about SQLite.
We have Postgres as part of the platform (`fly postgres`), and not SQLite. But SQLite is super interesting to us, especially with Litestream, because it plays nicely with apps that are deployed close to their users. A good starting point: https://fly.io/blog/globally-distributed-postgres/ --- now, substitute an incarnation of SQLite with good read replicas, and in-process performance.
In this case fly.io (and others) are trying to change that by doing interesting things with storage of the database and the transaction logs. It works by hooking into various SQLite features (like virtual file systems and post transaction hooks) so your application doesn’t need to care about that layer and you just write you app with standard SQLite code.
The theory is you get best of both worlds - it could be pretty cool if it works out well.
Normal apps typically need MySQL or PostgreSQL, no?