Skip site navigation (1) Skip section navigation (2)

Performance with partitions/inheritance and multiple tables

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 (view raw or flat)
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

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-12-24 14:57:31
Subject: Re: SATA drives performance
Previous:From: Richard NeillDate: 2009-12-24 14:40:38
Subject: Re: SATA drives performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group