From: Thomas Andrews <tandrews@soliantconsulting.com>
To: pgsql-performance@postgresql.org
Subject: Thousands of tables versus on table?
Date: Mon, 04 Jun 2007 13:40:01 -0400
Message-ID: <C289C6B1.A63%tandrews@soliantconsulting.com> (raw)
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.
Some of the indices in tableA and tableB do not index on the client ID
first.
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.
=thomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tandrews@soliantconsulting.com
Subject: Re: Thousands of tables versus on table?
In-Reply-To: <C289C6B1.A63%tandrews@soliantconsulting.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox