Comparison of mysql postgresql mssql database. When not to use PostgreSql

Content Series:

1. History of the development of MySQL and PostgreSQL

The history of MySQL begins in 1979, at its origins stood small company led by Monty Widenius. In 1996, the first release of 3.11 for Solaris with a public license appeared. Then MySQL was ported to other operating systems, and a special commercial license appeared. In 2000, after adding an interface similar to Berkeley DB, the database became transactional. Replication was added around the same time. In 2001, version 4.0 added the InnoDB engine to the existing MyISAM, resulting in caching and increased performance. In 2004, version 4.1 was released, which introduced subqueries, partial indexing for MyISAM, and Unicode. In version 5.0 in 2005, stored procedures, cursors, triggers, and views appeared. MySQL is developing commercial trends: in 2009, MySQL became a trademark of Oracle.

The history of Postgres began in 1977 with the Ingress database.

In 1986, it was renamed PostgreSQL at the University of Berkeley, California.

In 1995, Postgres became an open database. Interactive psql appeared.

In 1996 Postgres95 was renamed to PostgreSQL versions 6.0.

Postgres has several hundred developers around the world.

2. Architecture of MySQL and PostgreSQL

PostgreSQL– a unified database server with a single engine – storage engine. Postgres uses a client-server model.

For each client, a new process(not a stream!). To work with such client processes, the server uses semaphores.

The client request goes through the following stages.

Connect.
Parsing: the correctness of the request is checked and a query tree is created. The parser is based on the basic Unix utilities yacc and lex.
Rewrite: a query tree is taken and the presence of rules in it is checked, which lie in system directories. Each time the user query is rewritten to a query that accesses the database tables.
Optimizer: for each request, a query plan is created, which is passed to the executor. The point of the plan is that it goes through all possible options for obtaining the result (whether to use indexes, joins, etc.), and selects the fastest option.
Query execution: the executor recursively traverses the tree and gets the result, using sorting, joins, etc., and returns rows. Postgres is an object-relational database, each table in it represents a class, and inheritance is implemented between tables. Implemented SQL92 and SQL99 standards.

The transaction model is built on the basis of the so-called multi-version concurrency control (MVCC), which gives maximum performance. Referential integrity is ensured by the presence of primary and secondary keys.

MySQL has two layers - an outer sql layer and an internal set of engines, of which the InnoDb engine is most often used, as it most fully supports ACID.

Implemented SQL92 standard.

From a modular point of view, MySQL code can be divided into the following modules.

Server initialization.
Connection manager.
Stream manager.
Command handler.
Authentication.
Parser.
Optimizer.
Table manager.
Engines (MyISAM, InnoDB, MEMORY, Berkeley DB).
Logging.
Replication.
Network API.
Kernel API.

The order of operation of the modules is as follows: first, the first module is loaded, which reads command line options, config files, allocates memory, initializes global structures, loads system tables and transfers control to the connection manager.

When a client connects to the database, control is transferred to the thread manager, which creates a thread (not a process!) for the client, and its authentication is checked.

Client requests depending on their type on upper level processed by the fourth module (dispatcher). Requests will be logged by the 11th module. The command is passed to the parser and the cache is checked. Next, the request can go to the optimizer, table module, replication module, etc. As a result, the data is returned to the client through the stream manager.

The most important code is in the sql/mysqld.cc file. It contains basic functions, which have not changed since version 3.22: init_common_variables() init_thread_environment() init_server_components() grant_init() // sql/sql_acl.cc init_slave() // sql/slave.cc get_options() handle_connections_sockets() create_new_thread() handle_one_connection() check_connection() acl_check_host() // sql/sql_acl.cc create_random_string() // sql/password.cc check_user() // sql/sql_parse.cc mysql_parse() // sql/sql_parse.cc dispatch_command() Query_cache::store_query () // sql/sql_cache.cc JOIN::optimize() // sql/sql_select.cc open_table() // sql/sql_base.cc mysql_update() // sql/sql_update.cc mysql_check_table() // sql/sql_table .cc

