January 4 2023

Software performance and design choices. When the Database is the bottleneck and PostgreSQL the solution to all ills.

A brief analysis on common software problems derived from the wrong choice of tools and in particular from preferring MySQL over PostgreSQL.

PostgreSQL-vs-MySQL

If there is one thing that has remained in my mind in the staging I did in fifth grade (23 years ago) at Elettromeccanica Cognigni in Civitanova Marche, it was these few but significant words "The right tools are already half the job".

This sentence was uttered by the grandfather of a dear friend of mine, who used to come to visit the owner of the company and browse or talk about this or that, as the elderly who have time during the day to cultivate their friendships usually do. and make visits here and there.

I was engaged in emptying an armature of an electric motor (an electric winding) by beating the chisel with a remedied pliers, when I heard these high-sounding words and was subsequently invited to use a hammer.

More precise, faster and more comfortable work. In a few weeks' time, the school internship would have finished and I would have finished this experience in contact with accumulators, windings, electric motors, and the like, leaving behind many of the notions learned in a "disposable" way, however that precious advice and that solemn sentence pronounced to effect is still today one of the cornerstones of my profession, and more widely of my way of life.

We could eat the soup with a fork, but surely a spoon is better.

The problem with applications using MySQL

There is a problem that affects almost everyone, mainly involving all those situations in which you have to deal with software written by others that uses a MySQL database or related forks such as MariaDB, Percona Server, which are in any case based and derived from MySQL itself.

When working with CMSs such as WordPress, WooCommerce, Magento, Prestashop and the like, in the end we always end up dealing with an RDBMS such as MySQL or a derivative, and it seems that it is normal, right and proper to have an RDBMS that looks also extremely fast and performing.

The problem isn't a big problem when the database doesn't become the bottleneck of our application, and of the performance that inevitably affects user experience, SEO, turnover and corporate profit.

Although it may have significantly improved the speed and performance of MySQL over the years, for example by switching from the old engine MyISAM to InnoDB you always have to ask yourself if MySQL (or related forks) is the best solution on the market in terms of features, performance and speed.

MySQL is extremely slow compared to PostgreSQL

For example, if we were looking for an RDBMS compatible with SQL Standard that is open source, free, well supported and documented, multi-architecture, portable, extremely performant, we will surely exclude commercial and proprietary closed source solutions such as Oracle DB, SQL Server or DB2 from IBM.

However, we could and should consider PostgreSQL or just Postgres.

PostgreSQL is a powerful open source object-relational database system with over 35 years of active development that has earned it a solid reputation for reliability, feature robustness, and performance.

Also known as Postgres , is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally called POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL . After a revision in 2007, the development team decided to keep the PostgreSQL name and the Postgres alias. 

PostgreSQL presents transactions with Atomicity, Consistency, Isolation, Durability properties (ACID), automatically updatable views, showed materialized , triggers , foreign keys and stored procedures . It is designed to handle a wide variety of workloads, from single machines to data warehouses or web services with many concurrent users. It is the default database for macOS Server and is also available for Windows , Linux ,FreeBSD and OpenBSD .

But how slow is MySQL compared to PostgreSQL?

Or rather, how much faster is PostgreSQL compared to MySQL ?

How did I benchmark the databases?

  1. Which database has the fastest writes
  2. Which database has the fastest reads
  3. Which database has the least memory and CPU usage

Preparation

PostgreSQL

docker run --name postgres -e POSTGRES_PASSWORD=password -p 5433:5432 -v postgres_data:/var/lib/postgresql/data -d postgres:alpine

Timescale DB

docker run --name timescale -e POSTGRES_PASSWORD=password -p 5434:5432 -v timescale_data:/var/lib/postgresql/data -d timescale/timescaledb:latest-pg12

MySQL

docker run --name mysql -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 -v mysql_data:/var/lib/mysql -d mysql:latest

MariaDB

docker run --name mariadb -e MYSQL_ROOT_PASSWORD=password -p 3307:3306 -v mariadb_data:/var/lib/mysql -d mariadb:latest

Tap Server

