PostgreSQL Vs. MySQL

This series of posts analyzes how specific databases stack up against each other. We've covered the following comparisons so far:

In this post, we'll see how PostgreSQL stacks against MySQL. We’ll compare the key technical differences between PostgreSQL and MySQL: indexes, queries, performance, replication and clustering - everything you need to know about the differences between PostgreSQL and MySQL.

Who uses these databases?

Here are a few examples of companies that use these databases:

PostgreSQL: Apple, BioPharm, Etsy, IMDB, Macworld, Debian, Fujitsu, Red Hat, Sun Microsystem, Cisco, Skype. See the full list here.

MySQL: GitHub, US Navy, NASA, Tesla, Netflix, WeChat, Facebook, Zendesk, Twitter, Zappos, YouTube, Spotify. See the full list here.

What about database structure?

PostgreSQL: PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. PostgreSQL is ACID-compliant, transactional, has updatable and materialized views, triggers, and foreign keys. It also supports functions and stored procedures.

PostgreSQL uses tables, constraints, triggers, roles, stored procedures and views as the core components that you work with. A table consists of rows, and each row contains a same set of columns. PostgreSQL uses primary keys to uniquely identify each row (a.k.a. record) in a table, and foreign keys to assure the referential integrity between two related tables.

It's worth mentioning that PostgreSQL also supports many NoSQL features as well.

MySQL: MySQL is an open-source relational database management system (RDBMS). Just like PostgreSQL, and all other relational databases for that matter, MySQL uses tables as a core component and has more-less the same feature set as PostgreSQL.

To be fair and to not make this PostgreSQL vs. MySQL post all about how PostgreSQL is great, it’s worth mentioning that newer versions of MySQL (5.7+) also support some NoSQL features as well.

Are indexes needed?

Indexes enhance database performance, as they allow the database server to find and retrieve specific rows much faster than without an index. But, indexes add a certain overhead to the database system as a whole, so they should be used sensibly.

Without an index, the database server must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more costly the operation.

PostgreSQL: PostgreSQL includes built-in support for regular B-tree and hash indexes. Indexes in PostgreSQL also support the following features:  

  • Expression indexes: can be created with an index of the result of an expression or function, instead of simply the value of a column.
  • Partial indexes: index only a part of a table.


MySQL: Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions include the indexes on spatial data types that use R-trees. MySQL also supports hash indexes and the InnoDB engine uses inverted lists for FULLTEXT indexes.

How are their queries different?

PostgreSQL queries are, in most cases, exactly the same as MySQL queries.

Selecting records from the customer table

PostgreSQL query:
SELECT * FROM customer;

MySQL query:
SELECT * FROM customer;

Inserting records into the customer table

PostgreSQL query:
INSERT INTO customer(cust_id, branch, status) VALUES ('appl01', 'main', 'A');

MySQL query:
INSERT INTO customer(cust_id, branch, status) VALUES ('appl01', 'main', 'A');

Updating records in the customer table

PostgreSQL query:
UPDATE customer SET branch="main" WHERE custage > 2;

MySQL query:
UPDATE customer SET branch="main" WHERE custage > 2;

Where (and how) are these databases deployed?

PostgreSQL: PostgreSQL was written in C. It has support for the following programming languages: C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, Erlang. Typical installations are on various Linux servers, cloud-based or on premise. Leading cloud vendors have PostgreSQL support on their platforms. There is also a REST API for any Postgres database.

MySQL: MySQL was written in C and C++. It has support for the following programming languages: C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, Erlang. Again, cloud-based implementation and platform support are available. Customers can choose to use the Oracle Corporation or the open-source build.

Both databases are available on multiple operating systems.

What types of replication / clustering are available?

Replication is a process that enables you to have multiple copies of the data copied automatically from 'master' to 'slave' databases. There are multiple benefits, with a few of them being:

  • backup
  • spreading the load to improve performance
  • analytics team can work on one of the slave databases, thus not hurting the performance of the main database in case of long-running and intensive queries

Clustering, in the context of databases, refers to using shared storage and providing multiple database front-ends for clients. The front-end servers share an IP address and cluster network name that clients use to connect, and they decide among themselves who is currently in charge of serving client requests.

PostgreSQL: PostgreSQL has synchronous replication (called 2-safe replication), that utilizes two database instances running simultaneously where your master database is synchronized with a slave database. Unless both databases crash simultaneously, data won't be lost. With synchronous replication, each write waits until confirmation is received from both master and slave. For more information, please refer to the detailed wiki.

MySQL: MySQL replication is one-way asynchronous replication where one server acts as a master and others as slaves. You can replicate all databases, selected databases or even selected tables within a database.

MySQL Cluster is a technology providing shared-nothing (no single point of failure) clustering and auto-sharding (partitioning) for the MySQL database management system.

Internally MySQL Cluster uses synchronous replication through a two-phase commit mechanism to guarantee that data is written to multiple nodes. This contrasts with what is usually referred to as "MySQL Replication", which is asynchronous.

Who's currently behind the databases?

PostgreSQL: PostgreSQL is an open-source project maintained by PostgreSQL Global Development Group and its prolific community. Here's a full list of the contributors, and the source code is on Github.

MySQL: MySQL's source code is available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL is now owned by Oracle Corporation. For proprietary use, several paid editions are available and offer additional functionality.

There are also some forks, notably MariaDB and Percona Server. 

Who Provides Support and is There a Community Around It?

PostgreSQL: PostgreSQL has a wide variety of community and commercial support options available for users. The Community support includes mailing lists and IRC. This list of companies offers commercial support.

MySQL: MySQL also has a lot of community support options, as well as commercial. The popular support sites include MySQL.com and Percona

Who maintains the documentation?

PostgreSQL: There is a wealth of PostgreSQL information available online.

MySQL: MySQL has a lot of available documentation, both official and from the community.

Useful community sites are the omnipresent StackOverflow and a bit more database-specific Stack Exchange for Databases. 

We hope that this PostgreSQL vs MySQL post helped you by showing the technical differences between PostgreSQL and MySQL. We showed how MySQL queries differ from PostgreSQL queries, how PostgreSQL and MySQL deal with replication, clustering, etc. Also, we talked about community and the companies behind these databases.

All in all, as the popular saying goes:  “Nobody was ever fired for buying IBM.”

In that sense, you will not make a mistake by choosing either database, although PostgreSQL is becoming more popular. If you're looking for a solution that is standards-compliant, transactional and ACID-compliant out of the box, and has wide support for NoSQL features, then you should check out PostgreSQL. 

Get a free consultation with a data architect to see how to build a data warehouse in minutes.
Request Demo
Read more in:
Share this post:

Work smarter, better, and faster with weekly tips and how-tos.