Thread: Doubt about performance of my db design

Hi

 

I have a question about the performance of my db design . I have this situation:

 

- One schema base containing 20 tables whit data of all users of my system. (These tables will contain a lot of rows (millions)). The data of each user is

independent of the rest

- One schema per user with a view of the schema base containing only the data of this user.

 

It is a good design? how efficient Does Postgree manage these big views? 

 

In the other hand, i have another idea:

 

- One schema whith the 20 tables and one schema per user whith tables that make inheritance of these.

 

If i do that, i avoid to make views.

 

What design is the best?

 

 Thanks, in advance

 

Gustavo ( sorry for my english)

Gustavo wrote:
>
>
>     Hi
>
>     I have a question about the performance of my db design . I have
>     this situation:
>
>     - One schema base containing 20 tables whit data of all users of
>     my system. (These tables will contain a lot of rows (millions)).
>     The data of each user is
>     independent of the rest
>     - One schema per user with a view of the schema base
>     containing only the data of this user.
>

If there is only one user per schema, you don't need to make views to
separate users.

>
>     It is a good design? how efficient Does Postgree manage these big
>     views?
>
>
A view is nothing more than a SQL query made to look like a table.  You
will need to read about views in the documentation, I think.

>     In the other hand, i have another idea:
>
>     - One schema whith the 20 tables and one schema per user whith
>     tables that make inheritance of these.
>
>
You will probably need to read a bit about inheritance.  You could use
inheritance as well, yes.  However, I would do it within one schema.

>     If i do that, i avoid to make views.
>
>     What design is the best?
>

Your question was answered in general here:

http://archives.postgresql.org/pgsql-novice/2007-02/msg00094.php

Basically, you need to decide what suits your needs best.  If
performance is critical (limiting), then you will need to test to see
which is the best solution.

Sean