We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons.
Has anyone else done such a move? Our database is the lifeblood of the application and will eventually be storing TBs of data, so I'm keen to hear about experiences of performance improvements/losses, major hurdles in converting SQL and stored procedures, etc.
Edit: Just to clarify to those who have asked why we don't like MySQL's licensing. We are developing a commercial product which (currently) depends on MySQL as a database back-end. Their license states we need to pay them a percentage of our list price per installation, and not a flat fee. As a startup, this is less than appealing.
Under the hood, MySQL FDW (mysql_fdw) facilitates the use of PostgreSQL server as a client for MySQL Server, which means it can then fetch data from the MySQL database as a client.
PostgreSQL is still catching up to MySQL's popularity, which means that fewer developers are working with it and fewer third-party applications are available. However, with the increasing need for highly concurrent services that are always available, Postgres might soon overtake MySQL as the most popular database tool.
PostgreSQL supports more advanced queries, it performs better on complicated queries, but is harder to manage. MySQL is fast, easy to manage, but you can run into it's limitations on advanced queries, stored procedures and the like.
Steve, I had to migrate my old application the way around, that is PgSQL->MySQL. I must say, you should consider yourself lucky ;-) Common gotchas are:
I hope that helps at least a bit. Have lots of fun playing with Postgres!
I have done a similar conversion, but for different reasons. It was because we needed better ACID support, and the ability to have web users see the same data they could via other DB tools (one ID for both).
Here are the things that bit us:
That said, once it was in place and tested, it was much nicer. With correct locking for safety reasons and heavy concurrent use, PostgreSQL performed better than MySQL. On the things where locking was not needed (read only) the performance was not quite as good, but it was still faster than the network card, so it was not an issue.
Tips:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With