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.
I would say the main advantage is scale and uptime. If you need to replicate, duplicate, or maintain state beyond one server; there are very few good RDBMS options, let alone open source. The MariaDB ecosystem competes with IBM Purescale and Oracle RAC; it's hard to appreciate that, until you really need it.
My reason has been relative simplicity. PostgreSQL comes off as much more complex (though whether that is actually true is probably dependent on what you're doing with it).
I'm also not working with super-high-performance production-critical loads, though, so grain of salt and all that.
Mysql is mercurial, Postgres is git. With mysql everything comes somewhat intuitive. Postgres is antiintutive in a lot of places. Probably the people that created it didn't realize that the complexity of Oracle was not needed but was a job program for expensive consultants /s
That link isn't particularly convincing. As far as I can see, the only Postgres test performed on the hardware that the top MariaDB entries had was on a positively ancient Postgres version (9.2.1).
* In that link, V11 is not the version of Postgres, it's the version of the test. Scroll down to DB Version.
* Lots of versions are tested, but 9.2.1 is the only version I see on the same hardware that the top MariaDB versions are tested against. The others are on much weaker hardware.
* Postgres 9.2.1 is 12 years old.
All of these sibling comments are listing good technical reasons but the most compelling reason by far that I’ve run into for using MariaDB over Postgres is that the underlying software you are trying to deploy only supports MySQL/MariaDB. When my options are “MariaDB” or “Use an entirely different application” I often opt for MariaDB
I know it may seem like a weird reason but I think MySQL Workbench is a MUCH better tool than any other open source interface for interacting with PostgreSQL. I usually use PostgreSQL for other reasons, but MySQL Workbench is the main thing which makes the decision difficult for me.
They are honestly equally fine for most use cases. I find MariaDB tooling better and have a lower mental overhead dealing with it, but perhaps I'm just more used to it.
We used to hit a wall when reaching 100bn rows on MySQL but that was 15 years ago.
Scale and ease of “sysadmin” level management for things such as clustering, replication, etc.
Some of that will be personal preference but I’ve never found administration of a Postgres database cluster to be nearly as intuitive as MySQL/MariaDB.
From the experience of my one man big web project with 12 languages I had a hard time setting up a search that ignores things like éê from other languages in postgres while mariadb innodb just ignores it.
> Maria supports partitioning, Postgres (as of my last knowledge) does not.
PostgreSQL's manual indicates that partitioning is a thing[1]. Is the something different than what you're thinking of?
One of my projects has the need to drop millions of rows a month based on the time period, and I've been considering a switch to postgres because they also have a module that will do that automatically.
In my case, pg_partman is actually more than enough. It's just a situation where the data isn't useful after a certain point. Timescale as I understand it would be massive overkill for my situation.
postgres have table partitions now, mariadb can however partition a table over multiple servers or shards using the engines like spider and connect, or proxies like maxscale and proxsql.
Local or remote, read your database manual about the fun and caveats that come from partitions.
* 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.