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, looking at key technical differences including indexes, query syntax, performance, replication, and clustering—everything you need to know to make an informed choice between these databases.
Who uses these databases?
Here are a few examples of companies that use these databases:
| Postgres | MySQL |
|
Uber |
Facebook |
How are Postgres and MySQL structured?
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 its core components. A table consists of rows, and each row contains the same set of columns. PostgreSQL uses primary keys to uniquely identify each row (aka, record) in a table, and foreign keys to assure the referential integrity between two related tables, but it's worth mentioning that PostgreSQL also supports many noSQL features as well.
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 or less the same feature set as PostgreSQL. Newer versions of MySQL (5.7+) even support some noSQL features.
Performance: Are indexes needed?
Indexes enhance database performance, as they allow the server to retrieve rows much faster by skipping to the most relevant part of the data. However, indexes add overhead to the system as a whole so should be used sensibly.
Different types of indexes serve different types of functions. B-tree indexes can be used across a wide variety of comparisons in expressions like =, >, >=, <, <=, or BETWEEN operators. One of the unique abilities of a B-tree index is that it allows developers to index on LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
In contrast, hash indexes are only used for equality comparisons. This is usually limited to =,<= or >=. In other words, your query optimizer will consider using a hash index when you use a column = “test” clause.
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.
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.
Materialized views in MySQL and Postgres
Materialized views are database objects that manage the results of a query that can be updated as needed from the original base table. These views can help improve performance by acting as a kind of pre-run query, especially if the query is complex.
When it comes to Postgres and MySQL, Postgres offers materialized views and MySQL does not. That means a well-managed Postgres database may outperform MySQL.
Data types & functionality
MySQL and Postgres both allow developers to use JSON as a datatype in tables. This gives engineers the ability to store complex nested data types in a single table which can improve performance.
Postgres has offered JSON for a while but MySQL didn’t provide JSON support until version 5.7.8.
That being said. Postgres also provides engineers the ability to store data in XML and arrays. In particular, an Array is not only a different data type, but Postgres also provides a host of functions you can use with those arrays that can be very helpful.
For example, Unnest is a helpful function that allows you to take an array and unpivot it in Postgres. You might have the 2D array [1,2] and [‘foo’,’bar’,’baz’] that you would like to unnest in Postgres. You can do so easily like this: unnest(ARRAY[1,2],ARRAY['foo','bar','baz']
The output will look like this:
1 foo
2 bar
NULL baz
Postgres’s ability to store data as a wide variety of data types as well as provide rich functionality when it comes to interacting with that data can make it a desirable option when it comes to picking your underlying database.
However, in many cases it isn’t beneficial to use arrays and JSON in your applications as it can be more difficult to update and might not meet your company’s normalization standards. In that case, MySQL’s “limitations” aren’t a problem.
Postgres and Notify/Listen
If we’re being honest, when comparing Postgres to MySQL there are a lot of similarities. But Postgres provides one very unique set of functionality: NOTIFY and LISTEN.
The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database.
NOTIFY provides a simple interprocess communication mechanism for a collection of processes that access the same PostgreSQL database. This means your Postgres database can send out events asynchronously in a way that’s very similar to a Pub/Sub model.
MySQL doesn’t provide this unique functionality. Instead, if you want to imitate this functionality you will either need to write some custom UDF or use a plugin.
Syntax: How are Postgres and MySQL 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 Postgres and MySQL deployed?
PostgreSQL was written in C. It has support for C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, and 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 was written in C and C++. It has support for C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, and 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 copy data from primary to replica databases. There are multiple benefits, with a few of them being:
- automating your backups
- spreading the load to improve performance
- working on a replica database to avoid damaging the performance of the primary database with long-running and intensive queries
Clustering, in the context of databases, refers to using shared storage and providing multiple database frontends for clients. The frontend 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 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, Postgres data won't be lost. With synchronous replication, each write waits until confirmation is received from both master and slave.
MySQL replication is one-way asynchronous replication where one server acts as a primary and others as replicas. 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 behind Postgres and MySQL and how are they supported?
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 on Github.
Along with a wealth of online documentation, PostgreSQL has a wide variety of community support, including mailing lists and IRC as well as third-party commercial support options.
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.
MySQL has a lot of available documentation, both official at MySQL.com and from community sites like the omnipresent StackOverflow and the more database-specific Stack Exchange for Databases.
Summary
All in all, as the popular saying goes: “Nobody was ever fired for buying IBM.” Both MySQL and Postgres are popular, reliable databases and you won’t be making a mistake by choosing either. We think Postgres has a bit of an edge because it’s standards-compliant, transactional and ACID-compliant out of the box, and has wide support for noSQL features, but the reality is that MySQL is pretty great as well and may even work better for your business’s needs.
No matter which database you choose, keep in mind that Panoply integrates with both Postgres and MySQL in just a few clicks, making it easy to store all your company’s data in one place and is designed for speedy analytical processing. Try it free today!
