Re: Pre-creating partitions incurs insert penalty

Thanks..I'll rewrite the rules as triggers. Would that in any way impact
constraint exclusion for select statements ?..I assume not.

On Thu, May 28, 2009 at 5:46 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> Yeah, rules have more overhead the more partitions you have, whereas
> triggers do not. If you can switch to triggers you'd like see better
> performance, but be aware that plpgsql is kind of retarded when it
> comes to doing anything fancy with triggers.
>
> On Thu, May 28, 2009 at 6:43 PM, Anj Adu <fotographs(at)gmail(dot)com> wrote:
> > Partitioning is implemented via rules and check constraints to ensure
> > partition integrity.
> >
> > On Thu, May 28, 2009 at 5:21 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> > wrote:
> >>
> >> On Thu, May 28, 2009 at 5:22 PM, Anj Adu <fotographs(at)gmail(dot)com> wrote:
> >> > I noticed a very strange performance issue after I pre-create daily
> >> > partitions for the next month on a table that has a very large insert
> >> > volume
> >> > (30 million a day). After the partitions are created..the inserts seem
> >> > to
> >> > slow down. I verifiied that this was the issue by dropping the
> >> > partitions...When I dropped the pre-created partitions..the
> performance
> >> > issue disappeared. Looks like you cannot have too many partitions (in
> >> > this
> >> > case..I had a total of 35 partitions when the performance issue was
> >> > noticed)
> >>
> >> How are you enforcing partiitoning on your inserts? Via app
> >> knowledge, triggers, or rules? I'd expect rules might have a penalty
> >> with more partitions, but not expect it from app or trigger based
> >> partitioning.
> >
> >
>
>
>
> --
> When fascism comes to America, it will be intolerance sold as diversity.
>