Re: Number of tables

On Thu, 2009-08-20 at 09:01 +0200, Fabio La Farcioli wrote:

> Each user have a 2 table of work...I finally have 2.000 (users) x 2
> tables = 4.000 tables!

Hmm, ok. Does each user really need two tables each? Why?

Does the set of tables for each user have a different structure? Or are
you separating them so you can give each user a separate database role
and ownership of their own tables?

> Postgres support an elevate number of tables??

Thousands? Sure.

> i have problem of performance ???
>
Yes, you probably will. There is a cost to having _lots_ of tables in
PostgreSQL in terms of maintaining table statistics, autovacuum work,
etc. I doubt it'll be too bad at 4000 tables, but if your user numbers
keep growing it could become a problem.

Other concerns are that it'll also be hard to maintain your design,
difficult to write queries that read data from more than one user, etc.
If you need to change the schema of your user tables you're going to
have to write custom tools to automate it. It could get very clumsy.

Instead of one or two tables per user, perhaps you should keep the data
in just a few tables, with a composite primary key that includes the
user ID. eg given the user table:

CREATE TABLE user (
id SERIAL PRIMARY KEY,
name text
);

instead of:

CREATE TABLE user1_tablea(
id INTEGER PRIMARY KEY,
blah text,
blah2 integer
);

CREATE TABLE user2_tablea(
id INTEGER PRIMARY KEY,
blah text,
blah2 integer
);

... etc ...

you might write:

CREATE TABLE tablea (
user_id INTEGER REFERENCES user(id),
id INTEGER,
PRIMARY KEY(user_id, id),
blah text,
blah2 integer
);

You can, of course, partition this table into blocks of user-IDs behind
the scenes, but your partitioning is invisible to your web app and can
be done solely for performance reasons. You don't have to try juggling
all these little tables.

Note that whether this is a good idea DOES depend on how much data
you're going to have. If each user table will have _lots_ of data, then
individual tables might be a better approach after all. It's also a
benefit if you do intend to give each user their own database role.

--
Craig Ringer