|
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
|
