From: | Radhika S <rs88820(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Performance with partitions/inheritance and multiple tables |
Date: | 2009-12-24 14:42:25 |
Message-ID: | a222cb860912240642m40b435c9p39b5ab96921d2ec@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2009-12-24 14:57:31 | Re: SATA drives performance |
Previous Message | Richard Neill | 2009-12-24 14:40:38 | Re: SATA drives performance |