User-Agent: Microsoft-Entourage/11.3.3.061214
Date: Mon, 04 Jun 2007 15:57:41 -0400
Subject: Re: Thousands of tables versus on table?
From: Thomas Andrews <tandrews@soliantconsulting.com>
To: Gregory Stark <stark@enterprisedb.com>
CC: Mark Lewis <mark.lewis@mir3.com>,
	<pgsql-performance@postgresql.org>
Message-ID: <C289E6F5.A74%tandrews@soliantconsulting.com>
Thread-Topic: [PERFORM] Thousands of tables versus on table?
Thread-Index: Acem4pvL2qTmdhLVEdyDLwAX8s2kmQ==
In-Reply-To: <877iqjmro5.fsf@oxford.xeocode.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit


On 6/4/07 3:43 PM, "Gregory Stark" <stark@enterprisedb.com> wrote:

> 
> "Thomas Andrews" <tandrews@soliantconsulting.com> writes:
> 
>> I guess my real question is, does it ever make sense to create thousands of
>> tables like this?
> 
> Sometimes. But usually it's not a good idea.
> 
> What you're proposing is basically partitioning, though you may not actually
> need to put all the partitions together for your purposes. Partitioning's main
> benefit is in the management of the data. You can drop and load partitions in
> chunks rather than have to perform large operations on millions of records.
> 
> Postgres doesn't really get any faster by breaking the tables up like that. In
> fact it probably gets slower as it has to look up which of the thousands of
> tables you want to work with.
> 
> How often do you update or delete records and how many do you update or
> delete? Once per day is a very low frequency for vacuuming a busy table, you
> may be suffering from table bloat. But if you never delete or update records
> then that's irrelevant.

It looks like the most inserts that have occurred in a day is about 2000.
The responders table has 1.3 million records, the responses table has 50
million records.  Most of the inserts are in the responses table.

> 
> Does reindexing or clustering the table make a marked difference?
> 

Clustering sounds like it might be a really good solution.  How long does a
cluster command usually take on a table with 50,000,000 records?  Is it
something that can be run daily/weekly?

I'd rather not post the schema because it's not mine - I'm a consultant.  I
can tell you our vacuum every night is taking 2 hours and that disk IO is
the real killer - the CPU rarely gets higher than 20% or so.

=thomas