MySQL vs. PostgreSQL vs. MariaDB

Get help choosing one of these Get news updates about these tools



PostgreSQL vs MySQL vs MariaDB - Help me Decide


PostgreSQL & MySQL are the two most popular open-source databases today.

The less popular MariaDB is a MySQL fork that happened after the acquisition of Sun Microsystems by Oracle on January 27, 2010.

Here's a graph representing the popularity of these 3 databases:

DB Ranking

(Source: db-engines.com)

PostgreSQL & MariaDB have been gaining ground recently, while MySQL is slowly declining. But that's not enough to make a decision.

So which one should you choose?

Let's look at this from different angles.

Performance

By running some benchmarks you can get a rough idea of how these databases compare to each other in terms of performance.

For this article, we going to use the HammerDB benchmarking tool.

This tool implements a standardized performance test called TPC-C.

Here's the description:

"TPC-C involves a mix of five concurrent transactions of different types and complexity. The database is comprised of nine types of tables with a wide range of record and population sizes."

Here's the setup:

  • Ubuntu 18.04
  • 1 GB RAM
  • 1 CPU
  • MySQL 5.7.23
  • MariaDB 10.1
  • PostgreSQL 10.5
  • Default settings

We ran the tests for 5 minutes for each database.

Here are the results:

  • PostgreSQL 18534 TPM, 7972 NOPM
  • MySQL 14051 TPM, 4659 NOPM
  • MariaDB 21441 TPM, 7172 NOPM

The meaning of these two values (TMP & NOPM) is described like this:

"TPM values cannot be compared between different database types. The NOPM value is based on a metric captured from within the test schema itself. As such NOPM (New Orders Per Minute) is a performance metric independent of any particular database implementation and is the recommended primary metric to use."

Performance Chart

Like any benchmark the results depend on the specific scenario the tests are run in.

This particular benchmark tries to reproduce the workload of any industry that must manage, sell or distribute a product or service.

Knowing that, we can conclude that both PostgreSQL & MariaDB are faster than MySQL in this particular scenario, with PostgreSQL coming on top of the raw performance game.

Features

These 3 databases support all the basic SQL operations you would expect.

But when it comes to more advanced features there is a lot of variability between them.

For example, PostgreSQL supports materialized views, while MySQL doesn't.

Here's a feature comparison table:

Feature PostgreSQL MySQL MariaDB
Materialized Views
Partial Indexes
Array Data Type
JSON Data Type
CHECK constraints ✓ (from version 10.2.1)
Replication
Full-Text Search
UPSERT
Common Table Expressions
Sequences

Looking at this table we can conclude that PostgreSQL has the most features implemented.

Besides these features, you can find some behavior differences.

For example, this query:

select 1000/0;

Returns NULL in MySQL, while PostgreSQL & MariaDB (since version 10.2.4) return a division by zero error.

This can be changed with SQL modes in MySQL.

Administration & Monitoring Tools

Because databases are critical software components there are dozens of tools available for administration, monitoring & troubleshooting.

Starting with the command-line applications:

  • mysql (MySQL, MariaDB)
  • psql (PostgreSQL)

These are great for basic administration tasks.

Why?

Because they come built-in with their respective servers so they are always available. There is nothing extra to install.

They both have a command history available, so you can re-run previously executed queries & commands.

And they have a set of bult-in commands that can facilitate interacting with the database.

For example, psql has the \d command to list all your databases, and mysql has the status command to get information like the server version & uptime.

Official graphical tools are also available.

They are:

Here's a screenshot from pgAdmin4:

![pgAdmin4]https://img.stackshare.io/stackups/postgresql_vs_mysql_mariadb_pgAdmin4.png)

And for MySQL Workbench:

MySQL Workbench

Now we move onto more specialized tools like pghero (PostgreSQL).

PgHero is "a performance dashboard for Postgres".

Here's a screenshot:

pghero

For MySQL, and MariaDB, you can use MySQL Tuner. This is a Perl script that will analyze your configuration file & database statistics to produce configuration recommendations.

Here's a screenshot:

mysqltuner

There are also log-parsing tools to help you find slow queries.

