Re: Table partitioning

On 2013-10-28 12:27, Herouth Maoz wrote:
> 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
theidea 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
worthevery 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
theapril one? 
>
> Second, when I delete (not drop!) from the mother table, are records deleted automatically from the child tables or
doI need to create rules/triggers for that? 
>
>
> TIA,
> Herouth
>
1. No - you'd need a condition like "where date_part("month",
time_arrived) = 1" in your select statements in order for the constraint
exclusion to kick in
2. Yes - there is no need to create rules or triggers for deletes on the
parent table (check out the syntax for "delete from <table>" versus
"delete from only <table>)