Subject: Re: Getting even more insert performance (250m+rows/day)
From: Mark Lewis <mark.lewis@mir3.com>
To: "Daniel J. Luke" <dluke@geeklair.net>
Cc: Dave Dutcher <dave@tridecap.com>, pgsql-performance@postgresql.org
In-Reply-To: <DAFB8564-C235-4E36-A976-A01C9371E408@geeklair.net>
References: <01f701c67f6d$0578bf40$8300a8c0@tridecap.com>
	<DAFB8564-C235-4E36-A976-A01C9371E408@geeklair.net>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Wed, 24 May 2006 13:18:32 -0700
Message-Id: <1148501913.28095.70.camel@archimedes>
Mime-Version: 1.0

> The data gets inserted in batches every 5 minutes and I potentially  
> have people querying it constantly, so I can't remove and re-create  
> the index.

How live does your data need to be?  One possibility would be to use a
separate table for each batch instead of a separate table per day,
create the indexes after the import and only after the indexes have been
created make the table available for user queries.

You'd be trading latency for throughput in that case.

Also, you mentioned that you're CPU-bound, but that you have multiple
CPU's.  In that case, performing N concurrent imports (where N is the
number of processor cores available) might be a win over a single-
threaded import.

-- Mark Lewis