Re: What's a reasonable maximum number for table partitions?

Hi Bill,
Could you point at some resource(s) that discuss inserting directly into
the partition? Would it be possible to read directly from the partition as
well?

Regards
Seref

On Fri, Feb 13, 2015 at 4:15 PM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> On Fri, 13 Feb 2015 11:12:13 -0500
> Vick Khera <vivek(at)khera(dot)org> wrote:
>
> > On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> >
> > > Does anybody have experience with huge number of partitions if so where
> > > did you start running into trouble?
> >
> > I use an arbitrary 100-way split for a lot of tracking info. Just modulo
> > 100 on the ID column. I've never had any issues with that. If you can
> > adjust your queries to pick the right partition ahead of time, which I am
> > able to do for many queries, the number of partitions shouldn't matter
> > much. Only rarely do I need to query the primary table.
> >
> > I don't think your plan for 365 partitions is outrageous on modern large
> > hardware. For 1000 partitions, I don't know. It will depend on how you
> can
> > optimize your queries before giving them to postgres.
>
> Worked on a project last year where we did 256 partitions. Didn't
> experience
> any problems, but I don't recall if 256 was an arbitrary number or if we
> did any significant testing into whether it was the sweet spot. In any
> event,
> we did a LOT of performance testing and found that 256 partitions performed
> very well. I second Vick's comments on selecting the partition ahead of
> time,
> in particular, we realized HUGE performance gains on inserts when our code
> determined the partition ahead of time and inserted directly into the
> partition instead of into the primary table.
>
> --
> Bill Moran
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>