Post­gr­eSQL vs. MySQL

Post­gr­eSQL 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­il­ar­it­ies and which of the two tools is best suited for different de­ploy­ment scenarios?

Both Post­gr­eSQL 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­is­on of Post­gr­eSQL vs MySQL high­lights some subtle dif­fer­ences that may or may not be be­ne­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­ment­a­tions cover the standard quite well. When comparing Post­gr­eSQL vs MySQL, Post­gr­eSQL is more powerful and supports a wider range of functions than MySQL.

An important dif­fer­ence between Post­gr­eSQL and MySQL is their basic ar­chi­tec­ture. Both systems are re­la­tion­al database man­age­ment systems (RDBMS). However, Post­gr­eSQL 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­form­ance access, indexes are used that refer to the in­di­vidu­al database entries. There are various storage engines and index methods, each with their ad­vant­ages and dis­ad­vant­ages.

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 ubi­quit­ous 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­gr­eSQL was ori­gin­ally conceived as ‘Postgres’, a successor to the DBMS Ingres. It was developed in the mid-1980s at the Uni­ver­sity of Cali­for­nia, Berkeley. The code was published under the ‘Berkeley Software Dis­tri­bu­tion’ (BSD) license. In the mid-1990s, the switch to SQL as a uniform interface took place alongside the name change to ‘Post­gr­eSQL’. Both names are still used today.

In the words of IBM, Post­gr­eSQL is:

Both Post­gr­eSQL 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­is­on of Post­gr­eSQL vs MySQL shows basic dif­fer­ences in their func­tion­al­ity 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­gr­eSQL is an object-re­la­tion­al DBMS (ORDBMS). Post­gr­eSQL supports a range of concepts known from object-oriented pro­gram­ming. These include user-defined data types, combined data types and in­her­it­ance. Post­gr­eSQL is more powerful than MySQL, but also more complex.

Data types are the found­a­tion for solid database design and the pro­duct­ive use of a database. When designing tables, you specify the type of data contained in in­di­vidu­al columns.

Database per­form­ance is a complex topic. Different DBMSs have certain ad­vant­ages and dis­ad­vant­ages depending on usage. In general, MySQL is con­sidered extremely per­form­ant, es­pe­cially 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­gr­eSQL often delivers better per­form­ance 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 Ana­lyt­ic­al Pro­cessing’ (OLAP). Post­gr­eSQL supports multiple con­nec­tions but has higher memory re­quire­ments.

Database per­form­ance is a complex topic. Different DBMSs have certain ad­vant­ages and dis­ad­vant­ages depending on usage. In general, MySQL is con­sidered extremely per­form­ant, es­pe­cially 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­gr­eSQL often delivers better per­form­ance 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 Ana­lyt­ic­al Pro­cessing” (OLAP). Post­gr­eSQL supports multiple con­nec­tions but has higher memory re­quire­ments.

Re­la­tion­al DBMSs ensure the con­sist­ency and avail­ab­il­ity of stored data. This is also referred to as the ‘ACID’ prop­er­ties. Post­gr­eSQL supports the ACID prop­er­ties; with MySQL this depends on the storage engine used.

The situation is similar regarding ‘Mul­tiver­sion Con­cur­rency Control’ (MVCC), which ensures data con­sist­ency in the event of sim­ul­tan­eous database access. With Post­gr­eSQL MVCC is a given, while with MySQL it depends on the storage engine. In terms of security, MySQL provides TLS en­cryp­tion. Post­gr­eSQL still uses the older SSL standard.

An important aspect of working with DBMS is the support of different admin in­ter­faces. Both Post­gr­eSQL and MySQL have a command line interface (CLI) with psql and mysql, re­spect­ively. 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­gr­eSQL 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­gr­eSQL 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­gr­eSQL 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­nately, the answer is simple. Use Post­gr­eSQL 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­gr­eSQL 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­sist­ency justifies the higher com­plex­ity of the ORDBMS. Fur­ther­more, suf­fi­cient resources are available for a high-per­form­ance Post­gr­eSQL en­vir­on­ment.

Another ap­plic­a­tion of Post­gr­eSQL is where a project ar­chi­tec­ture requires the man­age­ment of soph­ist­ic­ated data models. To map complex object hier­arch­ies or where in­her­it­ance 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­per­i­enced and af­ford­able MySQL admin. Strong per­form­ance when reading data means it’s a good option for websites and small online stores.

Lastly, it should be noted that Post­gr­eSQL and MySQL can be used in tandem. This is par­tic­u­larly at­tract­ive 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­gr­eSQL in­stall­a­tion running eval­u­ations and analyses.