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