The sql/sql_class.h header defines the base classes: Query_arena, Statement, Security_context, Open_tables_state classes, THD. The THD class object represents a thread handle and is an argument large quantity functions.

3. Comparison of MySQL and PostgreSQL: similarities and differences

ACID standard

The ACID standard is based on atomicity, integrity, isolation and reliability. This model is used to guarantee data integrity. This is implemented on the basis of transactions. PostgreSQL is fully ACID compliant. To fully support ACID in MySQL, you need to set default-storage-engine=innodb in the config.

Performance

Databases are often optimized based on the environment in which they operate. Both bases have various technologies to improve performance. Historically, MySQL began to be developed with speed in mind, while PostgreSQL was developed from the very beginning as a database with a large number settings and compliance with the standard. PostgreSQL has a number of settings that increase access speed:

partial indexes;
data compression;
memory allocation;
improved cache.

MySQL has partial support for partial indexes in InnoDB. If you take the MySQL ISAM engine, it turns out to be faster on flat queries, while there are no blocking on inserts, no support for transactions, foreign keys.

Compression

PostgreSQL compresses and decompresses data better, allowing you to save more data on disk space. At the same time, compression data is read faster from the disk.

MySQL compression for different engines is partly supported, partly not, and this depends on the specific version of a particular engine.

In terms of multi-processor performance, PostgreSQL has an advantage over MySQL. Even the MySQL developers themselves admit that their engine is not so good in this regard.

Data types

MySQL: uses TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB types to store binary data, which differ in size (up to 4 GB).

Character: four types - TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

PostgreSQL: Supports user data engine with CREATE TYPE command, BOOLEAN type, geometric types.

Character: TEXT (limitation – max row size).

To store binary data there is a BLOB type, which is stored in file system. Table columns can be defined as multidimensional array variable length. Object-relational extension: The structure of a table can be inherited from another table.

Stored procedures

Both PostgreSQL and MySQL support stored procedures. PostgreSQL follows the Oracle PL/SQL standard, MySQL follows the IBM DB2 standard. MySQL supports extend SQL for writing functions in C/C++ since version 5.1. PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C for writing stored procedures.

Keys

Both PostgreSQL and MySQL support unique Primary Key and Foreign Key. MySQL does not support check constraint, plus secondary keys are partially implemented. PostgreSQL: full implementation plus support for ON DELETE CASCADE and ON UPDATE CASCADE.

Triggers

MySQL: rudimentary support. PostgreSQL: declarative triggers: SELECT, INSERT, DELETE, UPDATE, INSTEAD OF; procedural triggers: CONSTRAINT TRIGGER. Events: BEFORE or AFTER on INSERT, DELETE, UPDATE.

Autoincrement

MySQL: There can only be one such column in a table, which must be indexed. PostgreSQL: SERIAL data type.

Replications

Supported in both MySQL and PostgreSQL. PostgreSQL has a modular architecture, and replication is included in separate modules:

Slony-I is the main replication mechanism in Postgres; performance decreases as a quadratic function of the number of servers;

Replication in PostgreSQL is trigger-based and slower than in MySQL. It is planned to add replication to the kernel starting from version 8.4.

In MySQL, replication is included in the core and has two flavors, starting with version 5.1:

SBR – statement based replication;
RBR – row based replication.

The first type is based on logging records to a binary log, the second is based on logging changes. Starting from version 5.5, MySQL supports so-called semi-synchronous replication, in which the main server (master) resets data to another server (slave) with each commit. The NDB engine does full synchronous two-phase replication.

Transactions

MySQL: InnoDB only. Support SAVEPOINT, ROLLBACK TO SAVEPOINT. Locking levels: table level (MyISAM). PostgreSQL: supported plus read committed and isolation levels. Support ROLLBACK, ROLLBACK TO SAVEPOINT. Locking levels: row level, table level.

Privilege levels

PostgreSQL: Privileges can be assigned to a user or user group.

Export-import data

MySQL: a set of export utilities: mysqldump, mysqlhotcopy, mysqlsnapshot. Import from text files, html, dbf. PostgreSQL: export - pg_dump utility. Import between databases and file system.

