Bitrix delivers its solutions with MySQL by default. This DBMS was chosen as the main one for Bitrix24 and 1C-Bitrix:Site Management for reasons relat to the history of development, performance and requirements for mass implementation. In the 2010s, when Bitrix was creat, MySQL was support by all hosting providers, was easier to administer, requir fewer resources with a higher spe of processing simple queries. At that time, Postgre’s capabilities seem excessive against the background of high requirements for the quality of administration.
But the world is changing. More and more projects have special requirements for fault tolerance, scalability, processing spe of large and unstructur data, and support for cluster architecture. In this regard, the PostgreSQL DBMS has a number of advantages, especially in complex and highly load B2B and corporate solutions.
For whom is PostgreSQL relevant?
First of all, for mium and large companies that:
- manage load systems with large volumes of structur and unstructur data, with a prominance of write operations and complex queries;
- want to achieve maximum performance, but are unwilling to compromise on security;
- want to have a reliable alternative to foreign DBMS and Russian support;
- must work on FSTEC-certifi software;
- fac with the fact that MySQL cannot cope with the load, or are forc to put up with its limitations;
- are planning active growth/scaling and want to prepare the infrastructure in advance.
Comparison of MySQL and PostgreSQL in Bitrix projects
Both DBMS are suitable for working with Bitrix24 and 1C-Bitrix:Site Management, despite the fact that in some cases the data management mechanism is implement differently in them.
Possible difficulties and limitations
Bitrix24 is an OLTP system (online transactions, afghanistan email list chats, tasks), and MySQL is often better suit for its operation – the performance is higher. But if the move is inevitable, then we suggest paying attention to a number of features that are important to consider when preparing for migration:
PostgreSQL is not just a DBMS with better scaling and performance. The philosophy itself is different, and this is important to consider before making a decision on implementation or migration, especially if we are talking about a “live” project with history. Postgre is built taking into account the principle of “explicit is better than implicit”, so the database will not “think up” for the user and forgive errors in the code. MySQL, on the contrary, is design for a low entry threshold. Given this, it is worthwhile to evaluate in advance the gain in performance/reliability compar to the costs of migration and subsequent support.
Single quotes instead of double quotes, case sensitivity, concatenation rules, operators in regular expressions – the differences in syntax, although small, will take time to it the code.
PostgreSQL can work with user-defin data types, but is stricter about them, often requiring explicit type conversion. For some data, the type can be chang using regular expressions, for example, TINYINT→SMALLINT, FLOAT→REAL, DOUBLE→DOUBLE PRECISION, etc. However, a number of types can cause problems during migration. For example, AUTO_INCREMENT cannot be notic on SEQUENCE, for each ENUM you ne to china numbers create your own CUSTOM TYPE ENUM. There are some peculiarities in working with Boolean types and binaries.
A number of MySQL system functions also work in PostgreSQL
But during migration it will almost always be necessary to rewrite user functions and update the syntax of system functions. For example, functions for working with JSON, window functions, specific string functions, dates and times, and trigger definitions will definitely require changes. A number of MySQL functions have no analogues in PostgreSQL and vice versa. The MySQL IF function has no analogues in PostgreSQL, so some functions will have to be rewritten to NULLIF, and some to CASE WHEN. Everything will have to be done manually, since you can’t rewrite this with regular expressions.
Limitations and features in queries. MySQL forgives errors
But in Postgres you can’t do HAVING without GROUP, you can’t compare integer with an empty string, SELECT DISTINCT by JSON field won’t work without converting the field to a text type, WHERE must have a condition. And there are a lot of such nuances.
Grouping and sorting. In Postgres, analytics in bitrix24: how to get the most out of unstructured data everything is much more serious in this regard. For example, you cannot select fields that have not been group or aggregat. That is, you either ne to exclude these fields, or do an aggregation, or add them to the group. But then the query structure will change. And on a live project, this can be painful.
