Hacker News Reader

Comment by lfmunoz4 | Hacker News Reader

What are some reasons to use MariaDB over PostgreSQL?

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

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

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).

> 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.

> PostgreSQL has gained autovacuum support.

That doesn't even remotely solve the inherent problems of Postgres's MVCC implementation. See https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...

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

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

My own take:

"With MySQL everything seems somewhat more intuitive at first, until it doesn't. Postgres seems antiintutive in some ways at first, until it isn't."

MariaDB scales much better, with native clustering support, native partitioning support, as well as better single-node performance: https://www.databasebenchmarks.net/benchmark-charts.html

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).

The top-performing Postgres instance among all benchmarks is v. 11.0.1008: https://www.passmark.com/baselines/V11/advanced-database-ben...

And I'm seeing versions tested up through 16.3 which was released in May. In fact even 9.2.1 is less than three years old.

   * 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.

This site is not a good like-for-like comparison.

Interesting, I found the page comparing performance on AWS useful.

https://www.databasebenchmarks.net/benchmark-charts.html/?aw...

Native bitemporal tables: https://mariadb.com/resources/blog/temporal-tables-part-5/

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.

They’re both great systems but there are a few differences primarily in small ways for most applications:

Maria supports partitioning, Postgres (as of my last knowledge) does not.

Unstructured data is more flexible in Maria natively, but Postgres can support it in a variety of ways.

You can find lists and lists of head to head comparisons out there which will highlight all of the niche differences that each brings over the other.

Ultimately either will work just fine for 99% of use cases.

> 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.

What am I missing?

[1] https://www.postgresql.org/docs/current/ddl-partitioning.htm...

> based on the time period

Is it the kind of thing where the TimescaleDB extension would make sense?

https://github.com/timescale/timescaledb

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.

> Maria supports partitioning, Postgres (as of my last knowledge) does not.

Modern versions of postgres have all the partioning features you'd expect (except automatic ranged partion creation)

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.

For a legacy system originally built on MySQL back before Postgres was so strong.

Reply on news.ycombinator.com