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? Don't know 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.

INFORMATION

Managed Server Srl is a leading Italian player in providing advanced GNU/Linux system solutions oriented towards high performance. With a low-cost and predictable subscription model, we ensure that our customers have access to advanced technologies in hosting, dedicated servers and cloud services. In addition to this, we offer systems consultancy on Linux systems and specialized maintenance in DBMS, IT Security, Cloud and much more. We stand out for our expertise in hosting leading Open Source CMS such as WordPress, WooCommerce, Drupal, Prestashop, Joomla, OpenCart and Magento, supported by a high-level support and consultancy service suitable for Public Administration, SMEs and any size.

Red Hat, Inc. owns the rights to Red Hat®, RHEL®, RedHat Linux®, and CentOS®; AlmaLinux™ is a trademark of AlmaLinux OS Foundation; Rocky Linux® is a registered trademark of the Rocky Linux Foundation; SUSE® is a registered trademark of SUSE LLC; Canonical Ltd. owns the rights to Ubuntu®; Software in the Public Interest, Inc. holds the rights to Debian®; Linus Torvalds holds the rights to Linux®; FreeBSD® is a registered trademark of The FreeBSD Foundation; NetBSD® is a registered trademark of The NetBSD Foundation; OpenBSD® is a registered trademark of Theo de Raadt. Oracle Corporation owns the rights to Oracle®, MySQL®, and MyRocks®; Percona® is a registered trademark of Percona LLC; MariaDB® is a registered trademark of MariaDB Corporation Ab; REDIS® is a registered trademark of Redis Labs Ltd. F5 Networks, Inc. owns the rights to NGINX® and NGINX Plus®; Varnish® is a registered trademark of Varnish Software AB. Adobe Inc. holds the rights to Magento®; PrestaShop® is a registered trademark of PrestaShop SA; OpenCart® is a registered trademark of OpenCart Limited. Automattic Inc. owns the rights to WordPress®, WooCommerce®, and JetPack®; Open Source Matters, Inc. owns the rights to Joomla®; Dries Buytaert holds the rights to Drupal®. Amazon Web Services, Inc. holds the rights to AWS®; Google LLC holds the rights to Google Cloud™ and Chrome™; Microsoft Corporation holds the rights to Microsoft®, Azure®, and Internet Explorer®; Mozilla Foundation owns the rights to Firefox®. Apache® is a registered trademark of The Apache Software Foundation; PHP® is a registered trademark of the PHP Group. CloudFlare® is a registered trademark of Cloudflare, Inc.; NETSCOUT® is a registered trademark of NETSCOUT Systems Inc.; ElasticSearch®, LogStash®, and Kibana® are registered trademarks of Elastic NV Hetzner Online GmbH owns the rights to Hetzner®; OVHcloud is a registered trademark of OVH Groupe SAS; cPanel®, LLC owns the rights to cPanel®; Plesk® is a registered trademark of Plesk International GmbH; Facebook, Inc. owns the rights to Facebook®. This site is not affiliated, sponsored or otherwise associated with any of the entities mentioned above and does not represent any of these entities in any way. All rights to the brands and product names mentioned are the property of their respective copyright holders. Any other trademarks mentioned belong to their registrants. MANAGED SERVER® is a trademark registered at European level by MANAGED SERVER SRL, Via Enzo Ferrari, 9, 62012 Civitanova Marche (MC), Italy.

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.
Back to top