PostgreSQL vs. MySQL

PostgreSQL and MySQL are two of the most widely used open source database management systems. But what exactly are their differences and similarities and which of the two tools is best suited for different deployment scenarios?

Both PostgreSQL and MySQL are databank management systems (DBMS). For the most part, the systems tend to be more similar than different. However, a comparison of PostgreSQL vs MySQL highlights some subtle differences that may or may not be beneficial to your project. 

Both systems use the SQL programming language as a central interface for interaction with databases and their contained data. The best known is probably the SELECT statement for executing queries. This allows data to be found and output within the database. Furthermore, there are various SQL commands to control the DBMS.

The functional scope of SQL is defined in various standards. Common implementations cover the standard quite well. When comparing PostgreSQL vs MySQL, PostgreSQL is more powerful and supports a wider range of functions than MySQL.

An important difference between PostgreSQL and MySQL is their basic architecture. Both systems are relational database management systems (RDBMS). However, PostgreSQL can do a lot more, because it’s an object-relational DBMS (ORDBMS).

Both DBMSs are based on a storage engine as an interface for storing data on physical data carriers. For high-performance access, indexes are used that refer to the individual database entries. There are various storage engines and index methods, each with their advantages and disadvantages.

MySQL was developed in the mid-1990s by MySQL AB in Sweden. The company was acquired by Sun Microsystems in 2008 and by Oracle in 2010. Because of a mistrust of the open source community surrounding Oracle, `MariaDB´ became the commercial fork of MySQL. This was to ensure that the project remained under an open source license.

During the boom years of the World Wide Web, MySQL became a popular component of the ubiquitous LAMP stack. The database software is now added alongside Linux, Apache and PHP as part of most web hosting plans. MySQL soon became the standard for web projects based on relational databases.

PostgreSQL was originally conceived as ‘Postgres’, a successor to the DBMS Ingres. It was developed in the mid-1980s at the University of California, Berkeley. The code was published under the ‘Berkeley Software Distribution’ (BSD) license. In the mid-1990s, the switch to SQL as a uniform interface took place alongside the name change to ‘PostgreSQL’. Both names are still used today.

In the words of IBM, PostgreSQL is:

Both PostgreSQL and MySQL simplify working with relational databases. Both systems understand SQL commands to create, modify and fill tables, and execute queries. A comparison of PostgreSQL vs MySQL shows basic differences in their functionality which are also apparent from their architectures.

While MySQL is a pure relational databank management system (RDBMS), PostgreSQL is an object-relational DBMS (ORDBMS). PostgreSQL supports a range of concepts known from object-oriented programming. These include user-defined data types, combined data types and inheritance. PostgreSQL is more powerful than MySQL, but also more complex.

Data types are the foundation for solid database design and the productive use of a database. When designing tables, you specify the type of data contained in individual columns.

Database performance is a complex topic. Different DBMSs have certain advantages and disadvantages depending on usage. In general, MySQL is considered extremely performant, especially when the database is accessed ‘read-heavy’, i.e. in read mode. This is the case with content management systems like WordPress, which mainly read content from the database and deliver it to visitors.

Unlike MySQL, PostgreSQL often delivers better performance for write-heavy operations. Furthermore, the ORDBMS is better suited for data warehousing solutions and other systems for ‘Online Analytical Processing’ (OLAP). PostgreSQL supports multiple connections but has higher memory requirements.

Database performance is a complex topic. Different DBMSs have certain advantages and disadvantages depending on usage. In general, MySQL is considered extremely performant, especially when the database is accessed “read-heavy”, i.e. in read mode. This is the case with content management systems like WordPress, which mainly read content from the database and deliver it to visitors.

Unlike MySQL, PostgreSQL often delivers better performance for write-heavy operations. Furthermore, the ORDBMS is better suited for data warehousing solutions and other systems for “Online Analytical Processing” (OLAP). PostgreSQL supports multiple connections but has higher memory requirements.

Relational DBMSs ensure the consistency and availability of stored data. This is also referred to as the ‘ACID’ properties. PostgreSQL supports the ACID properties; with MySQL this depends on the storage engine used.

The situation is similar regarding ‘Multiversion Concurrency Control’ (MVCC), which ensures data consistency in the event of simultaneous database access. With PostgreSQL MVCC is a given, while with MySQL it depends on the storage engine. In terms of security, MySQL provides TLS encryption. PostgreSQL still uses the older SSL standard.

An important aspect of working with DBMS is the support of different admin interfaces. Both PostgreSQL and MySQL have a command line interface (CLI) with psql and mysql, respectively. Using the CLI tools, you can connect to the database and execute SQL code via direct input or a script file.

In addition to command line interfaces, PostgreSQL and MySQL have web-based and native graphical user interfaces (GUI). Their dedicated import and export tools allow database backups to be created and restored. PostgreSQL comes with pg_dump and pg_restore and is more powerful in terms of backup than the MySQL backup using MySQL dump.

By comparing PostgreSQL vs MySQL, it’s clear that they’re very different, but which of the two database management systems should you be using for your project? Fortunately, the answer is simple. Use PostgreSQL if you have special database requirements. Where that’s not the case, MySQL is sufficient.

In other words, you’d use PostgreSQL to implement the website of a bank or a critical institution. The full ACID compliance pays off here. Greater demand for stability and data consistency justifies the higher complexity of the ORDBMS. Furthermore, sufficient resources are available for a high-performance PostgreSQL environment.

Another application of PostgreSQL is where a project architecture requires the management of sophisticated data models. To map complex object hierarchies or where inheritance is required as a central component of the data model, the use of the powerful ORDBMS is a good option. This may save the use of object-relational mapping (ORM).

For small to medium web projects, MySQL is the better choice. The RDBMS is less demanding in terms of server resources. It’s easier to find an experienced and affordable MySQL admin. Strong performance when reading data means it’s a good option for websites and small online stores.

Lastly, it should be noted that PostgreSQL and MySQL can be used in tandem. This is particularly attractive for data warehousing solutions. Usually, one or more outward-facing MySQL instances are used in such a setup. They collect data and pass it to a central PostgreSQL installation running evaluations and analyses.