Ask HN: Have You Ever Migrated from MySQL to PostgresQL or Otherwise? | Hacker News Reader

Ask HN: Have You Ever Migrated from MySQL to PostgresQL or Otherwise?

4 pointsleejo3 comments

I hear this suggested in talks / on the web frequently enough to wonder if any company has ever successfully migrated from mySQL to PostgresQL, or from database technology foo to database technology bar, without coming close to killing the company or blocking the development team from doing more profitable work? Or is this something that many people suggest but have never actually done?

Just move to a "real" database and all your problems will be solved, right? I don't think so - you will just replace one set of problems with another. And when you're dealing with a schema that is multiples of TB, hundreds of tables, stored procs, triggers, poorly normalised, contains a mass of undocumented domain knowledge, goes back over a decade, then it's not going to happen.

If it does happen then it's probably over a period of years, or effectively a rewrite. Databases are like mortgages, they're technical debt that take decades to pay off.

So, have you ever done the migration? How did it go?

Just a quick interjection: I wrote a library that migrates between any 2 relational databases. In my case, I migrated off of Oracle and onto MySQL, but the tool supports any RDBMS supported by SQLAlchemy.

https://github.com/seanharr11/etlalchemy

The best way to know is to try! It takes 4 lines of python to get you migrated.

PostgreSQL is a bit more feature rich in my findings, but for the majority of web development there really aren't huge differences. In my experience, MySQL performs a bit better for lots of simple READ queries, while PostgreSQL can handle larger, more complex queries and very frequent WRITEs.

I migrated from a custom database to postgresql. I have to say I am much happier in terms of being able to generate complex reports.

The types and jsonb are also very nice.

I have used mysql early in my career, and I still use it for certain existing projects. I liked the simplicity of its command line, but postgresql has caught up, and they continue to add amazing features.

I'm trying to give some consult to a company who is looking to store JSON data in MySQL, and they have a Rails app.

Given a Rails web app (i.e. ActiveRecord models), what is the best choice if the JSON column type is essential?

Initial research shows that PostgreSQL stores JSONB more efficiently, and provides faster query performance with indexes, while MySQL exposes and JSON query interface w/o much optimization. Thoughts?

Reply on news.ycombinator.com