Post­greSQL vs. MySQL

Post­greSQL and MySQL are two of the most widely used open source database man­age­ment systems. But what exactly are their dif­fer­ences and sim­i­lar­i­ties and which of the two tools is best suited for different de­ploy­ment scenarios?

Both Post­greSQL and MySQL are databank man­age­ment systems (DBMS). For the most part, the systems tend to be more similar than different. However, a com­par­i­son of Post­greSQL vs. MySQL high­lights some subtle dif­fer­ences that may or may not be ben­e­fi­cial to your project. 

Both systems use the SQL pro­gram­ming language as a central interface for in­ter­ac­tion 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. Fur­ther­more, there are various SQL commands to control the DBMS.

The func­tion­al scope of SQL is defined in various standards. Common im­ple­men­ta­tions cover the standard quite well. When comparing Post­greSQL vs. MySQL, Post­greSQL is more powerful and supports a wider range of functions than MySQL.

An important dif­fer­ence between Post­greSQL and MySQL is their basic ar­chi­tec­ture. Both systems are re­la­tion­al database man­age­ment systems (RDBMS). However, Post­greSQL can do a lot more, because it’s an object-re­la­tion­al DBMS (ORDBMS).

Both DBMSs are based on a storage engine as an interface for storing data on physical data carriers. For high-per­for­mance access, indexes are used that refer to the in­di­vid­ual database entries. There are various storage engines and index methods, each with their ad­van­tages and dis­ad­van­tages.

MySQL was developed in the mid-1990s by MySQL AB in Sweden. The company was acquired by Sun Mi­crosys­tems in 2008 and by Oracle in 2010. Because of a mistrust of the open source community sur­round­ing Oracle, “MariaDB” became the com­mer­cial 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 ubiq­ui­tous 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 re­la­tion­al databases.

Post­greSQL was orig­i­nal­ly conceived as “Postgres”, a successor to the DBMS Ingres. It was developed in the mid-1980s at the Uni­ver­si­ty of Cal­i­for­nia, Berkeley. The code was published under the “Berkeley Software Dis­tri­b­u­tion” (BSD) license. In the mid-1990s, the switch to SQL as a uniform interface took place alongside the name change to “Post­greSQL”. Both names are still used today.

In the words of IBM, Post­greSQL is:

Both Post­greSQL and MySQL simplify working with re­la­tion­al databases. Both systems un­der­stand SQL commands to create, modify and fill tables, and execute queries. A com­par­i­son of Post­greSQL vs. MySQL shows basic dif­fer­ences in their func­tion­al­i­ty which are also apparent from their ar­chi­tec­tures.

While MySQL is a pure re­la­tion­al databank man­age­ment system (RDBMS), Post­greSQL is an object-re­la­tion­al DBMS (ORDBMS). Post­greSQL supports a range of concepts known from object-oriented pro­gram­ming. These include user-defined data types, combined data types and in­her­i­tance. Post­greSQL is more powerful than MySQL, but also more complex.

Data types are the foun­da­tion for solid database design and the pro­duc­tive use of a database. When designing tables, you specify the type of data contained in in­di­vid­ual columns.

Database per­for­mance is a complex topic. Different DBMSs have certain ad­van­tages and dis­ad­van­tages depending on usage. In general, MySQL is con­sid­ered extremely per­for­mant, es­pe­cial­ly when the database is accessed “read-heavy”, i.e. in read mode. This is the case with content man­age­ment systems like WordPress, which mainly read content from the database and deliver it to visitors.

Unlike MySQL, Post­greSQL often delivers better per­for­mance for write-heavy op­er­a­tions. Fur­ther­more, the ORDBMS is better suited for data ware­hous­ing solutions and other systems for “Online An­a­lyt­i­cal Pro­cess­ing” (OLAP). Post­greSQL supports multiple con­nec­tions but has higher memory re­quire­ments.

Database per­for­mance is a complex topic. Different DBMSs have certain ad­van­tages and dis­ad­van­tages depending on usage. In general, MySQL is con­sid­ered extremely per­for­mant, es­pe­cial­ly when the database is accessed “read-heavy”, i.e. in read mode. This is the case with content man­age­ment systems like WordPress, which mainly read content from the database and deliver it to visitors.

Unlike MySQL, Post­greSQL often delivers better per­for­mance for write-heavy op­er­a­tions. Fur­ther­more, the ORDBMS is better suited for data ware­hous­ing solutions and other systems for “Online An­a­lyt­i­cal Pro­cess­ing” (OLAP). Post­greSQL supports multiple con­nec­tions but has higher memory re­quire­ments.

Re­la­tion­al DBMSs ensure the con­sis­ten­cy and avail­abil­i­ty of stored data. This is also referred to as the “ACID” prop­er­ties. Post­greSQL supports the ACID prop­er­ties; with MySQL this depends on the storage engine used.

The situation is similar regarding “Mul­ti­ver­sion Con­cur­ren­cy Control” (MVCC), which ensures data con­sis­ten­cy in the event of si­mul­ta­ne­ous database access. With Post­greSQL MVCC is a given, while with MySQL it depends on the storage engine. In terms of security, MySQL provides TLS en­cryp­tion. Post­greSQL still uses the older SSL standard.

An important aspect of working with DBMS is the support of different admin in­ter­faces. Both Post­greSQL and MySQL have a command line interface (CLI) with psql and mysql, re­spec­tive­ly. 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 in­ter­faces, Post­greSQL and MySQL have web-based and native graphical user in­ter­faces (GUI). Their dedicated import and export tools allow database backups to be created and restored. Post­greSQL comes with pg_dump and pg_restore and is more powerful in terms of backup than the MySQL backup using MySQL dump.

By comparing Post­greSQL vs. MySQL, it’s clear that they’re very different, but which of the two database man­age­ment systems should you be using for your project? For­tu­nate­ly, the answer is simple. Use Post­greSQL if you have special database re­quire­ments. Where that’s not the case, MySQL is suf­fi­cient.

In other words, you’d use Post­greSQL to implement the website of a bank or a critical in­sti­tu­tion. The full ACID com­pli­ance pays off here. Greater demand for stability and data con­sis­ten­cy justifies the higher com­plex­i­ty of the ORDBMS. Fur­ther­more, suf­fi­cient resources are available for a high-per­for­mance Post­greSQL en­vi­ron­ment.

Another ap­pli­ca­tion of Post­greSQL is where a project ar­chi­tec­ture requires the man­age­ment of so­phis­ti­cat­ed data models. To map complex object hi­er­ar­chies or where in­her­i­tance 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-re­la­tion­al 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 ex­pe­ri­enced and af­ford­able MySQL admin. Strong per­for­mance when reading data means it’s a good option for websites and small online stores.

Lastly, it should be noted that Post­greSQL and MySQL can be used in tandem. This is par­tic­u­lar­ly at­trac­tive for data ware­hous­ing solutions. Usually, one or more outward-facing MySQL instances are used in such a setup. They collect data and pass it to a central Post­greSQL in­stal­la­tion running eval­u­a­tions and analyses.