Subject: Re: Thousands of tables versus on table?
From: Mark Lewis <mark.lewis@mir3.com>
To: Thomas Andrews <tandrews@soliantconsulting.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <C289C6B1.A63%tandrews@soliantconsulting.com>
References: <C289C6B1.A63%tandrews@soliantconsulting.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Mon, 04 Jun 2007 11:15:43 -0700
Message-Id: <1180980943.31471.322.camel@archimedes>
Mime-Version: 1.0

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