| 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. |
https://www.postgresql.org/docs/current/ddl-partitioning.htm...
Logical replication...
https://www.postgresql.org/docs/current/logical-replication....
https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#...
https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatab...
In 'recent years' (in database support terms), PostgreSQL has gained autovacuum support.
https://www.enterprisedb.com/blog/postgresql-vacuum-and-anal...
This stack overflow question was insightful, in that most of the slowness many experience may be related to foreign key check lookups on unindexed columns that point to external keys. https://dba.stackexchange.com/questions/328884/why-is-the-de... Partitioned data and batches to spread out updates also appear to be current best practices https://www.dragonflydb.io/faq/postgres-delete-performance