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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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
>



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"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.

Does reindexing or clustering the table make a marked difference?

I would suggest you post your schema and the results of "vacuum verbose".

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 6/4/07, Thomas Andrews <tandrews@soliantconsulting.com> wrote:
>
>
>
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


What OS are you running on?


--
Yudhvir Singh Sidhu
408 375 3134 cell

Linux 2.4.9, if I¹m reading this right.

=thomas


On 6/4/07 4:08 PM, "Y Sidhu" <ysidhu@gmail.com> wrote:

> On 6/4/07, Thomas Andrews <tandrews@soliantconsulting.com> wrote:
>>
>>
>>
>> On 6/4/07 3:43 PM, "Gregory Stark" <stark@enterprisedb.com> wrote:
>>
>>> >
>>> > "Thomas Andrews" < tandrews@soliantconsulting.com
>>> <mailto: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
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>
>
> What OS are you running on?
>

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

How many gigabytes of stuff do you have in this database ?
( du -sh on the *right* directory will suffice, don't include the logs
etc, aim for data/base/oid)


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Thomas Andrews" <tandrews@soliantconsulting.com> writes:

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

ouch, ok, with 50M records cluster isn't going to be quick either, especially
if you have a lot of indexes.

With those kinds of numbers and with the kind of workload you're describing
where you have different areas that are really complete separate you might
consider partitioning the table. That's essentially what you're proposing
anyways.

Honestly table partitioning in Postgres is pretty young and primitive and if
you have the flexibility in your application to refer to different tables
without embedding them throughout your application then you might consider
that. But there are also advantages to being able to select from all the
tables together using the partitioned table.

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

Do you ever update or delete these records? If you never update or delete
records then the vacuum is mostly a waste of effort anyways. (You still have
to vacuum occasionally to prevent xid wraparound but that's much much less
often).

If you do delete records in large batches or have lots of updates then
vacuuming daily with default fsm settings probably isn't enough.

How many indexes do you have?

And if they don't all have client_id in their prefix then I wonder about the
plans you're getting. It's unfortunate you can't post your schema and query
plans. It's possible you have some plans that are processing many more records
than they need to to do their work because they're using indexes or
combinations of indexes that aren't ideal.
specific enough

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Gregory Stark 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.
>

That's not entirely true. PostgreSQL can be markedly faster using
partitioning as long as you always access it by referencing the
partitioning key in the where clause. So, if you partition the table by
date, and always reference it with a date in the where clause, it will
usually be noticeably faster. OTOH, if you access it without using a
where clause that lets it pick partitions, then it will be slower than
one big table.

So, while this poster might originally think to have one table for each
user, resulting in thousands of tables, maybe a compromise where you
partition on userid ranges would work out well, and keep each partition
table down to some 50-100 thousand rows, with smaller indexes to match.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate