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

Additional update:
After spending time in searching more option.
Both Postgresql and MySQL Claims
1. Postgresql:
http://www.postgresql.org/users-lounge/limitations.html
2.MySQL
(Drawback creating large numbers of tables)
http://forums.devshed.com/t27778/s.html

This might sound like crazy ideas. I just try to experiment several option
which are easy todo before developing).

After running auto generate tables 12 tables each for each of 1600
Tablespaces
Here are some statistics
1. For every 12 tables created in each Tablespace will create 35 files in
tablespace directory
2. For every 12 tables for each 1 tables space will creates additional
approx 17 additional new(eventhough it has it's own   tablespace files for
default database directory
3. The above screnario creating 19200 tables in database
4. It take approximately 2 hours for pgAdmin III when starting just to load
all tables info when first starting.
    Good news(it did not hang, it able to handle all 19200 tables once
loaded and easily browse all tables info)

I have not try to actually query the database from the web.
Seems like the whole approach of "large number of tables" is not good.
(As College Database Design course said....:):).

By creating 12 tables for each of 7000 Tablespace. Now files in folder is
not as huge as when everything in one TableSpace.


Any opinion is welcome...



"Rosny" <sehatrosny@hotmail.com> wrote in message
news:cp1o7f$13ad$2@news.hub.org...
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>