Here's pt-query-digest for MySQL, a tool for analyzing your logs & running queries to find the slowest queries so you can optimize them.

As you can see in this screen, it's also a command-line tool:

pt-query-digest

And here is pgBadger for PostgreSQL:

pgBadger

pgBadger is like pt-query-digest, but it will analyze PostgreSQL logs & slowest queries.

It looks like PostgreSQL has more graphical tools available, so if you are more comfortable using tools with graphical frontends, instead of command-line applications, then you may want to keep this in mind when making your decision.

Extensibility

You can install plugins & extensions if you need extra features for your database.

Extending MySQL & MariaDB is mostly done in the form of UDFs (User-Defined Functions), which are small plugins written in C.

However, it doesn't seem to be a very popular feature, so it's hard to find any useful UDF extensions.

PostgreSQL has some helpful extensions:

  • PostGIS adds spatial search capabilities for geographic information systems (GIS)
  • HStore allows you to use key-value storage without having to install a specialized database
  • Multicorn allows you to query other sources of data (like Redis, Amazon S3, or even Twitter!) as if they were real tables

If you need any of these extensions then PostgreSQL might be a great choice for you.

Final Considerations

To make a good decision you should consider what your needs are.

  • Is your application going to do a lot of work with geographic information? Then the PostGIS extension would be of great help.
  • Could your application benefit from specific features that each database supports, like materialized views or partial indexes? Then choose that database.
  • Do you need more hosting & support options? Then MySQL or MariaDB may be a better fit.

We hope you found this comparison useful!

MySQL vs PostgreSQL vs MariaDB - Comparison Table

Favorites

396

Favorites

405

Favorites

68

Hacker News, Reddit, Stack Overflow Stats

  • 1.11K
  • 8.42K
  • 540K
  • 8.36K
  • 8.07K
  • 92K
  • 43
  • 384
  • 5.51K

GitHub Stats

Description

What is MySQL?

The MySQL software delivers a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL Server is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software.

What is PostgreSQL?

PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions.

What is MariaDB?

Started by core members of the original MySQL team, MariaDB actively works with outside developers to deliver the most featureful, stable, and sanely licensed open SQL server in the industry. MariaDB is designed as a drop-in replacement of MySQL(R) with more features, new storage engines, fewer bugs, and better performance.

Pros

Why do developers choose MySQL?
  • Why do you like MySQL?

    Why do developers choose PostgreSQL?
  • Why do you like PostgreSQL?

    Why do developers choose MariaDB?
  • Why do you like MariaDB?

    Cons

    What are the cons of using MySQL?
    Downsides of MySQL?

    What are the cons of using PostgreSQL?
    No Cons submitted yet for PostgreSQL
    Downsides of PostgreSQL?

    What are the cons of using MariaDB?
    No Cons submitted yet for MariaDB
    Downsides of MariaDB?

    Companies

    What companies use MySQL?
    2566 companies on StackShare use MySQL
    What companies use PostgreSQL?
    2243 companies on StackShare use PostgreSQL
    What companies use MariaDB?
    404 companies on StackShare use MariaDB

    Integrations

    What tools integrate with MySQL?
    74 tools on StackShare integrate with MySQL
    What tools integrate with PostgreSQL?
    88 tools on StackShare integrate with PostgreSQL
    What tools integrate with MariaDB?
    10 tools on StackShare integrate with MariaDB

    What are some alternatives to MySQL, PostgreSQL, and MariaDB?

    • MongoDB - The database for giant ideas
    • Microsoft SQL Server - A relational database management system developed by Microsoft
    • SQLite - A software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine
    • Memcached - High-performance, distributed memory object caching system

    See all alternatives to MySQL

    Latest News

    MySQL @ FOSSASIA, 2018
    Using dbdeployer in CI tests
    Archiving MySQL Tables in ClickHouse
    PostgreSQL rocks, except when it blocks: Understandi...
    9.5.11
    9.6.7
    Bitten by MariaDB 10.2 Incompatibile Change
    MariaDB 10.2.13, MariaDB Connector/ODBC 3.0.3 and Ma...
    MariaDB Server 10.2.13 now available


    Interest Over Time


    Get help choosing one of these