From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Thomas Andrews <tandrews@soliantconsulting.com>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Thousands of tables versus on table?
Date: Mon, 04 Jun 2007 15:20:55 -0500
Message-ID: <46647427.4030103@g2switchworks.com> (raw)
In-Reply-To: <877iqjmro5.fsf@oxford.xeocode.com>
References: <C289C6B1.A63%tandrews@soliantconsulting.com>
<1180980943.31471.322.camel@archimedes>
<46645DD9.7030805@soliantconsulting.com>
<877iqjmro5.fsf@oxford.xeocode.com>
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.
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: smarlowe@g2switchworks.com, stark@enterprisedb.com, tandrews@soliantconsulting.com, mark.lewis@mir3.com
Subject: Re: Thousands of tables versus on table?
In-Reply-To: <46647427.4030103@g2switchworks.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