Skip site navigation (1) Skip section navigation (2)

Re: Question about Performance of my db design

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Gustavo <gustavor(at)intercomgi(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Question about Performance of my db design
Date: 2007-02-19 13:58:16
Message-ID: 45D9ACF8.2060008@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: A GilmoreDate: 2007-02-20 01:08:08
Subject: Using distinct with sum()
Previous:From: GustavoDate: 2007-02-19 13:38:45
Subject: Question about Performance of my db design

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group