PostgreSQL 9.0.X or 8.4.X - performance, replication, stability

Question

I've been developing an app w/ server infrastructure for a while now, and I'm getting ready to release soon(ish).

So far the server backend has been running on PostgreSQL 8.4.7, and it's working as it should.

Right now, though, I'm thinking about upgrading to 9.0.X to be on a more recent release for the beta testing (and subsequent release), and to make it easier to set up database replication in the future.

My questions:

• Is PostgreSQL 9.0.4 a viable DB to use as of now? Are there any known major bugs?

• Are there any noticeable performance differences?

• Is replication (async ok, sync preferred) easier to set up in 9.0.X than in 8.4.X?

Answer 1

As for "viable DB for use" - sure. Pg is very stable.

As for performance differences - there were some improvements, check http://www.postgresql.org/docs/current/interactive/release-9-0.html

As for replication - it's hard to compare, as replication-wise, 9.0 is very different, as it has hot-standby and streaming replication, which were not available in 8.*

All in all - if you're setting new database, and you are not forced to use 8.4 - there is really no reason not go with 9.0

Answer 2

My questions:

Is PostgreSQL 9.0.4 a viable DB to use as of now? Are there any known major bugs?

Yes, it's a viable database and no, all known bugs are fixed. 9.0 allready had 4 patchrounds, that's why it's 9.0.4. New patches become every 3 to 6 months available.

  Are there any noticeable performance differences?

Yes, but it depends on your specific queries and/or workload. You have to test your situation to see the difference.

  Is replication (async ok, sync preferred) easier to set up in 9.0.X than in 8.4.X?

There is no replication in version 8.4, version 9.0 is the first release of PostgreSQL that has build-in replication. So yes, it's much easier, it's now possible.

Answer 3

PostgreSQL tries very hard to not have any open bugs. There aren't any serious bugs at all, outside of things that are known to be slower than they could be that are impractical to backport, in 9.0 that haven't already been fixed. That said, newer code normally has more unknown bugs than older stuff does, and you can't ever know how many unknown bugs there are by definition.

The main speed improvements in PostgreSQL 9.0 are from improvements in the query planner. The database as a whole isn't really faster on simple things, but there are plenty of joins and other complicated to execute queries that are much improved.

PostgreSQL 9.0 is much easier to replicate than earlier versions, presuming you're OK with replicating the whole database installation. If you want to only replicate a subset, you'll need external replication programs, both in 8.4 and 9.0. Synchronous replication is available on top of the built-in replication starting in 9.1, which is currently in beta testing and expected later this year.