PostgreSQL vs MariaDB

Inzamam Shafiq wrote on 3/24/23 4:07 AM:

Hi Team,

Hope you�are�doing well.

Can someone�please list pros and cons of MariaDB vs PostgreSQL that
actually needs serious consideration while choosing the right database
for large OLTP DBs (Terabytes)?

That's a very broad question, which will take far more effort to answer
than most people are willing to provide.

I would suggest describing your specific use case and asking how well
PostgreSQL will fit it. You clearly have a lot of data, for example, so
is that distributed across many tables? Large blobs or lots of rows?
Lots of inserts? Reads? Updates? Those sorts of things.

Maybe somebody on this PostgreSQL list might even know MariaDB well
enough to compare and contrast the two for you.

On Fri, Mar 24, 2023 at 7:07 AM Inzamam Shafiq
<inzamam.shafiq@hotmail.com> wrote:

Can someone please list pros and cons of MariaDB vs PostgreSQL that actually needs serious consideration while choosing the right database for large OLTP DBs (Terabytes)?

For me, security is important. I don't want to do a lot of late night
patching, and I don't want to cleanup after a data breach. When
vendors pitch a product with a database, I insist on a PostgreSQL
backend.

MySQL makes regular appearances on BugTraq. MySQL has over 1700 CVEs
going back to 1999.[1]https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=MySQL It tells me there are problems with the
engineering process.

MariaDB adds additional CVEs on top of MySQL, but the count appears
low. I have never separated the purely MariaDB flaws from the
underlying MySQL flaws.

In contrast, PostgreSQL has about 240 CVEs going back to 1999.[2]https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=PostgreSQL It
tells me PostgreSQL has a better engineering process.

Jeff

[1]: https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=MySQL
[2]: https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=PostgreSQL

On Fri, Mar 24, 2023 at 3:50 PM Jeffrey Walton <noloader@gmail.com> wrote:

On Fri, Mar 24, 2023 at 7:07 AM Inzamam Shafiq

Can someone please list pros and cons of MariaDB vs PostgreSQL [...]

[...] MySQL has over [1700 CVEs going back to 1999.][1]
In contrast, PostgreSQL has about [240 CVEs going back to 1999.][2]

[1]: https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=MySQL
[2]: https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=PostgreSQL

OTOH, I checked a few, and they are not about PostgreSQL per-se,
but projects using PostgreSQL or extensions to PostgreSQL.
Thus I assume the same happens for MySQL.

Of course, I didn't check that many, just a small sample.

Also, there seems to be a low signal-to-noise ratio on CVEs.
See what [Richard Hipp of SQLite fame says about CVEs][3]https://sqlite.org/cves.html.
This (low quality of CVEs) comes up often on the SQLite Forum. FWIW.

Still, your input is insightful, qualitatively. Thanks, --DD

[3]: https://sqlite.org/cves.html

Here is an EDB presentation on this from 2019
https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison-syntax-performance-scalability-and-features
PostgreSQL vs. MySQL: A 360-degree Comparison [Syntax, Performance, Scalability and Features]<https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison-syntax-performance-scalability-and-features&gt;
In this blog, we will discuss the key differences in terms of performance, syntax, scalability, and features between PostgreSQL and Mysql. PostgreSQL and MySQL are both immensely popular open-source databases, and a variety of real-time applications today utilize both. MySQL is known to be the world’s most popular database, whereas PostgreSQL is known as the world’s most advanced RDBMS database.
www.enterprisedb.com

________________________________
From: Jeffrey Walton <noloader@gmail.com>
Sent: Friday, March 24, 2023 10:49 AM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: PostgreSQL vs MariaDB

On Fri, Mar 24, 2023 at 7:07 AM Inzamam Shafiq
<inzamam.shafiq@hotmail.com> wrote:

Can someone please list pros and cons of MariaDB vs PostgreSQL that actually needs serious consideration while choosing the right database for large OLTP DBs (Terabytes)?

For me, security is important. I don't want to do a lot of late night
patching, and I don't want to cleanup after a data breach. When
vendors pitch a product with a database, I insist on a PostgreSQL
backend.

MySQL makes regular appearances on BugTraq. MySQL has over 1700 CVEs
going back to 1999.[1]https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=MySQL It tells me there are problems with the
engineering process.

MariaDB adds additional CVEs on top of MySQL, but the count appears
low. I have never separated the purely MariaDB flaws from the
underlying MySQL flaws.

In contrast, PostgreSQL has about 240 CVEs going back to 1999.[2]https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=PostgreSQL It
tells me PostgreSQL has a better engineering process.

Jeff

[1]: https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=MySQL
[2]: https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=PostgreSQL

Inzamam Shafiq schrieb am 24.03.2023 um 12:07:

Can someone please list pros and cons of MariaDB vs PostgreSQL that
actually needs serious consideration while choosing the right
database for large OLTP DBs (Terabytes)?

As others have pointed out, this is really hard to quantify.

