Thread: 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
choosingthe 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] 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] 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
choosingthe 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

P {margin-top:0;margin-bottom:0;}

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:

p {margin-top:0; margin-bottom:0}

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.

P {margin-top:0;margin-bottom:0;}

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.

>
> Thomas
>
>