Nested Queries

Available in both MySQL and PostgreSQL, but they may not work efficiently in MySQL.

Indexing

Index hashing: partial in MySQL, full in PostgreSQL. Full text search: in MySQL – partial, in PostgreSQL – full. Partial indexes: not supported in MySQL, supported in PostgreSQL. Multi-column indexes: in MySQL the limit is 16 columns, in PostgreSQL – 32. Expression indexes: in MySQL – emulation, in PostgreSQL – full. Non-blocking create index: in MySQL - partial, in PostgreSQL - full.

Partitioning

MySQL supports horizontal partitioning: range, list, hash, key, composite partitioning. PostgreSQL supports RANGE and LIST. Automatic partitioning for tables and indexes.

Automatic recovery from failures

MySQL: partial for InnoDB - you need to manually make a backup. PostgreSQL: Write Ahead Logging (WAL).

Data Storage Engines

PostgreSQL supports one engine – Postgres Storage System. There are several of them in MySQL 5.1:

MyISAM – used to store system tables;
InnoDB – maximum ACID compliance, stores data from primary keys, caches inserts, supports compression starting from version 5.1 - see attribute ROW_FORMAT=COMPRESSED;
NDB Cluster – a memory-oriented engine, cluster architecture using synchronous replication;
ARCHIVE – supports compression, does not use indexes;
and also: MERGE, MEMORY (HEAP), CSV.

InnoDB is developed by InnoBase, a subsidiary of Oracle. In version 6, two engines should appear - Maria and Falcon. Falcon is an engine based on ACID transactions.

Licensing

PostgreSQL: BSD (Berkeley Software Distribution) open source. MySQL: GPL (Gnu General Public License) or Commercial. MySQL is open-source product. Postgres is an open-source project.

Conclusion

To summarize, we can say the following: MySQL and PostgreSQL are the two most popular open-source databases in the world. Each base has its own characteristics and differences. If you need fast storage For simple queries with minimal setup, I would recommend MySQL. If you need secure storage for a large volume of data with the possibility of expansion, replication, fully compliant with modern standards SQL language, I would suggest using PostgreSQL.

We will discuss the issues MySQL settings and PostgreSQL.

Resources for download

static.content.url=http://www.site/developerworks/js/artrating/

Zone=Open source, Linux

ArticleID=779830

ArticleTitle=MySQL & PostgreSQL: Part 1. Comparative Analysis

An excellent question for a holivar. But we won't. Published by the author, the author examines both DBMSs from the position of caring for data and comes to the conclusion that PostgreSQL is suitable for serious projects, and MySQL is not an option at all.

An example of the superiority of PosgreSQL over MySQL

For example, let's create a table and set the type numeric(4, 2) for one of the fields - four digits for storing the entire number and two digits after the decimal point. And then we'll try to insert a number that doesn't match the description. And what do you think? No problem!

In manual mode, we will (possibly) see a warning (1 warning), try to guess what it is about and restore the previous value. But the warning is easy to miss. If you are writing an application that deals with storing financial data, such mistakes will be very costly.

PostgreSQL takes care of your data and behaves more seriously:

INSERT INTO DATA VALUES (1, 1234.5678); ERROR: NUMERIC FIELD overflow DETAIL: A FIELD WITH PRECISION 4 , scale 2 must round TO an absolute VALUE less than 10 ^2 .

They simply won't let us make a mistake. Moreover, the message will contain details: exactly what restriction we violated and what maximum size number given That's it.

And now a little mysticism. Remember how we described that the id field for MySQL should not be NULL? Let's try:

Surprisingly, despite the explicit prohibition (NOT NULL), the MySQL DBMS allowed updating the field, and set its value to 0, although we did not say anything about zero at all. 0 and NULL are completely different entities. 0 is a number. NULL is a special indicator that tells us that we do not know what number is contained in this field. And it doesn’t have to be 0. Thus, the DBMS again assigned a random value to our database. If your application has the same error, then MySQL will help this error remain undetected for a long time. What about PostgreSQL? Everything is clear here.

