Re: PostgreSQL vs MariaDB - Mailing list pgsql-general

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