Table partitioning

From: Herouth Maoz <herouth(at)unicell(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: Table partitioning
Date: 2013-10-28 16:27:42
Message-ID: 3B23DF71-B302-4A6B-84B3-56715EE7119A@unicell.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a rather large and slow table in Postgresql 9.1. I'm thinking of partitioning it by months, but I don't like the idea of creating and dropping tables all the time.

I'm thinking of simply creating 12 child tables, in which the check condition will be, for example, date_part('month'', time_arrived) = 1 (or 2 for February, 3 for March etc.).

I'll just be deleting records rather than dropping tables, the same way I do in my current setup. I delete a week's worth every time.

So, I have two questions.

First, is constraint exclusion going to work with that kind of condition? I mean, if my WHERE clause says something like "time_arrived >= '2013-04-05' and time_arrived < '2013-04-17'", will it be able to tell that date_part("month",time_arrived) for all the records is 4, and therefore avoid selecting from any partitions other than the april one?

Second, when I delete (not drop!) from the mother table, are records deleted automatically from the child tables or do I need to create rules/triggers for that?

TIA,
Herouth

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Elliot 2013-10-28 16:31:57 Re: Table partitioning
Previous Message Yuri Khan 2013-10-28 11:45:13 Replication by file syncing and data directory permissions