At scale, there are some workloads where it can be a great choice:
* InnoDB (default storage engine in MySQL and MariaDB) uses a clustered index, which can handle an extremely high volume of primary key range scan queries
* Ability to handle several thousand connections per second without needing a proxy or pool (the connection model in MySQL and MariaDB is multi-threaded instead of multi-process)
* Workloads that lean heavily on UPDATE or DELETE have terrible MVCC pain (vacuum) in Postgres, rarely a problem in MySQL or MariaDB due to using an undo log design
* Support for index hints and forced indexes, preventing huge outages when the query planner makes a random mistake at an off hour
* Built-in support for direct I/O is important for very high-volume OLTP workloads -- InnoDB's buffer pool design is completely independent of filesystem/OS caching
* If you need best-in-industry compression, the MyRocks storage engine is easy to use in MariaDB
* Logical replication can handle DDL out-of-the-box in FOSS MariaDB or MySQL, whereas in Postgres you must pay for an enterprise solution
* Much better collation support out-of-the-box
* Tooling ecosystem which includes multiple battle-tested external online schema change tools, for safely making alterations of any type to tables with billions of rows
* MariaDB has built-in support for using system-versioned tables, application-time periods, or both (bitemporal tables)
That all said -- Postgres is an amazing database with many awesome features which MariaDB lacks. Overall unless your situation is very high scale or an unusual edge-case, it's usually best to just go with what you know / what your team knows / what you can hire for, etc.
> Partitioning has been supported for quite a while
My comment (which you're replying to) didn't mention partitioning at all.
> Logical replication...
My comment specifically said that logical replication of DDL statements is not supported out-of-the-box in FOSS Postgres, which is absolutely accurate. See the very first thing mentioned on https://www.postgresql.org/docs/current/logical-replication-...
You can pay EnterpriseDB for a solution, among other vendors. That situation is far from ideal.
The fact that vacuum runs automatically does not stop stuck/slow vacuum and transaction ID wrap-around issues from being a death sentence for a high-write-volume Postgres instance. There are many stories on the internet about this, the Notion one (my employer) is here: https://www.notion.so/blog/sharding-postgres-at-notion
Logical replication does exist in pgsql, which is great. What it still lacks however (and I am sure they will very quickly catch up on) is the user facing process of being able to fix or sync a broken node without a rebuild. I'm also pretty sure pgsql logical replication is single threaded?
Things like pg_rewind are layered on fixes that other database users don't have to depend on or learn. Except Oracle (because it's a mess).
I very much appreciate your deep knowledge of MySQL, and willingness to share amidst the sea of postgres fans. There's a reason both are popular and widely used.
* InnoDB (default storage engine in MySQL and MariaDB) uses a clustered index, which can handle an extremely high volume of primary key range scan queries
* Ability to handle several thousand connections per second without needing a proxy or pool (the connection model in MySQL and MariaDB is multi-threaded instead of multi-process)
* Workloads that lean heavily on UPDATE or DELETE have terrible MVCC pain (vacuum) in Postgres, rarely a problem in MySQL or MariaDB due to using an undo log design
* Support for index hints and forced indexes, preventing huge outages when the query planner makes a random mistake at an off hour
* Built-in support for direct I/O is important for very high-volume OLTP workloads -- InnoDB's buffer pool design is completely independent of filesystem/OS caching
* If you need best-in-industry compression, the MyRocks storage engine is easy to use in MariaDB
* Logical replication can handle DDL out-of-the-box in FOSS MariaDB or MySQL, whereas in Postgres you must pay for an enterprise solution
* Much better collation support out-of-the-box
* Tooling ecosystem which includes multiple battle-tested external online schema change tools, for safely making alterations of any type to tables with billions of rows
* MariaDB has built-in support for using system-versioned tables, application-time periods, or both (bitemporal tables)
That all said -- Postgres is an amazing database with many awesome features which MariaDB lacks. Overall unless your situation is very high scale or an unusual edge-case, it's usually best to just go with what you know / what your team knows / what you can hire for, etc.