For large databases, I would assume that Postgres' parallel
queries is a major advantage over MariaDB

From a management perspective, having transactional DDL makes
a lot of things easier (or more robust)

I am maintaining a very high-level overview, if you are interested

https://www.sql-workbench.eu/dbms_comparison.html

I do not claim that this is complete or 100% accurate. And it focuses
on SQL level features. Not so much on management or monitoring features
which are typically a major concern in large scale databases.

In the end, each database product has its quirks and problems
(some more than others).

I think Postgres' weakest point is the MVCC implementation.
Which typically is not really a problem, but there are usage
patterns where it can become a problem, especially if not dealt
with properly.

Thomas

On Fri, Mar 24, 2023 at 4:07 AM Inzamam Shafiq
<inzamam.shafiq@hotmail.com> wrote:

Can someone please list pros and cons of MariaDB vs PostgreSQL that actually needs serious consideration while choosing the right database for large OLTP DBs (Terabytes)?

Think about what you want/need from the database for your project and
then see which engine best satisfies those requirements. All modern
RDBMS engines can handle terabytes of data. Relatively static data
(mostly reads) or heavy insert will be fine with either. With large
databases, UPDATEs and DELETEs may require more maintenance with
Postgres in the form of tweaking autovacuum settings.

https://www.sql-workbench.eu/dbms_comparison.html

For a basic comparison of Postgres vs MariaDB, just uncheck all the
other engines at the top. Go down the list of rows, especially the
ones where only one of the two support a feature. If you don't know
what a feature does or what use cases it would be for, this would
provide an excellent opportunity to become better acquainted with it.

For example, if you end up needing bitemporal temporal table support
out of the box, go with MariaDB. On the other hand if you want the
most flexibility in data types and modeling your data, Postgres is
going to win out.

* Transactional DDL
* Clustered indexes
* DDL event triggers
* Alter table used in a view

Go down the list. What do you need? What do you think you'll need?
What would be nice to have?

Do you want to host it yourself or go to one of the cloud providers
for a fully managed option? If self-hosted, use whatever you like.
Hack it to your specs. If managed in the cloud, Postgres and MySQL are
more commonly available than MariaDB, but worth checking out ahead of
time.

Cheers,

Miles

Hi Ben,

We have a complex running, and we will be having a lot of Insert, update and deletes. We have many partitioned tables with huge data and some complex SQL is written at the application logic, some analytical functions, union/intersect etc. clauses and also we have materialized views used at the DB level. There are some triggers and complex procedures as well.

Regards,

Inzamam Shafiq
Sr. DBA
________________________________
From: Ben Chobot <bench@silentmedia.com>
Sent: Friday, March 24, 2023 7:08 PM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: PostgreSQL vs MariaDB

Inzamam Shafiq wrote on 3/24/23 4:07 AM:
Hi Team,

Hope you are doing well.

Can someone please list pros and cons of MariaDB vs PostgreSQL that actually needs serious consideration while choosing the right database for large OLTP DBs (Terabytes)?

That's a very broad question, which will take far more effort to answer than most people are willing to provide.

I would suggest describing your specific use case and asking how well PostgreSQL will fit it. You clearly have a lot of data, for example, so is that distributed across many tables? Large blobs or lots of rows? Lots of inserts? Reads? Updates? Those sorts of things.

Maybe somebody on this PostgreSQL list might even know MariaDB well enough to compare and contrast the two for you.

What RDBMS is your data currently on?

On 3/25/23 01:15, Inzamam Shafiq wrote:

Hi Ben,

We have a complex running, and we will be having a lot of Insert, update
and deletes. We have many partitioned tables with huge data and some
complex SQL is written at the application logic, some analytical
functions, union/intersect etc. clauses and also we have materialized
views used at the DB level. There are some triggers and complex procedures
as well.

Regards,

/Inzamam Shafiq/
/Sr. DBA/
----------------------------------------------------------------------------
*From:* Ben Chobot <bench@silentmedia.com>
*Sent:* Friday, March 24, 2023 7:08 PM
*To:* Inzamam Shafiq <inzamam.shafiq@hotmail.com>
*Cc:* pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
*Subject:* Re: PostgreSQL vs MariaDB
Inzamam Shafiq wrote on 3/24/23 4:07 AM:

Hi Team,

Hope you are doing well.

Can someone please list pros and cons of MariaDB vs PostgreSQL that
actually needs serious consideration while choosing the right database
for large OLTP DBs (Terabytes)?

That's a very broad question, which will take far more effort to answer
than most people are willing to provide.

I would suggest describing your specific use case and asking how well
PostgreSQL will fit it. You clearly have a lot of data, for example, so is
that distributed across many tables? Large blobs or lots of rows? Lots of
inserts? Reads? Updates? Those sorts of things.

Maybe somebody on this PostgreSQL list might even know MariaDB well enough
to compare and contrast the two for you.

--
Born in Arizona, moved to Babylonia.

