5 ms·

What are some reasons to use MariaDB over PostgreSQL?

refset 2y ago
Native bitemporal tables: https://mariadb.com/resources/blog/temporal-tables-part-5/ https://mariadb.com/resources/blog/temporal-tables-part-5/
floating-io 2y ago
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.
- ReptileMan 2y ago
  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
  - aguaviva 2y ago
    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."
Bjartr 2y ago
For a legacy system originally built on MySQL back before Postgres was so strong.
garciasn 2y ago
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.
- floating-io 2y ago
  > 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.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...
  - justinclift 2y ago
    > based on the time period Is it the kind of thing where the TimescaleDB extension would make sense? https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb
    - floating-io 2y ago
      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.
- TkTech 2y ago
  > 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)
- mathnode 2y ago
  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.
evanelias 2y ago
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.
- mjevans 2y ago
  Partitioning has been supported for quite a while https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm... Logical replication... https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication.... https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#pglogical-2 https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#... https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.postgresql-rds-postgresql-full-load-pglogical.html 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-analyze-best-practice-tips 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-delete-operation-on-a-postgresql-database-table-unusually-very-slow 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 https://www.dragonflydb.io/faq/postgres-delete-performance
maxk42 2y ago
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 https://www.databasebenchmarks.net/benchmark-charts.html
- AlisdairO 2y ago
  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).
  - maxk42 2y ago
    The top-performing Postgres instance among all benchmarks is v. 11.0.1008: https://www.passmark.com/baselines/V11/advanced-database-benchmark.php?id=69451299 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.
    - AlisdairO 2y ago
      * 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.
- g8oz 2y ago
  Interesting, I found the page comparing performance on AWS useful. https://www.databasebenchmarks.net/benchmark-charts.html/?aws https://www.databasebenchmarks.net/benchmark-charts.html/?aw...
mathnode 2y ago
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.
phil21 2y 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.
ThatMedicIsASpy 2y ago
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.
SOLAR_FIELDS 2y ago
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
flemhans 2y ago
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.
danbarbarito 2y ago
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.

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

floating-io 2y ago

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.

ReptileMan 2y ago
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
- aguaviva 2y ago
  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."

Bjartr 2y ago

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

garciasn 2y ago

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.

floating-io 2y ago
> 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.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...
- justinclift 2y ago
  > based on the time period Is it the kind of thing where the TimescaleDB extension would make sense? https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb
  - floating-io 2y ago
    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.
TkTech 2y ago
> 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)
mathnode 2y ago
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.

evanelias 2y ago

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.

mjevans 2y ago
Partitioning has been supported for quite a while https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm... Logical replication... https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication.... https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#pglogical-2 https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#... https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.postgresql-rds-postgresql-full-load-pglogical.html 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-analyze-best-practice-tips 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-delete-operation-on-a-postgresql-database-table-unusually-very-slow 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 https://www.dragonflydb.io/faq/postgres-delete-performance

maxk42 2y ago

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 https://www.databasebenchmarks.net/benchmark-charts.html

AlisdairO 2y ago
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).
- maxk42 2y ago
  The top-performing Postgres instance among all benchmarks is v. 11.0.1008: https://www.passmark.com/baselines/V11/advanced-database-benchmark.php?id=69451299 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.
  - AlisdairO 2y ago
    * 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.
g8oz 2y ago
Interesting, I found the page comparing performance on AWS useful. https://www.databasebenchmarks.net/benchmark-charts.html/?aws https://www.databasebenchmarks.net/benchmark-charts.html/?aw...

mathnode 2y ago

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.

phil21 2y 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.

ThatMedicIsASpy 2y ago

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.

SOLAR_FIELDS 2y ago

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

flemhans 2y ago

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.

danbarbarito 2y ago

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.