Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id D6E1B9FB2AB;
	Thu, 18 Jan 2007 17:04:20 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 10730-02; Thu, 18 Jan 2007 17:04:12 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from exchange.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25])
	by postgresql.org (Postfix) with ESMTP id 2412D9FA131;
	Thu, 18 Jan 2007 17:04:14 -0400 (AST)
Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com
	([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ;
	Thu, 18 Jan 2007 21:04:07 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	18 Jan 2007 15:04:07 -0600
Subject: Re: [PERFORM] Postgres and really huge tables
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Brian Hurt <bhurt@janestcapital.com>
Cc: pgsql-performance@postgresql.org, pgsql-advocacy@postgresql.org
In-Reply-To: <45AFD927.9050605@janestcapital.com>
References: <4939.1164740825@sss.pgh.pa.us>
	<200611282344.kASNijt09203@momjian.us>
	<456CE066.50803@cheapcomplexdevices.com>
	<456D8A65.3080400@janestcapital.com>
	<1164812624.9690.500.camel@archimedes>
	<456DA476.4000606@janestcapital.com>
	<456DB8C9.7020206@cheapcomplexdevices.com>
	<45AFD927.9050605@janestcapital.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1169154247.9586.100.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Thu, 18 Jan 2007 15:04:07 -0600
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/94
X-Sequence-Number: 10576

On Thu, 2007-01-18 at 14:31, Brian Hurt wrote:
> Is there any experience with Postgresql and really huge tables?  I'm 
> talking about terabytes (plural) here in a single table.  Obviously the 
> table will be partitioned, and probably spread among several different 
> file systems.  Any other tricks I should know about?
> 
> We have a problem of that form here.  When I asked why postgres wasn't 
> being used, the opinion that postgres would "just <explicitive> die" was 
> given.  Personally, I'd bet money postgres could handle the problem (and 
> better than the ad-hoc solution we're currently using).  But I'd like a 
> couple of replies of the form "yeah, we do that here- no problem" to 
> wave around.

It really depends on what you're doing.

Are you updating every row by a single user every hour, or are you
updating dozens of rows by hundreds of users at the same time?

PostgreSQL probably wouldn't die, but it may well be that for certain
batch processing operations it's a poorer choice than awk/sed or perl.

If you do want to tackle it with PostgreSQL, you'll likely want to build
a truly fast drive subsystem.  Something like dozens to hundreds of
drives in a RAID-10 setup with battery backed cache, and a main server
with lots of memory on board.

But, really, it depends on what you're doing to the data.