The comparison below was done in MySQL AB. I tried to be as accurate and objective as possible, however, knowing MySQL, I could not boast of the same knowledge of PostgreSQL capabilities, so I could have been wrong in some ways.

First of all, I would like to note that PostgreSQL and MySQL are widely used software products, which were developed for different purposes (although the creators of both strive to bring them to full compatibility with the ANSI SQL standard). This means that MySQL is more suitable for solving some problems, while PostgreSQL is more suitable for others. When choosing a DBMS, check whether its capabilities meet the requirements of the problem being solved. If you want to maximum speed work, it would probably be best to opt for MySQL Server. If you need additional features, available only in PostgreSQL, this DBMS is worth using.

A significant difference between MySQL and PostgreSQL is that almost all the code contained in MySQL was created by developers working at MySQL AB and constantly busy improving the server code. The exception to this rule is transaction systems and the regexp regular expression library.

Most of the PostgreSQL code is written by many developers who have no connection with each other. Not long ago, the PostgreSQL developers announced that their team finally had enough time to review all the code included in the next version of PostgreSQL.

Comparison of MySQL and PostgreSQL features

MySQL has the following advantages over PostgreSQL:

· MySQL is usually much faster than PostgreSQL. In addition, MySQL 4.0 implements a query cache. It allows you to increase the speed of request processing many times over for sites that are dominated by repeated read requests.

· In count MySQL users also far superior to PostgreSQL. Therefore, the code is tested much more meticulously and its reliability has been experimentally proven to be greater than that of PostgreSQL. MySQL is used more often than PostgreSQL in production, mainly because MySQL AB (formerly TCX DataKonsult AB) provides high-quality commercial technical MySQL support since the appearance of this system on the market, and PostgreSQL did not have any support until very recently.

· MySQL works better on Windows than PostgreSQL. MySQL Server runs as a real (native) Windows application (a service on NT/2000/XP), while PostgreSQL runs in the emulation environment, Cygwin. I have heard about the lack of stability of PostgreSQL in the Windows environment, but I still could not verify this information myself.

MySQL equipped big amount API for other languages and supported by many existing programs than PostgreSQL.

MySQL runs on highly reliable industrial systems 24/7 (on 24 hours a day, 7 days a week). In most cases, no cleanups are required in MySQL. PostgreSQL cannot yet work on such systems, since sometimes it is necessary to run VACUUM to free up the space occupied by the consequences of the UPDATE and DELETE commands and carry out the statistical analysis necessary to achieve maximum performance PostgreSQL. It is also necessary to run VACUUM after each addition of several columns to the table. On busy systems, VACUUM needs to be run more frequently, in worst cases several times a day. But while VACUUM is running (and its work can last for hours if the database is large enough), the database is practically “dead”. However, in PostgreSQL version 7.2, performing the basic functions of this program no longer leads to the database being blocked, and users can continue to work with it normally. The new VACUUM FULL command takes things more seriously: just like in older versions, it locks the table and compresses a copy of the table on disk.

· There are significantly more books about MySQL than about PostgreSQL. Books about MySQL have been published by O"Reilly, SAMS, Que and New Riders. All MySQL features are described in detail in the documentation, since it is prerequisite incorporating new features into the code.

· MySQL has a much more powerful ALTER TABLE implementation.

· MySQL provides the ability to create tables without transactions, which is necessary for applications that require the highest possible speed.

· MySQL can work with two transaction-aware table engines, namely InnoDB and BerkeleyDB. Since all transaction support systems work differently under different conditions, this gives the developer the opportunity to find best solution for the conditions in which his system will operate. See section 7 MySQL Table Types.

· The MERGE table merging command gives you the unique ability to create a view of several identical tables and work with them as one. This is especially convenient for working with logs divided, for example, by month.

· Ability to compress read-only tables without overriding direct access to their records, improves system performance by reducing the number of disk read operations. This is especially useful for archiving.

· MySQL implements full-text search.

· It is possible to work with several databases through one connection (of course, depending on the user’s privileges).

