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

On Fri, 13 Feb 2015 11:12:13 -0500
Vick Khera <vivek@khera.org> wrote:

> On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuckun@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general