postgreSQL is, according to this article by Andy Pavlo, doing very well in design wins for new projects. Could WordPress adopt it? It would be hard to do. Even if the core could be adapted, the vast ecosystem of plugins and themes is very MariaDB / MySQL centric. Here are some musings from the core folks.
Could sqlite be used for WordPress? Support is built into php. It sure would be great: it would take the famous five minute install down to two minutes. sqlite offer some concurrency on local file systems. The challenge for WordPress — indeed for any php-based web app — is the rapid coming and going of processes and database connections. It would probably work well, especially for small installations. But it would have the same SQL dialect compatibility issues as postgreSQL. There’s at least one attempt to do this.
Could duckdb be used for WordPress? No (at any rate not yet): it requires all processes except one to open the database file in read-only mode.
It’s my opinion that moving WordPress to a different DBMS will require a highly capable SQL dialect translation layer. WordPress core has the plumbing to do this sort of thing: a plugin can put a custom db.php file into the content directory. That’s how the wildly popular Query Monitor plugin works.
The development and testing required for a dialect translation layer is daunting. Such a project would require serious support from companies with money. But the long term cost and energy savings, especially for budget hosting companies, would be vast.