Обсуждение: Postgres Database Design Issues in Tablespace
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
SehatRosny schrieb: > 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). > 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. > B1. Use B option, > Creating 7000 TableSpace for Departments > - One Department has one tablespace > - Each Department has 20 tables > Question : > 1. Is it B1 the right approach? > 2. Is progresql will have problems if I have 7000 tablespace? why tablespace at all? do some caclulation, buy a large disc and forget about tablespace. (i.e. symlinks) postgresql-cygwin certainly will have problems which such a configuration. Please use a decent operating system and filesystem for such a crazy idea. cygwin is certainly not suited well for such a production database. consider native windows or any unix instead. NTFS is certainly not suited well for > 500 files per dir. consider a unix filesystem instead. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/
Hi, Thank you for the response. before the posting goes to further discussion. I realize this for cygwin discussion. You are right when running Postgresql need to be in native Windows or Linux. So that's why I am choosing Postgresql 8.0. I already post this issues in diffrent group. that's in pgsql performances. (just for clarification, I am not running it in cygwin environment. It just happens I do not not which group to post. And thought cygwin is for postgresql in windows environment). Thank you, Rosny >From: Reini Urban <rurban@x-ray.at> >To: SehatRosny <SehatRosny@hotmail.com> >CC: pgsql-cygwin@postgresql.org >Subject: Re: [CYGWIN] Postgres Database Design Issues in Tablespace >Date: Mon, 06 Dec 2004 17:34:30 +0100 > >SehatRosny schrieb: >>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). 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. >>B1. Use B option, >> Creating 7000 TableSpace for Departments >> - One Department has one tablespace >> - Each Department has 20 tables > >>Question : >> 1. Is it B1 the right approach? >> 2. Is progresql will have problems if I have 7000 tablespace? > >why tablespace at all? do some caclulation, buy a large disc and forget >about tablespace. (i.e. symlinks) > >postgresql-cygwin certainly will have problems which such a configuration. >Please use a decent operating system and filesystem for such a crazy idea. > >cygwin is certainly not suited well for such a production database. >consider native windows or any unix instead. > >NTFS is certainly not suited well for > 500 files per dir. >consider a unix filesystem instead. >-- >Reini Urban >http://xarch.tu-graz.ac.at/home/rurban/