Migrate from MySQL to PostgreSQL (Rails) | Stefan's Blog

Almost all of our other projects are using PostgreSQL, so having a different SQL dialect in the mix is a bit annoying and confusing when diving into specific vendor-specific SQL features.Transactional DDL, meaning even the migrations are running in a transaction. This is a huge benefit because it means that if a migration fails, the database is rolled back to the state before the migration. Especially useful if you have more than one change in a migration, and one of them fails. With MySQL that is usually a pain, because you have to manually revert the changes that were already applied or comment them out. With PostgreSQL, we even pair our DDL migrations (e.g. adding a column) with a data migration (e.g. filling the column with data) frequently, so that we can be sure that the data is in a consistent state after the migration.We like to use specific SQL-Features, such as JSONB with indexed columns, Text-Search with TS-Vector or Reverse-Tagging-Search with TS-Query.