Re: Number of tables

Craig Ringer ha scritto:
> 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?
>
No no...

>> 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.
>
The number of the user probably will increase with the time...

> 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.
>
It's true...i don't think to this problem..


> 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.

Every table have between 1.000 and 100.000(MAX) records...

Do you think i don't have problem in performance ??
The user only view the record whit its user_id....

I am thinking to redesign the DB