How to make a proper migration from MySQL to PostgreSQL
Migrations are always horrible. Doesn't matter from which system to which other system. About what you should take care when you think about migration by the example of migration from MySQL to PostgreSQL.
Why do you want to migrate?
There are different reasons why a user wants to migrate. Mostly for migrations from MySQL to PostgreSQL it is one of the following:
- performance reasons
- stableness
- reliability
- GIS
- Replication issues
- Cluster issues
- High availability issues
- localisation/globalisation issues
- need of object relational features
- sick of bugs
- costs for license (dual license, GPL)
- license (BSD instead of GPL)
Check profitability
First of all you should check if a migration really makes sense.
Just imagine you were a developer of live online games. You developed the game three or five years ago by using MySQL. All worked fine at the beginning but today you have more then a million users and 4000 queries per second. You already optimised all what you can optimise at and for MySQL but you still often have problems with the database server.
Consider, PostgreSQL isn't able to handle this huge load out of the box too. You will have other problems then with MySQL but you will have problems too. A huge effort on optimisation is necessary for PostgreSQL here too. This would mean you have to optimise your database for PostgreSQL and you have to optimise your full source code for PostgreSQL. This would cost you half of a year work until you will have an advantage by using PostgreSQL.
Now consider the life time of your game. Usually such kinds of games have a life time from just a few years. Your game already reached the highest point in its life. It will be one of the top live online games maybe this year and maybe next year but not longer. So it just makes no sense to make such huge changes here.
It will be better to let the old game on the old system and start the new project (a new game) directly by using PostgreSQL.
How long need a migration?
Just dumping the database and change the different SQL stuff from the system to PostgreSQL related SQL and then import all into PostgreSQL often isn't good enough. You will have the same problems as before or different problems which are not better than the old problems. When you wanted to migrate for performance reasons it also could happen that PostgreSQL will have an inferior performance than MySQL has had.
This means you have to change lots of other stuff. I have never seen a migration that was faster than 3 months. Usually, you will need 3 months up to half a year until all will work proper and you will be lucky with the new system.
Use the advantages of the system
Usually, when you want to migrate then you want to use the advantages of the system. PostgreSQL has different advantages than MySQL and you have to do different stuff when you want to get a benefit of the advantages. Let me show you some examples:
At MySQL you often have to use a bad db design to avoid joins and subqueries (because of bad performance here).Indexing is just implemented rudimentary. You need different storage engines for full text search and transaction handling. Trigger handling especially before trigger is ugly and buggy. Procedures and functions only possible in SQL. Not Null behaviour isn't familiar. Non DDL/DCL transactions. Non check constraints. Often you will find usage of the advantages of known MySQL bugs.
PostgreSQL also has an excellent performance by using a good database design. Joins and subqueries work fast. You can choose between several index algorithm and indexing works as expected. You also have a good performance on DDL indexing stuff. You only have one storage engine. You won't have problems with triggers, procedures/functions. Also you can write procedures/functions in almost every language. Not Null behaviour is familiar. You will have DDL/DCL transactions and check constraints and there are non known bugs at the moment for PostgreSQL. You also can create rules when you need them as advantage.
You will find two different philosophies outside:
Often from MySQL users you will hear: Put most of database logic into the application.
Often from PostgreSQL users you will hear: Put database logic in the database.
My own opinion is: Put database logic in the database, it doesn't matter which system. Unfortunately, with MySQL sometimes you just have to put it into the application because of missing feature or other disadvantages.
You have lots of features at PostgreSQL where for MySQL not even has source code. So when you want to have an advantage of a migration to PostgreSQL you should use this features as well.
MySQL hierarchy: Database/Schema -> Table -> Column
PostgreSQL hierarchy: Database -> Schema -> Table -> Column
When you will ask MySQL here you often will get the answer: our databases are not real databases. What we call databases is the same as schema at PostgreSQL. What PostgreSQL is calling database is called an instance in MySQL.
When you ask me or some other PostgreSQLer here you will get the answer: they just have one less hierarchy level. Database is database and table is table they just don't have the schema level.
Anyway, when you want to migrate of course you can use these 4 hierarchy levels too and not only three.
Because MySQL has so many bugs you often find workarounds for bugs in application code. Of course this has to be repaired when you want to have an advantage on PostgreSQL.
Data types is the next topic. Neither timestamp nor datetime from MySQL work in the same way as timestamp in PostgreSQL. Also text/varchar/char is different. Looking to performance just consider, that you can't use indexes on data type text in MySQL. You can use them in PostgreSQL. Also you won't have different performance for text, varchar and char.
Summary
To make a proper migration:
- Think about the database, the design. Think about it independent from what already exist. Re-design the database and use the advantages from PostgreSQL.
- Re-design the application/software
Lots of work ...
But this is the best way for migration from MySQL to PostgreSQL. Often migration isn't profitable and it is better to stay on MySQL and just design the new/upcoming project with PostgreSQL.
If profitability says a migration makes sense and you really do all this work then you will be happy and won't have further problems in nearer future.
