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

database-performancedatabase-replicationpostgresql

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?

Best Answer

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

Related Solutions

Linux – PostgreSQL Replication

Short answer - there's no such solution yet for PostgreSQL if you need online readonly slaves.

There're two major development projects currently going on in this area which are included in PostgreSQL 9.0 (Spring/Summer 2010), namely:

Synchronous Replication:

http://wiki.postgresql.org/wiki/NTT's_Development_Projects

Read only hot standby slaves:

http://wiki.postgresql.org/wiki/Hot_Standby

which in combination aim to achieve the ease of use of MySQL-style replication minus the bugs/issues MySQL has plus the reliability users know from PostgreSQL.

All of this was kicked off by a manifest from the PostgreSQL Core Team in 2008:

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php

The PostgreSQL replication solutions to this day with the largest user base are Slony-I (more expensive for writes, makes schema changes fiddly), WAL shipping/walmgr (Slaves can't be used online) and pgQ/londiste from Skype/Skytools (more tools/building blocks than a finished solution).

I've written a few things on Log Shipping, walmgr and Slony-I, see

http://blogs.amd.co.at/mt/mt-search.cgi?blog_id=1&tag=pgrep&limit=20 for more information.

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

OK, got it from someone else. This query should do the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

Best Answer

Related Solutions

Linux – PostgreSQL Replication

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

Related Topic