WordPress should be on SQLite by default. Most installations run only on one sever and are rather small (less than a few GB database size). And not a lot of inserts/updates to the DB.
In those cases SQLite will probably have much better speed and a lower memory footprint.
Those are orthogonal and not mutually exclusive. I have a couple wordpress sites that are static.
There are a number of different ways to do it which range from Next.js with WP as a headless CRM (my current preference), or using a static site generator plugin inside of WP. I can recommend "Simply Static" as a handy plugin that (combined with nginx) will make as much of your site as possible static but still updates quickly and can allow some dynamism.
But it does introduce complexity, and the more frequently the site changes the less it makes sense. As the site grows also most sites end up wanting dynamic stuff that often can't (or shouldn't) be done on the client-side, slowly making the static build less and less useful. It's just not that simple.
Yes, but you can still build a static site reading the content from a SQLite file instead of a directory of HTML files. That’s the beauty of SQLite: this is “just” a database library and file format.
On WordPress, you can change a tag, hit save, and the change is live.
On a static site, the change would look more like: edit a file, rebuild the site, deploy to production.
I left WordPress for static sites 8 years ago and never looked back. It's so nice to eliminate the database and web server from your stack and have everything under source control.
On my internal network servers I use wp-supercache to generate static pages on page visit, then I use a shell script that iterates through all possible article, rss, tag, and category pages with curl at varying intervals, forcing a cache build. In parallel I rsync all the images and statically built pages to my public edge. The public only gets to interact with content that is completely static and don't have php installed at all. It is easy enough, and the only real way to allow people to install whatever dumb wordpress plugins that they want on the internally installation without getting the sites owned all the time.
For an affiliate site with millions of pages you need much more to make WordPress sluggish. On the other hand, it only takes a single badly written plugin to achieve the same effect. :D
I host quite a lot and the size is around 40-80 MB for presentation sites. Shops are around 100-300 MB. So a nice fit for SQLite.
Would be a really nice options, but it should be part of the normal WP distribution.
In those cases SQLite will probably have much better speed and a lower memory footprint.