Message-ID: <45916BBB.5070602@earlham.edu>
Date: Tue, 26 Dec 2006 13:36:43 -0500
From: Kevin Hunter <hunteke@earlham.edu>
User-Agent: Thunderbird 2.0b1 (Macintosh/20061206)
MIME-Version: 1.0
To: PostrgreSQL Novice List <pgsql-novice@postgresql.org>
Subject: Partitioning
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi All,

A friend has asked me about creating a unique table for individual users 
that sign up for his site.  (In essence, each user who signs up would 
essentially get a set of CREATE TABLE {users,friends,movies,eats}_<id> ( 
... ); statements executed, the idea being to reduce the number of rows 
that the DB needs to search or index/update in regards to a particular 
user id.)  The just seems ludicrous to me, because the database still 
needs to find those tables from its internal structure, not to mention 
that it just seems silly to me from a design perspective.  Something 
about unable to optimize any queries because not only is the WHERE 
clause in flux, but so is the FROM clause.

Question: Could someone explain to me why this would be bad idea, 
because I can't put into words why it is.  Or is it a good idea? 
(Towards learning, I'm looking for a technical, Postgres oriented 
answer, as well as the more general answer.)

Since he's worried about performance, methinks a better idea would be to 
use the partitioning support of Postgres.  Is this a good hunch?

(Side question: When did Postgres get partitioning support?  I see it 
referenced in the 8.1 documentation but not before.)


Thanks,

Kevin