Postgres Database Design Issues in Tablespace

Поиск
Список
Период
Сортировка
От SehatRosny
Тема Postgres Database Design Issues in Tablespace
Дата
Msg-id BAY18-DAV140718DA5B7CAEC853BC8BDDB40@phx.gbl
обсуждение исходный текст
Ответы Re: Postgres Database Design Issues in Tablespace
Список 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
                    
         
      
      
    
             
             
                       
 
 

В списке pgsql-cygwin по дате отправления:

Предыдущее
От: laurie.burrow@powerconv.alstom.com
Дата:
Сообщение: pgpool on cygwin or windows
Следующее
От: Reini Urban
Дата:
Сообщение: Re: Postgres Database Design Issues in Tablespace