· MySQL was designed from the start to be multi-threaded, while PostgreSQL uses processes. Switching contexts and accessing shared data by multiple threads is much faster than by separate processes. This gives MySQL Server a nice performance advantage in multi-user applications, and it also allows MySQL Server to take advantage of symmetric multiprocessor (SMP) systems much more effectively.

· MySQL has a much more powerful privilege system than PostgreSQL. While PostgreSQL only provides INSERT, SELECT, and UPDATE/DELETE privileges over a database or table, MySQL provides the ability to define a full set of diverse privileges at the database, table, and column levels. Additionally, MySQL allows you to set privileges for host/user combinations.

· MySQL uses a communication protocol between client and server with data compression, which increases system performance in conditions of low-speed communication channels.

· All types of tables in MySQL (except InnoDB) are implemented as files (one table per file), which greatly simplifies the creation backup copies, moving, deleting and even creating symbolic links between databases and tables, even if the server is down.

· Updating MySQL is completely painless. When upgrading MySQL, there is no need to copy/restore data, which is necessary to do when installing most PostgreSQL updates.

Below are the advantages of PostgreSQL over MySQL today.

Conclusion

The only benchmarks available today that compare MySQL Server and PostgreSQL that anyone can download and run are the benchmarks in the MySQL suite. And that’s why I choose MySQL

Blog of the Mail.ru Group company

In anticipation of my report at the PGCONF.RUSSIA 2015 conference, I will share some observations about the important differences between the MySQL and PostgreSQL DBMS. This material will be useful to all those who are no longer satisfied with the capabilities and features of MySQL, as well as those who are taking their first steps in Postgres. Of course, this post should not be considered as an exhaustive list of differences, but it will be quite sufficient to make a decision in favor of one or another DBMS.

Replication

The topic of my report is “Asynchronous replication without censorship, or why PostgreSQL will conquer the world,” and replication is one of the most painful topics for busy projects using MySQL. There are many problems - correct operation, stability, performance - and at first glance they look unrelated. If we look at the historical context, we get interesting conclusion: MySQL replication has so many problems because it was not thought out, and the point of no return was support for the storage engine (plug-in engines) without answers to the questions “what to do with the log?” and “how different storage engines can participate in replication.” In 2004, in the PostgreSQL mailing list, a user tried to “find” the storage engine in source code PostgreSQL and was very surprised that they were not there. During the discussion, someone suggested adding this feature to PostgreSQL, and one of the developers replied, “Guys, if we do this, we will have problems with replication and transactions between engines.”

The problem is that many storage management systems… often do their own WAL and PITR. Some do their own buffer management, locking and replication/load management too. So, as you say, its hard to say where an interface should be
abstracted.

link to this letter in postgresql mailing list

More than 10 years have passed, and what do we see? MySQL has annoying problems with transactions between tables in different storage engines and MySQL has problems with replication. Over these ten years, PostgreSQL has added plug-in data types and indexes, and also has replication - that is, the advantage of MySQL has been leveled, while the architectural problems of MySQL remain and interfere with life. MySQL 5.7 tried to solve the replication performance problem by parallelizing it. Since the project at work is very sensitive to replication performance due to its scale, I tried to test if it got any better. I found that parallel replication in 5.7 is slower than single-threaded replication in 5.5, and only in some cases - about the same. If you are currently using MySQL 5.5 and want to upgrade to a more recent version, please note that migration is not possible for highly loaded projects, since replication will simply no longer keep up.

After the highload talk, Oracle took note of the test I developed and said they would try to fix the problem; recently they even wrote to me that they were able to see parallelism in their tests, and sent me the settings. If I'm not mistaken, with 16 threads there was a slight acceleration compared to the single-threaded version. Unfortunately, I have not yet repeated my tests on the provided settings - in particular because with such results our problems still remain relevant.

The exact reasons for this performance regression are unknown. There were several assumptions - for example, Christian Nelsen, one of the MariaDB developers, wrote on his blog that there may be problems with the performance scheme and thread synchronization. Because of this, there is a regression of 40%, which is visible in regular tests. Oracle developers refute this, and they even convinced me that it doesn’t exist; apparently, I see some other problem (and how many of them are there?).

