Message-ID: <4CAE3FDB.1040909@2ndquadrant.com>
Date: Thu, 07 Oct 2010 17:47:07 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Aaron Turner <synfinatic@gmail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: large dataset with write vs read clients
References: <AANLkTimNJc=0ffgRO_gO6Vsq06=aqBYXZouhbv0kBe-i@mail.gmail.com>
In-Reply-To: <AANLkTimNJc=0ffgRO_gO6Vsq06=aqBYXZouhbv0kBe-i@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Aaron Turner wrote:
> Are newer PG versions more memory efficient?
>   

Moving from PostgreSQL 8.1 to 8.3 or later should make everything you do 
happen 2X to 3X faster, before even taking into account that you can 
tune the later versions better too.  See 
http://suckit.blog.hu/2009/09/29/postgresql_history for a simple 
comparison of how much performance jumped on both reads and writes in 
the later versions than what you're running.  Memory consumption will on 
average decrease too, simply via the fact that queries start and finish 
more quickly.  Given an even workload, there will be less of them 
running at a time on a newer version to keep up.

Given the size of your database, I'd advise you consider a migration to 
a new version ASAP.  8.4 is a nice stable release at this point, that's 
the one to consider moving to.  The biggest single problem people 
upgrading from 8.1 to 8.3 or later see is related to changes in how data 
is cast between text and integer types; 1 doesn't equal '1' anymore is 
the quick explanation of that.  See 
http://wiki.postgresql.org/wiki/Version_History for links to some notes 
on that, as well as other good resources related to upgrading.  This may 
require small application changes to deal with.

Even not considering the performance increases, PostgreSQL 8.1 is due to 
be dropped from active support potentially as early as next month:  
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

Also:  PostgreSQL 8.1.3 has several known bugs that can lead to various 
sorts of nasty data corruption.  You should at least consider an 
immediate upgrade to the latest release of that version, 8.1.22.  Small 
version number increases in PostgreSQL only consist of serious bug 
fixes, not feature changes.  See 
http://www.postgresql.org/support/versioning for notes about the 
project's standard for changes here, and how it feels about the risks of 
running versions with known bugs in them vs. upgrading.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book