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

Postgres Database Design Issues in Tablespace

From: "SehatRosny" <SehatRosny(at)hotmail(dot)com>
To: <pgsql-cygwin(at)postgresql(dot)org>
Subject: Postgres Database Design Issues in Tablespace
Date: 2004-12-06 10:33:48
Message-ID: BAY18-DAV140718DA5B7CAEC853BC8BDDB40@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-cygwin
Hi,
 
This kind of long email.
 
 After searching the mailing list, have not found good answer
 for TableSpace. So, I try to post this question.
 
My question 
Question : 
  1 Which option from below scenario will be good in term of performance and
future scalability?
  2. Is it Option B1 below the right approach?
  3. Is progresql will have problems if I have 7000 tablespace?
 
------------------------------------------------------------------- 
Environment : 
 - Windows 2003
 - Postgresql 8.0 beta 5
 
Scenario :
Original Design:
  Total Tables 40:
     - 20 tables are main tables responsible for the others 20 tables
     - the others 20 tables are specific for each department.
     - from these 20 tables(departments)
        there are 4-5 tables that will contain approx 20 millions records
       (these tables will be hit every times access to the website).
       
Refering to 20 tables which can be partition    
A. All departments tables is put into 20 tables.
   some querying of 20 millions records.
   
B. For each department create tablespace. (Which means, if there
 are 7000 departments, there will be 7000 tablespace each contains
 20 tables). 
     
 
Question : Which option will be good in term of performance
           and future scalability?
           
A1. Use A option, 
    As tables become huge. partition the tables which hits often
    and has large size file(usually when it bigger than 2-3 GB size)
    into separate tablespace.
    
    Problems in A1 approach : 
      1. query take very long. It might be resolved
        - indexing, better written pgsql statement.
    
    Advantage : total files are small. around 1000 in one directory
    
    
B1. Use B option,
    Creating 7000 TableSpace for Departments
      - One Department has one tablespace
      - Each Department has 20 tables
    
    Advantage :
      - each table is small and query is very fast.
      - scalability. As the sites grows, contents grows. will 
        not effect future scalability as much as A1. 
        in A1 the query already max out for performance partition.
        in B1 the query has not max out yet because the data is 
        already distribute across thousands of tables
     
    Disadvantage:
      - total numbers of files is huge. 
        (after creating 7000 tablespace, and start
         table automatic generator to create 20 tables
         for each 7000 tablespace. 
         After running the 1500th tablespace. 
         Each TableSpace has : 35 files
         Surprisingly the default table space already has 20000 files)
       - Need to use dynamic table name query. (this is ok,
         since there are not very complex sql statement logic)
         
I am trying to choose option B1, as it is good for future scability.
 
Question :
  1. Is it B1 the right approach?
  2. Is progresql will have problems if I have 7000 tablespace?
  
  
Thank you,
Rosny
                    
         
      
      
    
             
             
                       
  

 

Responses

pgsql-cygwin by date

Next:From: Reini UrbanDate: 2004-12-06 16:34:30
Subject: Re: Postgres Database Design Issues in Tablespace
Previous:From: AJIT K KADARIDate: 2004-12-06 06:12:34
Subject: Postgresql installation-initdb failed

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