Thread

1. PostgreSQL vs MariaDB

   Inzamam Shafiq <inzamam.shafiq@hotmail.com> — 2023-03-24T11:07:11Z

   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)?
   
   Thanks.
   
   Regards,
   
   Inzamam Shafiq
   Sr. DBA
   

2. Re: PostgreSQL vs MariaDB

   Ben Chobot <bench@silentmedia.com> — 2023-03-24T14:08:18Z

   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.
   

3. Re: PostgreSQL vs MariaDB

   Jeffrey Walton <noloader@gmail.com> — 2023-03-24T14:49:42Z

   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] 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
   
   
   
   

4. Re: PostgreSQL vs MariaDB

   Dominique Devienne <ddevienne@gmail.com> — 2023-03-24T15:41:50Z

   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].
   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
   

5. Re: PostgreSQL vs MariaDB

   Sumit Sengupta <ssengupta@hotmail.com> — 2023-03-24T16:17:03Z

   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>
   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] 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
   
   
   

6. Re: PostgreSQL vs MariaDB

   shammat@gmx.net — 2023-03-24T16:27:33Z

   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
   
   
   
   

7. Re: PostgreSQL vs MariaDB

   Miles Elam <miles.elam@productops.com> — 2023-03-24T21:18:38Z

   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
   
   
   
   

8. Re: PostgreSQL vs MariaDB

   Inzamam Shafiq <inzamam.shafiq@hotmail.com> — 2023-03-25T06:15:51Z

   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.
   

9. Re: PostgreSQL vs MariaDB

   Ron Johnson <ronljohnsonjr@gmail.com> — 2023-03-25T06:43:54Z

   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.

10. Re: PostgreSQL vs MariaDB

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2023-03-25T15:44:06Z

    Στις 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
    

11. Re: PostgreSQL vs MariaDB

    Thomas Guyot <tguyot@gmail.com> — 2023-03-28T03:44:58Z

    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
    
    
    
    

12. Re: PostgreSQL vs MariaDB

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2023-03-28T06:10:42Z

    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
    >
    >