Blog

PostgreSQL and MySQL are two extremely popular relational database management systems that have been improved upon for over two decades. Both solutions have their advantages and disadvantages. Which one is the best?

Most developers would probably say that PostgreSQL is ideal for large, complex analytical processes, while MySQL is better for websites or online transactions. Others would point out that PostgreSQL has many excellent features and native NoSQL capabilities that help with database challenges, while MySQL is lightweight and fast. But which database is objectively better?

PostgreSQL vs MySQL – where did they come from?

PostgreSQL, also known as Postgres, was started in 1986 at the University of California, Berkeley. It is currently developed by the global PostgreSQL Global Development Group. Postgres is known as “the most advanced open-source relational database in the world” and is used for critical tasks in large, medium, and small companies worldwide. It is also the most frequently chosen database by administrators and developers. The reasons for this are its many features, high scalability, and striving for maximum compatibility with the SQL standard. PostgreSQL allows for both local and cloud-based database use. It is also widely used on all platforms, including Docker containers.

MySQL, known as “the most popular database in the world” (although PostgreSQL’s popularity is still growing), had its first release in 1995. It was initially developed by Swedish company MySQL AB, which was acquired by Sun Microsystems in 2008. Oracle then acquired Sun Microsystems two years later. MySQL is available in both commercial and GPL versions. However, MySQL is not fully SQL-compliant, which may make it unsuitable for some applications. Additionally, MySQL’s integration capabilities are limited, making it harder to be part of heterogeneous database environments. Nevertheless, it is lightweight and fast, making it a popular choice for simple applications.

Comparing PostgreSQL and MySQL

PostgreSQL is an object-relational database, allowing for abstractions such as table inheritance and function overloading, which can be important in some applications. Postgres is written in the C language and supports communication using the following programming languages: C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, Erlang.

MySQL is a purely relational database written in C and C ++. Supports the following programming languages: C, C ++, Delphi, Perl, Java, Lua,.NET, Node.js, Python, PHP, Lisp, Go, R, D, Erlang.

The default Postgres configuration generally works better than the default MySQL settings (however, you can customize them to compensate for this). PostgreSQL is also known for protecting data integrity at the transaction level, making it less susceptible to data corruption. PostgreSQL also has an implemented MVCC (Multiverse Concurrency Control) mechanism for transaction management, allowing it to comply with the ACID principle (indivisibility, consistency, isolation, durability). MySQL is incompatible with ACID, and the way it supports foreign key references, auditing and transactions makes it less reliable than Postgres.

Database performance

PostgreSQL offers high speeds for both reading and writing, making it ideal for large, complex databases. It also provides concurrency without reading / writing locks, which allows it to process multiple threads simultaneously. Works great with complicated queries that require combining data from multiple tables.

MySQL works well when high read speed is required. Therefore, it excels in web applications. However, it has performance issues for complicated queries. Therefore, it is not entirely suitable as a platform for business analysis or data analysis, because it lacks a memory architecture enabling efficient analytical queries.

Security

Both PostgreSQL and MySQL use access control lists (ACLs) for user rights to databases, schemes and tables. Both solutions support encryption at the levels of the entire disk, individual files, individual tables or columns. They also support the same level of security by offering native support for encrypted database connections.

Data Types

PostgreSQL and MySQL support many different types of both traditional (TIMESTAMP, DATE, INTEGER) and complex data (TEXT, XML, JSON). However, PostgreSQL supports more of them and, very importantly, allows you to easily create your own data types:

• using the command CREATE TYPE
• combining basic types into logically related structures that better model stored data
• by using available extensions introducing support for more advanced types, for example related to geographical data.

Replication

Both PostgreSQL and MySQL have replication and clustering functions. MySQL supports master-slave and master-to-multiple-slaves mechanisms. All changes are therefore replicated by SQL, which means that replication can only be asynchronous – one server acts as master and the other as slave. This can be unfavorable in terms of performance and scalability.

The replication in PostgreSQL is based on WAL (Write Ahead Log) files. It is a faster and more reliable solution because it does not cause a large overhead on the master server. Postgres supports master-slave replication, including cascade replication, where one of the standby servers transfers changes from the master to other standby servers. The Postgres replication is called stream replication, which is asynchronous by default. If we want to provide an even lower risk of data loss on a standby server, we can enable operation in synchronous mode. Synchronous replication (also called 2-safe replication) is waiting for approval of changes to standby servers before proceeding to the next transaction. This means that data loss can only occur if both databases fail simultaneously. From version 10.PostgreSQL also supports an even more advanced – mechanism logical replication which allows, for example, to play only part of the changes in the main server to the standby server – even by bypassing the DELETE operation, thus creating an archive server.

JSON

PostgreSQL has been supporting JSON since version 9.2 and does so in a more advanced way than MySQL. What’s more, the JSONB data type available since version 9.4, which stores JSON in binary format, supports full-text indexing, which significantly speeds up full-text search in JSON documents. MySQL has started supporting JSON since version 5.7, which is two years later than PostgreSQL.

JSON data columns can be searched using SQL queries, and JSON attributes can be indexed. However, support for JSON-specific functions compared to PostgreSQL is limited. Here, too, there is a lack of full MySQL compatibility with SQL.

Scalability of tables

MySQL supports indexing and partitioning B-Tree, which improves query performance in larger tables. However, the lack of support for bitmap indexes limits administrators’ ability to fine-tune the database. PostgreSQL has several indexing options and three types of partitioning. Expression indexing, partial indexing, bitmap indexing and full-text indexing certainly contribute to improving query performance in larger size tables. In PostgreSQL, table partitions and indexes can be placed in separate table spaces on different disk file systems.

Data storage

Data storage is the fundamental ability of every database. Both PostgreSQL and MySQL allow you to define tabletspaces that allows administrators to specify additional locations for data storage. MySQL also supports several connected memory engines ( pluggable storage engines) helping to meet specific memory requirements for different types of applications. PostgreSQL does not support this functionality so far, but instead offers some functionality through access to data from other engines, using the foreign data wrappers mechanism compliant with the SQL / MED standard.

When PostgreSQL?

PostgreSQL is the best choice for applications with high transactions. It can satisfy various internet and mobile application services. PostgreSQL is also great as a data warehouse for running complex queries and reporting procedures on large amounts of data.

When is MySQL?

Because MySQL is not completely compatible with SQL, it will not work for complex applications that support large amounts of data. Instead, it is suitable for simple web applications or applications that require a simple schema design and perform data operations using simple SQL queries.

Summary

In this material we discussed several selected, most important issues in our opinion. As you can see, there are many differences. However, each new version of PostgreSQL and MySQL brings both databases closer together, and the indicated differences between them are slowly blurring. However, the numerous Postgres functionalities, full SQL compatibility, better replication, native NoSQL functions or the ability to create your own data types still make PostgreSQL „the most advanced Open Source relational database in the world ” and thus exceeds MySQL. When choosing a base, however, remember the basic principle. Practically, the amount of data in each database is systematically growing and what was supposed to be smaller in the assembly, after a few years it can be very large, and migration from MySQL to PostgreSQL is always an additional job.