Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Migrating 1200 databases from MySQL to Postgres

psyenix.blogspot.com

Post reply on HN
Print view View original

31–40 of 85 posts

Re: Migrating 1200 databases from MySQL to Postgres

#31

Post by porker »

leesalminen wrote: post #29
TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

I chose MySQL (well, MariaDB) for a project that started in 2013. I chose it because I have the most experience maintaining it and have a pretty good handle on how to tune.

Ditto. I still choose it for small to medium sized webapps because - let's face it - the data storage is not their pain point.

MySQL is fast enough, usually has enough features, I know how to tune it and queries, and I normally only bump into 2-3 things in a project which would've been easier with PostgreSQL. And can work around them.

The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me reconsider, as legacy apps needed updating.

tl;dr: yes. But PostgreSQL would be easier for some items

Re: Migrating 1200 databases from MySQL to Postgres

#32

Post by erjiang »

willcodeforfoo wrote: post #26

I'm working on migrating an app right now (just one MySQL database) but can highly recommend [pgloader]( http://pgloader.io/ ). It has a bunch of built-in and configurable rules to cast different column types to PostgreSQL and the developer has been very responsive in helping me make weird column types work, too.

Pgloader can be very powerful. Despite never having using Common Lisp, I found it easy to add in a custom column conversion functions without modifying pgloader itself. You can write additional lisp files with custom procedures and load them at runtime.

Re: Migrating 1200 databases from MySQL to Postgres

#33

Post by falcolas »

TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

Yes, I would use MySQL. I'm very familiar with it for one, and the extra storage engine options turn a sure thing into a no-brainer decision.

Ultimately, I think familiarity is and will always be the biggest driver for experienced devs who choose a DB.

Re: Migrating 1200 databases from MySQL to Postgres

#34

Post by mseebach »

porker wrote: post #31
leesalminen wrote: post #29

Earlier quoted context omitted.

I chose MySQL (well, MariaDB) for a project that started in 2013. I chose it because I have the most experience maintaining it and have a pretty good handle on how to tune.

Ditto. I still choose it for small to medium sized webapps because - let's face it - the data storage is not their pain point. MySQL is fast enough, usually has enough features, I know how to tune it and queries, and I normally only bump into 2-3 things in a project which would've been easier with PostgreSQL. And can work around them. The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me recon…

> The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me reconsider, as legacy apps needed updating.

Uhm, err, that change is to stop the database from silently trowing away your data. It's not about "fast enough", or "easy". It's about your database actually saving your data.

If you're happy for your "database" to keep quietly throwing away data in order to keep legacy apps "working", you can just disable strict mode.

Re: Migrating 1200 databases from MySQL to Postgres

#35

Post by Amezarak »

SOLAR_FIELDS wrote: post #5

One thing author did not touch on: since the tables were denormalized-ish in the original MySQL DB, did his application lose significant performance by having to perform the joins for every single query in the renormalized PostGres instance? Or were the DB's small enough and indexed properly enough so it didn't really matter? Might have been worthy of testing this to see if it was worth it to materialize certain view…

At least in my experience, joins in 100s of GB databases are not a performance problem. I've always been somewhat baffled by the claims I see pretty regularly that joins are a major performance concern. Was this a big deal in the past?

Re: Migrating 1200 databases from MySQL to Postgres

#36

Post by jeffdavis »

falcolas wrote: post #33
TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

Yes, I would use MySQL. I'm very familiar with it for one, and the extra storage engine options turn a sure thing into a no-brainer decision. Ultimately, I think familiarity is and will always be the biggest driver for experienced devs who choose a DB.

What storage engines and options do you use?

Re: Migrating 1200 databases from MySQL to Postgres

#37

Post by falcolas »

jeffdavis wrote: post #36
falcolas wrote: post #33

Earlier quoted context omitted.

Yes, I would use MySQL. I'm very familiar with it for one, and the extra storage engine options turn a sure thing into a no-brainer decision. Ultimately, I think familiarity is and will always be the biggest driver for experienced devs who choose a DB.

What storage engines and options do you use?

InnoDB is my default goto, TokuDB when on-disk size is a consideration, Archive DB for the exceptional cases where I need even more, rarely accessed, data on disk, and MyISAM for its full text searching capabilities (usually de-normalized from a backing InnoDB table). Those are the big ones.

There's also the NDB and Galara Cluster, but you want a specific use-case to justify the added complexity and overhead.

I've seen Blackhole used in production, but that was an odd one involving a... unique... replication setup. The CSV engine is pretty awesome for huge data imports, but again, pretty unusual for actual use.

Re: Migrating 1200 databases from MySQL to Postgres

#38

Post by forgot-my-pw »

jeffdavis wrote: post #36
falcolas wrote: post #33

Earlier quoted context omitted.

Yes, I would use MySQL. I'm very familiar with it for one, and the extra storage engine options turn a sure thing into a no-brainer decision. Ultimately, I think familiarity is and will always be the biggest driver for experienced devs who choose a DB.

What storage engines and options do you use?

XtraDB if you can, InnoDB if you can't.

Re: Migrating 1200 databases from MySQL to Postgres

#39

Post by e12e »

TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

Like other comments here state, in general, use what you know. That said, postgresql has ~always been the better rdbms. Some would argue mysql at times have been a better ~key-value store with ~sql interface.

Apparently postgresql is difficult to run in a properly restricted bsd jail. Other than that, I'd be hard pressed to come up with reasons to prefer mysql on technical merit over either postgresql, or, say redis or something.

Re: Migrating 1200 databases from MySQL to Postgres

#40

Post by sandGorgon »

TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

Most e-commerce systems start off on magento, which is MySQL only. So a LOT of MySQL projects are simply the answer to :

1. Did you use magento/Drupal/WordPress/etc PHP framework

2. Did you host on a 20$ per month server with unlimited bandwidth and free database ?

Post reply on HN