Στις 24/3/23 13:07, ο/η Inzamam Shafiq έγραψε:

Hi Team,

Hope you are doing well.

Can someone please list pros and cons of MariaDB vs PostgreSQL that
actually needs serious consideration while choosing the right database
for large OLTP DBs (Terabytes)?

I would say that for small shops and simpler apps MySQL / MariaDB may be
fine. If you go to enterprise class apps and infrastructure you'll start
to need more, which means PostgreSQL.

For instance : unnest , transactional DDL, the extensions system, custom
types, the MVCC etc those are very important features that some
commercial DBs lack.

Thanks.

Regards,

/Inzamam Shafiq/
/Sr. DBA/

--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt

On 2023-03-24 07:07, Inzamam Shafiq wrote:

Hi Team,

Hope you are doing well.

Can someone please list pros and cons of MariaDB vs PostgreSQL that
actually needs serious consideration while choosing the right database
for large OLTP DBs (Terabytes)?

Hi Inzamam,

I will have my take as well, but note I have much more experience with
MySQL/MariaDB and mostly from 10 years ago (although I did use both in
the last decade too, mostly for hobby and a bit of PostgreSQL at work,
and I have both running on my workstation).

First of all unless you plan on licensing Oracle for MySQL support, you
should probably go with MariaDB (which is what you seem to consider
already). I've known and used MySQL before the MariaDB fork (and even
before Sun's acquisition), and MariaDB is still heavily developed with
open bug trackers and many 3rd party companies specializing in
MySQL/MariaDB support.

Having a sysadmin background, I find MariaDB to be easier to understand
and administer as a server application. In the main engines, tables are
straight up files on disk (for InnoDB which is now the default engine, a
file-per-table option also makes this possible). There isn't really a
concept of tablespaces, OTOH you can just move some files and symlink
them (while the DB is down of course) to get some tables onto bigger or
faster disks.

Recent versions of InnoDB (shortly after the MariaDB fork at least) have
had a lot of scalability and instrumentation improvement (a lot of it
from Percona's XtraDB fork), and also allow you to further separate the
common data files such as using separate files for the doublewrite
buffer and redo logs (write-only except during crash recovery; perfect
for spinning disks) from other read/write data files (containing undo
logs and system tables amongst others, and table data when not using
file-per-table).

There's obviously the plugable engines (it appears PostgreSQL is
implementing this too now), I'm less familiar with the latest
development of those and have mostly used InnoDB/XtraDB but there's
quite a few very specialized engines too. One I find particularly
interesting is MyRocks which is optimized for flash storage with
compression and can do high performance bulk inserts from files.

OTOH my experience with PostgreSQL is that it seems to have greater
support for some SQL features and concepts, or at least used to. I'm not
sufficiently SQLiterate to give many specifics but I remember seeing a
few examples in the past, one was lack of sequences which appears to
have been added about 5 years ago (before that one could use
auto_increment keys to get similar functionality).

From my perspective PostgreSQL appears to be more similar to other
database engines when it comes to managing tablespaces, schemas, etc.,
that said I had only limited experience with using Oracle, Sybase, DB2
and MSSQL, and not really anything about managing tablespaces/schemas.
Also unlike MariaDB, Postgresql can version DDL too (in InnoDB they
cause an implicit commit and rollbacks are no longer possible for the
transaction executing it).

I feel there may also likely more edge cases that you may have to be
aware for some specific operations with MariaDB (it's well documented
too) esp. with replication... but maybe that's just me knowing it
better, and it's mostly from 10y old experience (it tend to be getting
better over time and I haven't worked on any replicated setup lately).

So, TL;DR if you're a real DBA with experience with other commercial DB
engines, I think you will find yourself more at ease with PostgreSQL,
and it will likely be easier to port statements from other engines.

Someone with a strong sysadmin background, will likely be more
comfortable setting up and maintaining MariaDB, and some of its plugable
engines may also be worth considering, but that really depend on the
type of load and hardware you will be using.

I know there's very good instrumentation to troubleshoot performance
issues with MariaDB/InnoDB, something I'm absolutely not familiar with
PostgreSQL...

Regards,

Thomas

On 3/28/23 06:44, Thomas Guyot wrote:

On 2023-03-24 07:07, Inzamam Shafiq wrote:

Hi Team,

Hope you are doing well.

Can someone please list pros and cons of MariaDB vs PostgreSQL that
actually needs serious consideration while choosing the right
database for large OLTP DBs (Terabytes)?

Someone with a strong sysadmin background, will likely be more
comfortable setting up and maintaining MariaDB, and some of its
plugable engines may also be worth considering, but that really depend
on the type of load and hardware you will be using.

I believe this as well. Also PostgreSQL is more loved by developers due
to its academic and scientific origins.

Regarding the rest of commercial DB systems, using my PgSQL experience I
had no problems living with MS SQL Server, and I kinda felt at home when
it came to monitor MS SQL Server as well. Of course our main DB is
PostgreSQL.