Considering move to PostgreSQL, feedback requested...

Question

We are considering using PostgreSQL 8.4 on RHEL for some upcoming projects.

We've been heavy users of MySQL since 3.23 (now on 5.1). The database servers are behind a load balanced cluster of web/app servers.

Our usage is mostly business related web apps (user accounts with lots of emails / contacts / stats / projects / plans / tasks / permissions / relations / whatever per user).

Some of our MySQL databases range up into 200+ tables, 150,000,000+ records and growing.

For those of you who have experience with PostgreSQL (and ideally MySQL):

1. What are the main considerations we should be taking into account?
2. Are there any major pitfalls that you have run across?
3. What good reasons are there to move forward?
4. What good reasons are there to stay away?

---

Here are some of the reasons that we are interested in PostgreSQL:

• Broader support for transactions, triggers, stored procedures, constraints, sql, subqueries, datatypes, and more...
• Ability to use Python as the stored procedure language
• Multiple schema's per database
• DDL inside transactions
• Hot backup
• More open licencing

Here are some of the reasons we are interested in leaving MySQL:

• Botched 5.1 release (see here)
• ~ 14 releases into 5.1 GA, and still fixing bugs similar to "Multiple-table updates for InnoDB tables could produce unexpected results. (Bug#43580)"
• Major backward compatibility breakage in 5.1.20 series, and fixed in 5.1.30 series.
• Bought by Sun
• Bought by Oracle
• Gap between open source and commercial version growing wider
• Multiple forks (including Drizzle), while really cool, indicate unrest with the core product

Answer 1

Personally I've used both databases and feel they are both solid. I understand your concern about 5.1, we currently run 5.0 and are pretty happy with it. MySQL is definitely going through some craziness right now, and I don't see Oracle as a great place for the core of MySQL to be. That said, the version we are running does the job we need it to do and I get great community support for it. There are tons of community folks who use and love MySQL and I just don't see it getting buried in some Oracle closet. In my eyes the MySQL forks are a good sign that there is a strong community element to MySQL development.

A few years ago when I migrated a system from MySQL to Postgres we had some serious issues with performance which took a considerable amount of tuning to resolve, ultimately we were never able to get it to perform as well as MySQL but we were able to get it to meet our needs. Keep in mind this was migrating from MyISAM tables with no ACID or logging to a fully ACID/ logged database, it was an unfair challenge, but many people are running similar setups. I also think PostgreSQL has put a lot of effort into performance in the years since so it's likely less an issue.

Another issue we had is the differences between SQL implementations. Plan on investing a significant amount of time and effort rewriting your applications unless you use an abstraction layer on your database.

Ultimately, why did you choose MySQL to begin with are those reasons still valid? I see you list the advantages of PostgreSQL but are these things you really are going to use or are they just a rundown of features? I don't really see them as advantages because for our purposes they just don't apply, in your case maybe they are critical issues.

Answer 2

As far as your reasons for leaving MySQL, can you explain what you mean by:

• Major backward compatibility breakage in 5.2.x series, and fixed in 5.3.x series.
• Gap between open source and commercial version growing wider

These seem pretty much untrue (particularly since 5.2 and 5.3 weren't actual MySQL releases) and the community and open source version are pretty much identical.

As far as migrating, one of the biggest issues people hit is with replication. MySQL replication does have some pros and cons, but generally PostgreSQL replication (via Slony) is much harder to setup and manage.

In addition, migration of any existing codebase between any database engines is always very tricky. The problem is that to maximize performance you will need to use many database specific features (whether Oracle, MySQL, PostgreSQL, etc...)

Ultimately it depends on if you think the time is worth the effort. Does the advantages of migrating override the disadvantages?

From your list provided, a lot of the advantages you listed seem pretty nebulous. For example, has the 5.1 release really been botched for you? Have you seen lots of crashes/corruption? As far as bugs, have you hit any of them? I would form a more concrete pros and cons list and see what you would gain by migrating.