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