Re: Thousands of tables versus on table?

Oh, and we vacuum every day. Not sure about REINDEX, but I doubt we
have done that.

=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
>