I want to have SQLite as my default database for 2 second initial setup. I have people that want the simplest website possible and most of the times I use a static website generator to do my job.
Why not use SQLite instead and if the website gains traffic, to be able to switch to a different database?
Plugin compatibility is going to be a huge issue here. There are probably also a lot of queries within core itself that would not work in other DBs.
Yes, I think that is not easy at all.As written there https://codex.wordpress.org/Using_Alternative_Databases there is a plugin for postregresql https://wordpress.org/plugins/postgresql-for-wordpress/ but is very old with no updates.Add support can be very dramatic for backwards but if we find someone that analysis how works we can see in case.
SQlite doesn't play nice with concurrent PHP write access. However, I'd love to have SQLite support.
I think the proposal ages ago was a database abstraction layer so plugins don't know what database is actually being used.
I'm using https://github.com/aaemnnosttv/wp-sqlite-db as a drop-in replacement for a blog and works wonders. I have reported though some weird issues with WooCommerce and as soon as we fix those, I will be using it exclusively for small to nearly medium websites.
About concurrency, there's pragma WAL support and solves that issue for you.
SQLite works just fine for the average website.
Imagine, Pieter Levels use it for remoteok.io and some other websites of his, so...yeah, it can work without a sweat if used properly.