Radhika,

If the data is 9 million rows, then I would suggest that you leave it as it is, 
unless the server configuration and the number of users firing queries 
simultaneously is a matter of concern.

Try creating indexes on often used fields and use EXPLAIN to speed performance 
of the queries ... and of course proper configuration of autovacuum. I have 
seen query results within a few ms. on similar amount of data on a 2GB RHEL 
RAID 5 system, so it should not have been an issue.

HTH,

Shrirang Chitnis
------------------------------------------------------------------------------------------------------

From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Radhika S
Sent: Thursday, December 24, 2009 8:12 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Performance with partitions/inheritance and multiple tables

Hi,
We currently have a large table (9 million rows) of which only the last couple 
of days worth of data is queried on a regular basis.
To improve performance we are thinking of partitioning the table.

One idea is:
Current_data = last days worth
archive_data < today (goes back to 2005)

The idea being proposed at work is:
current_data = today's data
prior years data - be broken down into one table per day
archive_data - data older than a year.

My question is:
a) Does postgres suffer a performance hit say if there are 200 child tables.
b) What about aggregation between dates in the last year. eg total sales for 
firm a  for the last year. It will need to look up n number of tables.

Any ideas, tips, gotchas in implementing partitioning would be welcome. It is a 
somewhat mission critical (not trading, so not as mission critical) system.

How expensive is maintaining so many partitions both in terms of my writing / 
maintaining scripts and performance.

Thanks in advance.
Radhika

The information contained in this message, including any attachments, is 
attorney privileged and/or confidential information intended only for the use 
of the individual or entity named as addressee.  The review, dissemination, 
distribution or copying of this communication by or to anyone other than the 
intended addressee is strictly prohibited.  If you have received this 
communication in error, please immediately notify the sender by replying to the 
message and destroy all copies of the original message.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Reply via email to