PostgreSQL 9.0 is Serious Competition

A new version (9.0) of PostgreSQL is in beta and ready for public testing. If version 9.0 lives up to its proposed feature list, then it is serious competition for MySQL. PostgreSQL proponents have long suggested that PostgreSQL has always enjoyed superiority over MySQL. I beg to differ but that may be changing.

I tried using PostgreSQL several times over the past few years, beginning with version 6.x. I received it on CD for review. It was OK. Nothing thrilling or special. I did think it was an ambitious project but at the time, I didn't really care much for it.
I thought it was clunky, bloated and too complicated.

I tried again with a 7.x version. Same results.

Version 8.0 arrived with a bit of fanfare and I really tried this time. Not that I hadn't tried before but this time, I dedicated a whole Linux system to it. I was convinced that I was going to use PostgreSQL this time. I wanted to use it. For me, it was either Oracle or PostgreSQL and I couldn't afford Oracle. Still can't.

I gave PostgreSQL a good workout through attempting a setup of a web database application using Perl and PHP for the front end and automated back end. Again, PostgreSQL was too complex for me to use and therefore far too complex for many of my clients. I never felt comfortable enough with it to maintain my relationship with it.

I found PostgreSQL to be slow and many of the basic features that I needed and wanted weren't there.

However, (And I might be a glutton for punishment or not know when to give up on something, but...) I'm excited about PostgreSQL 9.0 for two of its features that didn't exist in the versions I tried: Column-Level Permissions and Default Object Permissions.
These two features merely scratch the surface of what's going on with 9.0. You'll have to have a look at the Feature Matrix to see most of what's included in the new release.

In version 9.0, a large number of new features will allow developers and DBAs to broaden their use of PostgreSQL, including:

New binary replication.
64-bit support on Windows.
Improved LISTEN/NOTIFY allows fast internal database event messaging.
Anonymous procedure blocks with the DO statement.
Conditional and SQL-compliant per-column triggers.
Support for Python 3 in in PL/Python and numerous PL/Perl improvements.
Uniqueness constraints for non-scalar data (exclusion constraints)
Improved key-value data support.
Automatic join removal, optimizing for ORM-generated queries.

PostgreSQL supports Solaris, Linux and Windows with binary installations. You may also download the source code and compile it on any platform with which you're working.

Do I think PostgreSQL is ready to go head to head with MySQL? Yes. Do I think that PostgreSQL has a chance to unseat MySQL as the "World's Most Popular Open Source Database Software?" Not for a second. I do think, however, that PostgreSQL will begin to raise corporate eyebrows and gain some enterprise adoption with its new, long-awaited feature set.

Commercial support is available through third parties and hosting companies.

As soon as PostgreSQL 9.0 production version is available, I'm grabbing it, installing it (In a VM, of course) and trying it out one more time before giving up on it forever.

What do you think of PostgreSQL? Do you use it in a production environment? Are you excited about version 9.0?

[joshberkus]

Ken,

I'm curious about what features weren't available in 8.0 which caused you to give up on PostgreSQL. We're always examining our development priorities, and your experience would be valuable. Thanks!

--Josh Berkus, PostgreSQL project

[nanotech]

PostgreSQL is the best database for ERP systems and works like a dream, check out xTuple OpenMFG. PostgreSQL is solid and can be used in very complex scenarios (quite a few lab QA and formula development systems use it) and just look at EnterpriseDB

[likbez]

That's pretty funny garbage...

PostgreSQL is way ahead on MySQL in many enterprise areas and this guy is talking how it will match it.

Both are probably too complex for a typical web database application.

I especially like the quote:
I gave PostgreSQL a good workout through attempting a setup of a web database application using Perl and PHP for the front end and automated back end. Again, PostgreSQL was too complex for me to use and therefore far too complex for many of my clients. I never felt comfortable enough with it to maintain my relationship with it.

This is a typical snobbish attitude of a person evaluating complex package for which it already has an alternative that works reasonably well. Tons of Solaris reviews by Linux users belong to the same category.

[anand2010]

This is the most ridiculous post I have ever read. Seriously, like someone mentioned earlier, this is garbabe! You should STOP spreading FUD about any projects NOW!

Stop living under a rock and open your eyes. MySQL is a high school project and PostgreSQL is Enterprise ready. I use both MySQL and PostgreSQL at work and PostgreSQL is miles ahead in terms of features, stability and ease-of-use. Everyone hates MysQL at work and we are stuck because of some legacy applications!

[mangoo_]

anand2010: PostgreSQL where it's now (stable 8.4), is certainly not enterprise ready.

Enterprise needs high availability and failover, and what PostgreSQL has to offer, is a laugh when compared to MySQL.

Hopefully, it'll finally change with the upcoming 9.0.

[Exception e]

@mangoo_ Do you mean that postgresql crashes more often?
The reason I use MySQL is that it has a huge array of tools with lot of options. I have the idea things got better now with postgre, but I am not sure whether the ecosystems of both are on par.

