Message-ID: <46645DD9.7030805@soliantconsulting.com>
Date: Mon, 04 Jun 2007 14:45:45 -0400
From: Thomas Andrews <tandrews@soliantconsulting.com>
User-Agent: Thunderbird 2.0.0.0 (Macintosh/20070326)
MIME-Version: 1.0
To: Mark Lewis <mark.lewis@mir3.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Thousands of tables versus on table?
References: <C289C6B1.A63%tandrews@soliantconsulting.com>
	<1180980943.31471.322.camel@archimedes>
In-Reply-To: <1180980943.31471.322.camel@archimedes>
Content-Type: multipart/mixed; boundary="------------010406050206040002020106"

This is a multi-part message in MIME format.
--------------010406050206040002020106
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

We're running 7.4 but will be upgrading to 8.2.

The responses table has 20,000,000 records.

Sometimes (but not all the time) an insert into the responses table can 
take 5-6 seconds.

I guess my real question is, does it ever make sense to create thousands 
of tables like this?

=thomas

Mark Lewis wrote:
> On Mon, 2007-06-04 at 13:40 -0400, Thomas Andrews wrote:
>> I have several thousand clients.  Our clients do surveys, and each survey
>> has two tables for the client data,
>>
>>    responders
>>    responses
>>
>> Frequent inserts into both table.
>>
>> Right now, we are seeing significant time during inserts to these two
>> tables.
> 
> Can you provide some concrete numbers here?  Perhaps an EXPLAIN ANALYZE
> for the insert, sizes of tables, stuff like that?
> 
>> Some of the indices in tableA and tableB do not index on the client ID
>> first.
> 
> What reason do you have to think that this matters?
> 
>> So, we are considering two possible solutions.
>>
>>  (1) Create separate responders and responses tables for each client.
>>
>>  (2) Make sure all indices on responders and responses start with the
>>   client id (excepting, possibly, the primary keys for these fields) and
>>   have all normal operation queries always include an id_client.
>>
>> Right now, for example, given a responder and a survey question, we do a
>> query in responses by the id_responder and id_survey.  This gives us a
>> unique record, but I'm wondering if maintaining the index on
>> (id_responder,id_survey) is more costly on inserts than maintaining the
>> index (id_client,id_responder,id_survey) given that we also have other
>> indices on (id_client,...).
>>
>> Option (1) makes me very nervous.  I don't like the idea of the same sorts
>> of data being stored in lots of different tables, in part for long-term
>> maintenance reasons.  We don't really need cross-client reporting, however.
> 
> What version of PG is this?  What is your vacuuming strategy?  Have you
> tried a REINDEX to see if that helps?
> 
> -- Mark Lewis
> 


--------------010406050206040002020106
Content-Type: text/x-vcard; charset=utf-8;
 name="tandrews.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="tandrews.vcf"

begin:vcard
fn:Thomas Andrews
n:Andrews;Thomas
org:Soliant Consulting
adr;dom:;; 14 N. Peoria Street 2H;Chicago;IL;60607
email;internet:tandrews@soliantconsulting.com
title:Application Engineer
tel;work:617-868-5580
tel;home:617-868-5580
tel;cell:617-599-8728
x-mozilla-html:FALSE
url:http://www.soliantconsulting.com/
version:2.1
end:vcard


--------------010406050206040002020106--