Whenever we start developing a new web application, the question arises of how to work with the data. One of the most widespread solutions is relational systems. Systems like MySQL or PostgreSQL are widespread and have as many supporters as detractors.
If you search the internet for the differences between MySQL and PostgreSQL, almost all comparisons mention that MySQL has better performance and PostgreSQL offers a much wider range of functions.
It is true that MySQL has gained functionality over time, just as PostgreSQL has gained performance. The latter is also partly due to the advances and improvements in hardware.
At WATA Factory, we asked ourselves how important these differences are in practice, without getting into discussions about how many microseconds of difference thereare between one or the other, or about the number of advanced features we end up never using in our everyday lives.
MySQL: Main differences
With MySQL we can choose different database engines for each table: InnoDB and MyISAM. The latter is the one that has accompanied MySQL since its beginnings and is responsible for its reputation for being “fast”.
The speed of MyISAM is largely since it has no referential integrity constraints, triggers or other functions. While these functions are more than useful, they can slow down input operations or updates because they must be checked for each operation. InnoDB, on the other hand, has such functions and would therefore not be as fast in input operations or updates.
Another aspect is the question of blocking. Since in MyISAM the data is changed by INSERT, UPDATE or DELETE commands, blocking occurs at the table level. In InnoDB or PostgreSQL, blocking occurs at the tuple level. Therefore, if the application performs large INSERT, UPDATE or DELETE operations that affect a significant number of tuples in the table, MyISAM is much more efficient. On the other hand, if we have many small concurrent operations, InnoDB works better by blocking only the affected tuple, improving concurrency.
One might think that a DB engine that we call relational and that does not provide foreign keys is of little use and also poses a risk to data integrity. However, let’s take the case of a typical business intelligence solution where we have a star model with a central table with few relationships populated by massive processes. It is possible that a DB engine without foreign keys and with table locking is an advantage that should be considered. It should be noted that we can not only combine the two DB engines in the same schema, but also work with other transactional tables in the same schema.
One possibility, that has been suggested by some, is to use a MySQL with InnoDB for development and then move the tables to MyISAM once they are in production. Of course, this requires you to be very clear about what you are doing and to control the whole process very carefully. The idea is to control the integrity of the data during development, but to speed up the queries once they are in production.
In short, if we are going to use MySQL in our development, we should consider whether we are really going to take advantage of MyISAM. If not, it may be better to opt for other engines.
PostgreSQL and its functions
Although MySQL has integrated and continues to integrate more and more functionalities, it is true that PostgreSQL has stood out from the beginning as a more complete and free relational database management system. For many, it is considered the oracle of free software.
Among its functionalities, PL/pgSQL stands out, which is very similar to Oracle’s PL/SQL By using this language, we can introduce the complexity typical of procedural languages into our SQL: Loops, conditionals, functions…. The triggers, to give an example, can become very complex.
At the SQL level, PostgreSQL also contains window functions that enable complex SQL queries, which are particularly useful in statistical applications. Similarly, with the Rules System, we can fine-tune the execution of queries and data manipulation commands by changing the way the command is processed by the database engine.
There are other noteworthy aspects such as the link between tables and materialised views. However, a detailed presentation would require many more articles.
Therefore, with PostgreSQL we have a wider range of possibilities when developing a new application, so we are less restricted than when using MySQL, which is very interesting for projects where evolutionary maintenance is foreseen in the long or medium term.
MySQL vs. PostgreSQL at WATA Factory
At WATA Factory, we evaluate which database engine to use for each individual project, depending on factors such as whether it is a long-term project, the type of application or the number of concurrent users, to name only a few.
For practical purposes, when there are no specific performance requirements, as is the case for most of the applications we are developing, we prefer PostgreSQL, which offers very interesting functionalities that could be useful for the future without making much difference for practical purposes.
However, we have also been involved in the development of projects based on CMS such as Drupal, WordPress or Joomla.
In these cases, it is of course always best to use the system recommended by the community developing these CMSs, which is usually MySQL.
This is because this type of development is usually subject to special requirements in terms of positioning or SEO, so a MySQL with MyISAM can be a good alternative.
In the case of Drupal, although the documentation states that it supports MySQL and PostgreSQL, there are times when installing third-party modules, they are mainly developed and tested in MySQL. Depending on the Drupal version and the modules we use on our website, the choice that will probably cause us less headaches is MySQL.
If we develop the third-party software, it is quite possible that we will use an engine like MySQL, which is easily customisable and extensible. On the other hand, if we later decide that our product should support both database systems, it would be easier to start from MySQL and adapt it to PostgreSQL than vice versa.