The Reason

The main reason we wanted to change was that Postgres had better support for handling geographical data, such as coordinates, polygons, and distance calculations. Because of our CI/CD pipeline, some parts of the code had already been released to production, and a lot of data (more than three million addresses) had been created. That's why we couldn't just change the database engine and continue the work on the features.

The Problems

The first issue was with Postgres, which uses strict types. It means the BOOLEAN is a boolean, not a TINYINT. So during the data migration, we faced a lot of incompatible data types that we had to fix. In Postgres, we have no autoincrement ID, we need to use sequences. Which is about the same. We can use more sequences in one table or the same sequences in several tables. This can be a huge benefit.

The other side effect of being strict is that trying to modify tables that have view defined for them cannot automatically be done. Postgres will give a "cannot alter the type of a column used by a view or rule" error, while MySQL will automatically modify the dependent view. The solution was to drop the view before the change and re-add it after.

The second issue we faced was that in many tables, we use enums to limit the number of choices a value can take. In MySQL, there is no enum type. It employs constraints to limit the values. When using the same enum in more than one place and we have to change the enum values (add or remove values) during development, we will also have to touch every table with the constraint. In Postgres, we can create our own ENUM types such as CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'), and after that, we can use them in every place where we need them. If we need to expand them, we can do it in one place only.

The third issue was not related to Postgres itself but to Doctrine. As we mentioned earlier, Postgres uses strict types. However, with the default Doctrine settings, we cannot use the same queries without converting the parameters to the right type. For example, if we want to make a search query with LIKE, we cannot search for a string in an int column. We need to cast it manually. There are several packages available that we can use to solve this. During the process, we used the following one: https://github.com/opsway/doctrine-dbal-postgresql

The last issue we would like to touch on is collation. On many pages, we have search functionality, and of course, we use LIKE in the query to be able to search. In Postgres, however, special characters are not normalized. Let's see an example. If we search for an address on Rue d'Amérique and our search term is Amerique, Postgres can't find it. In MySQL, it wasn't a problem at all. The engine could do normalization out of the box. To solve this issue, we had to use the officially bundled unaccent extension and the UNACCENT function in the queries.

The Conclusion

Fortunately, Postgres is an excellent tool. The issues we faced weren't huge problems. However, we may experience different behaviours compared to MySQL if we have no or only limited experience with Postgres. It is also true when we use raw queries instead of an ORM. If we compare the two, Postgres has much more flexibility and because of the strict types is a little bit safer than MySQL.