docker run --name percona -e MYSQL_ROOT_PASSWORD=password -p 3308:3306 -v percona_data:/var/lib/mysql -d percona:ps-8

First comparison

From these results, it appears that PostgreSQL is the winner and SQL Server the loser. But we haven't done any read and/or write benchmarks yet.

Writing benchmarks.

Write SQL DBMS Benchmarks

Reading benchmarks.

Read SQL DBMS Benchmarks

The Postgres engine family is about twice as fast as the MySQL engine family.

Why is MySQL more used and popular than PostgreSQL ?

MySQL is one of the most popular database management systems in the world, although its performance is not comparable to other databases such as PostgreSQL. There are several reasons why MySQL is more popular than PostgreSQL, despite the latter's superior performance.

First of all, MySQL has a longer history than PostgreSQL and therefore had more time to spread and become popular. MySQL was first released in 1995, while PostgreSQL was released in 1996. This means that MySQL had one more year to spread and become known by programmers and industry professionals.

Additionally, MySQL is often included as a default component in many operating systems and development stacks, making it easily accessible to anyone who needs a database. Additionally, MySQL has very extensive documentation and a strong online presence, making it easy to learn and use for novice programmers.

Finally, MySQL is often chosen by companies due to its simplicity and its ability to handle large amounts of data. MySQL does not require advanced system administration knowledge to use and can easily handle large amounts of data, making it ideal for companies that need a scalable and easy-to-manage database.

Environmental impact of PostgreSQL compared to MySQL.

Using PostgreSQL instead of MySQL could have a positive impact on the environment. PostgreSQL is known for its efficiency and speed, which means it can handle large amounts of data with less resource consumption than MySQL. This can lead to a reduction in CO2 emissions, as servers will use less energy to perform the same tasks.

The use of PostgreSQL rather than MySQL could mean, for example, reducing the number of servers within an organization, avoiding the use of MySQL Clusters to compensate for performance problems, as well as avoiding a replacement or an upgrade of the hardware both for vertical scaling (increase of resources on a single machine), and for horizontal scaling, i.e. increasing nodes and machines.

Conclusions.

We have seen how, based on the benchmarks mentioned above, PostgreSQL is decidedly better and more recommended for a series of advantages both in terms of performance and speed and in the more efficient use of memory, without having to give up open source or a system of free license that allows it to be adopted without restrictions on free software and applications.

One therefore wonders why and why many realities, including the most used CMSs such as WordPress, Prestashop, Magento, Joomla, have decided to continue using a DBMS such as MySQL and derivatives which in fact does not have that caliber and those requirements such as make it palatable compared to the PostgreSQL counterpart.

Many considerations regarding the efficiency of the software as well as the impact on consumption and on the environment would come up if we consider this reasoning applied on a large scale, allowing for example to reduce the number of nodes in a cluster or to avoid in some cases horizontal or vertical scaling to address database inefficiency.

However, except for some rather immature sporadic projects to be used in production with the necessary guarantees, there still seems to be no future for PostgreSQL to be used as a backend for the most popular CMSs mentioned above.

A real shame when you consider that the degree of maturity of PostgreSQL is so high that it has virtually no rival (except perhaps Oracle DB).

 

Do you have doubts? Not sure where to start? Contact us


We have all the answers to your questions to help you make the right choice.

Chat with us

Chat directly with our presales support.

0256569681

Contact us by phone during office hours 9:30 - 19:30

Contact us online

Open a request directly in the contact area.

PRACTICAL

ManagedServer.it is the leading Italian provider of high performance hosting solutions. Our subscription model is affordable and predictable, so customers can access our reliable hosting technologies, dedicated servers and the cloud. ManagedServer.it also offers excellent support and consulting services on Hosting of the main Open Source CMS such as WordPress, WooCommerce, Drupal, Prestashop, Magento.

JUST A MOMENT !

Would you like to see how your WooCommerce runs on our systems without having to migrate anything? 

Enter the address of your WooCommerce site and you will get a navigable demonstration, without having to do absolutely anything and completely free.

No thanks, my customers prefer the slow site.
Scroll to Top