In MySQL replication, problems with the storage engine are aggravated by the selected replication level - they are logical, while in PostgreSQL they are physical. In principle, logical replication has its advantages; it allows you to do more interesting things, I will also mention this in the report. But PostgreSQL, even within the framework of its physical replication, already reduces all these advantages to nothing. In other words, almost everything that is in MySQL can already be done in PostgreSQL (or will be possible in the near future).

You can't hope to implement low-level physical replication in MySQL. The problem is that instead of one log (as in PostgreSQL), there are two or four of them, depending on how you count them. PostgreSQL simply commits queries, they go to a log, and this log is used in replication. PostgreSQL replication is super stable because it uses the same log as failover operations. This mechanism has been written for a long time, well tested and optimized.

In MySQL the situation is different. We have a separate InnoDB log and a replication log, and we need to commit both. And this is a two-phase commit between journals, which by definition is slow. That is, we cannot simply say that we are repeating a transaction from the InnoDB log - we have to figure out what kind of request it is and run it again. Even if this is logical replication, at the line level, then these lines need to be looked for in the index. And not only do you have to do a lot of work to execute the query, but it will again be written to its InnoDB log on the replica, which is clearly not good for performance.

In PostgreSQL, in this sense, the architecture is much more thoughtful and better implemented. It recently announced a feature called Logical Decoding - which allows you to do all sorts of interesting things, which are very difficult to do within the confines of a physical journal. In PostgreSQL, this is an add-on on top, logical decoding allows you to work with a physical log as if it were a logical one. It is this functionality that will soon remove all the advantages of MySQL replication, except perhaps the log size - statement-based MySQL replication will win - but statement-based MySQL replication has completely wild problems in the most unexpected places, and should not be considered good decision(I will also talk about all this in the report).

In addition, there is trigger replication for PostgreSQL - this is Tungsten, which allows you to do the same thing. Trigger replication works as follows: triggers are set, they fill tables or write files, the result is sent to the replica and applied there. It is through Tungsten, as far as I know, that they migrate from MySQL to PostgreSQL and vice versa. In MySQL, logical replication works directly at the engine level, and it is no longer possible to do it any other way.

Documentation

PostgreSQL has much better documentation. In MySQL, formally it even seems to exist, but the point is individual options can be difficult to understand. It seems to be written what they do, but to understand how to configure them correctly, you need to use unofficial documentation and look for articles on this topic. Often you need to understand the MySQL architecture; without this understanding, the settings look like some kind of magic.

For example, the Percona company took off: they ran the MySQL Performance Blog, and in this blog there were many articles that discussed certain aspects of using MySQL. This brought wild popularity, brought clients into consulting, and allowed us to attract resources to launch the development of our own Percona-Server fork. The existence and popularity of the MySQL Performance Blog proves that official documentation is simply not enough.

PostgreSQL actually has all the answers in its documentation. On the other hand, I have heard a lot of criticism when comparing the PostgreSQL documentation with the “grown-up” Oracle. But this is actually very important indicator. No one is trying to compare MySQL with the adult Oracle at all - that would be funny and absurd - but PostgreSQL is already starting to be compared quite seriously, the PostgreSQL community hears this criticism and is working to improve the product. This suggests that in terms of its capabilities and performance it is beginning to compete with such powerful system like the Oracle they run on mobile operators and banks, while MySQL remains in the website niche. And giant projects that have grown to a large amount of data and users slurp up MySQL with a big spoon, constantly running into its limitations and architectural problems that cannot be corrected by spending a reasonable amount of effort and time.

An example of such large projects on PostgreSQL is 1C: PostgreSQL is available as an option instead of Microsoft SQL, and Microsoft SQL is truly a fantastic DBMS, one of the most powerful. PostgreSQL can replace MS SQL, and trying to replace it with MySQL... let's lower the curtain of pity over this scene, as Mark Twain wrote.

Standards