It is unclear for me what indispensable and unique features mysql offered you.

I expect postgre to outperform mysql with comlex queries; mysql handling of joins is a laugh in comparison with postgre.

[mangoo_]

I meant high availability and failover.

[rycamor]

@mangoo_ writes:

"Enterprise needs high availability and failover, and what PostgreSQL has to offer, is a laugh when compared to MySQL."

And I have to laugh when considering this statement, having been forced to use MySQL for the past few months on a large project.

For starters, replication was statement-based up until 5.0, which fails completely on any system with heavy use of functions and stored procedures, since there is so much chance for MySQL's famed non-deterministic behavior. Secondly, even with 5.1 and row-based replication, I had to use every tweak in the book, including slave_exed_mode=IDEMPOTENT in order to get replication which didn't fail at least once daily (which of course also opens you up to slave inconsistencies). Let's not even get into the lovely hack of using alternating odd-even autonumber PKs for multi-master replication.

So no... I am not at all concerned about whether PostgreSQL can compete with MySQL in the enterprise.

[mangoo_]

@rycamor: what did you use for replication in PostgreSQL in a large enterprise project?

[Exception e]

I meant high availability and failover.

Yes I was allready afraid of that. What made mysql to have a higher availability? Availability is a characteristic that is influenced by many factors. So what did you experience concretely?

For the discussion I would like to point to a fairly balanced and up-to-date comparison wiki.

[mangoo_]

@Exception_e: the most advanced replication / HA software for PostgreSQL is pgpool-ii (I think).

It's really hard to setup - so even if you think it's working correctly, it may not be.

And even if you do it - it may "hang" if you want to do recovery (ouch ouch, your Very Important Database is not responding, so make plenty of tests before using it in production), will not stop until the last client disconnects (well, there are some which are connected permanently...), may kick a backend occasionally, in a moment carefully chosen by Murphy's law.

If you tried both worlds, you'll notice the difference.

Note: never tested replication offered by the upcoming PostgreSQL 9.0, which shouldn't have these flaws.

[mangoo_]

@Exception_e: and last but not least, replication tools have some limitations (i.e. will not replicate everything), so there may be usage cases where you just can't have replication (or have to redesign your database, which may not be always possible).

[Exception e]

@mangoo_ Thanks, it is good to know these thing the day I need to rely on replication.

From the linked wiki I read pg 9.0 will have replication built-in (like mysql has), so things might indeed be better than in < pg 9.0
Strange it took so long.

[sam.m]

Just feel like giving my two cents. I've worked with both mysql and postgreSQL. Having compared the two I feel a postgres developer summed it up best (can't remember which one). For simple queries mysql is faster, while for complex queries postgreSQL is certainly the quicker of the two.

Applied to the real world, unless your creating a simple foo bar hello world application your queries won't be simple (some exceptions apply). The corporate world doesn't rely on simple queries. If it did most DBA will be done by your average Joe that thinks excel should be used to store data amongst large groups of people.

A properly set up PostgreSQL database will preform better than a mysql one, when faced with complex operations.

To the poster who says he thought PostgreSQL is to complicated your logic is far off. You state mysql is better because its not as complex as postgreSQL. First, in terms of job security I would rather know something challenging than simple. Second, how could you even rate/review/pass judgement on a product when you state you don't know how to use. It doesn't prove anything other than you should do your proper homework before making any real opinions.

You are stated that: "PostgreSQL will begin to raise corporate eyebrows". One of the worlds largest database is a modded PostgreSQL db by yahoo. In 2008 it was 2 petabytes, with about 24 billion updates per day. How is that for a raised corporate eyebrow?

I'm not really trying to attack you here, I just want you to get your facts right and start to do some real research. There are larger databases out there. PostgreSQL isn't as popular as mysql by a long shot. But I ask you this: Do you enjoy eating at McDonalds, one of the worlds most popular fast food joints? I personally won't because while it makes simple disgusting burgers quickly, it can never give you a great bloody steak no matter how much time it takes.

Just my two cents. Written by a Corporate IT Technical Manager. Obviously this is a personal opinion that has no relation to my employer at all. Just thought you should know my eyebrow was raised years ago.

-Sam

[gozo]

@sam.m: FYI, for human gastrointestinal tract, McDonalds burger is almost the same junk food as a bloody steak, just to point out some nonsense in your comparison.

[evanx]

Honestly I don't understand the statement, "PostgreSQL was too complex for me to use and therefore far too complex for many of my clients," because i find PostgreSQL very easy to setup and tweak. Oracle, on the other hand, I imagine is indeed "far too complex."

We run high volume transactional databases (millions of sales transactions a month) and PostgreSQL (and underlying Linux) have impressed me beyond words.

We use PostgreSQL 9.0 with streaming replication to a read-only standby, which we use heavily for running queries and reports. (I have uploaded an article to Scribd you can search for if you're interested.)

We expect to migrate to 9.1 soon. Although we do not require synchronous replication, we use inheritance for archive tables, and understand 9.1 has performance improvements in that area.