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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