PostgreSQL complies with SQL-92, SQL-98, SQL-2003 (all reasonable parts of it are implemented) and is already working on SQL-2011. This is very cool. In comparison, MySQL doesn't even support SQL-92. Some will say that in MySQL such a goal was simply not set by the developers. But you need to understand that the difference between versions of the standard is not minor changes - these are new functionality. That is, at the moment when MySQL said: “We will not follow the standard,” they were not only introducing some minor differences that made MySQL difficult to support, they were also closing the door to the implementation of many necessary and important features. There is still no proper optimizer. What is called optimization there is called “parser” plus normalization in PostgreSQL. In MySQL, this is just a query execution plan, without separation. And MySQL will not come to support standards very soon, since there is a burden pressing on them backward compatibility. Yes, they want it, but in five years, maybe they will have something. PostgreSQL already has everything now.

Performance and administrative complexity

From point of view you just administration comparison is not in favor of PostgreSQL. MySQL is much easier to administer. And not because in this sense it is better thought out, but simply knows how to do much less. Accordingly, it is easier to configure it.

MySQL has a problem with complex queries. For example, MySQL does not know how to lower a group into separate parts of union all. The difference between the two queries - in our example, grouping by individual tables and union all on top worked 15 times faster than union all and then grouping, although the optimizer must bring both queries into the same, efficient query execution plan. We will have to generate such requests manually - that is, waste developers' time on what the database should do.

The “simplicity” of MySQL results, as can be seen above, from extremely poor capabilities - MySQL simply works worse and requires more time and effort during development. In contrast, PostrgreSQL has histograms and a normal optimizer, and will execute such queries efficiently. But if there are histograms, then there are their settings - at least bucket size. You need to know about the settings and change them in some cases - therefore, you need to understand what this setting is, what it is responsible for, be able to recognize such situations, and see how to choose the optimal parameters.

Occasionally it happens that PostrgreSQL's skill can hinder rather than help. 95% of the time everything works fine - better than MySQL - but one stupid query runs much slower. Or everything works well, and then suddenly (from the user’s point of view) as the project grows, some queries began to work poorly (there was more data, a different query execution plan began to be selected). Most likely, to fix it, just run analyze or tweak the settings a little. But you need to know what to do and how to do it. At a minimum, you need to read the PostgreSQL documentation on this topic, but for some reason they don’t like to read documentation. Maybe because it is of little help in MySQL? :)

I would like to emphasize that PostgreSQL is no worse in this sense, it just allows you to postpone problems, while MySQL immediately throws them out and you have to spend time and money solving them. In this sense, MySQL always works consistently poorly, and even at the development stage people take these features into account: they do everything in the simplest possible way. This applies only to productivity, more precisely, to the methods of achieving it and to its predictability. In terms of correctness and convenience, PostgreSQL is head and shoulders above MySQL.

So what should you choose?

To decide between MySQL and PostgreSQL for a specific project, you first need to answer other questions.

First, what experience does the team have? If the entire team has 10 years of experience working with MySQL and needs to get up and running as quickly as possible, then it’s not a fact that it’s worth changing a familiar tool to an unfamiliar one. But if deadlines are not critical, then PostgreSQL is worth trying.

Secondly, we must not forget about operational problems. If you do not have a highly loaded project, then from a performance point of view there is no difference between these two DBMSs. But PostgreSQL has another important advantage: it is more strict, does more checks for you, gives you less opportunity to make mistakes, and this is a huge advantage in the long term. For example, in MySQL you have to write your own tools to verify the regular referential integrity of the database. And even with this there can be problems. In this sense, PostgreSQL is a more powerful, more flexible tool, and it’s more pleasant to develop on it. But this largely depends on the experience of the developer.

To summarize: if you have a simple online store, no money for an admin, no serious ambitions to grow into big project and have experience with MySQL, then take MySQL. If you expect that the project will be popular, if it is large, it will be difficult to rewrite it, if it has complex logic and relationships between tables - take PostgreSQL. Even out of the box it will work for you, will help you in development, will save time, and will make it easier for you to grow.