TableSpace Design issues on Postgres 8.0 beta 5 - Mailing list pgsql-performance

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

note: previously posted on cygwin. but I think it is more appropriate for
this group