Postgres Database Design Issues in Tablespace - Mailing list pgsql-cygwin
| From | SehatRosny |
|---|---|
| Subject | Postgres Database Design Issues in Tablespace |
| Date | |
| Msg-id | BAY18-DAV140718DA5B7CAEC853BC8BDDB40@phx.gbl Whole thread Raw |
| Responses |
Re: Postgres Database Design Issues in Tablespace
|
| List | 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.
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).
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.
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
1. Is it B1 the right approach?
2. Is progresql will have problems if I have 7000 tablespace?
Thank you,
Rosny
pgsql-cygwin by date: