Large databases, performance

Hi,

Today we concluded test for database performance. Attached are results and the 
schema, for those who have missed earlier discussion on this.

We have (almost) decided that we will partition the data across machines. The 
theme is, after every some short interval a burst of data will be entered in 
new table in database, indexed and vacuume. The table(s) will be inherited so 
that query on base table will fetch results from all the children. The 
application has to consolidate all the data per node basis. If the database is 
not postgresql, app. has to consolidate data across partitions as well.

Now we need to investigate whether selecting on base table to include children 
would use indexes created on children table.

It's estimated that when entire data is gathered, total number of children 
tables would be around 1K-1.1K across all machines. 

This is in point of average rate of data insertion i.e. 5K records/sec and 
total data size, estimated to be 9 billion rows max i.e. estimated database 
size is 900GB. Obviously it's impossible to keep insertion rate on an indexed 
table high as data grows. So partitioning/inheritance looks better approach. 

Postgresql is not the final winner as yet. Mysql is in close range. I will keep 
you guys posted about the result.

Let me know about any comments..

Bye
 Shridhar

--
Price's Advice:	It's all a game -- play it to have fun.