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

Thanks, google it is then ;)

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

> On Fri, 13 Feb 2015 16:29:02 +0000
> Seref Arikan <serefarikan(at)kurumsalteknoloji(dot)com> wrote:
>
> > Hi Bill,
> > Could you point at some resource(s) that discuss inserting directly into
> > the partition?
>
> Not off the top of my head, I would have to google just like
> you would.
>
> > Would it be possible to read directly from the partition as
> > well?
>
> Certainly. Whatever process you use to determine the name of the
> partition in the partition config will work anywhere else. For example,
> if the partition config is picking a table partition based on % 256
> (which is what we were doing, based on an integer entity ID) you can
> do that same math in whatever programming language the application is
> written in (in our case it was PHP).
>
> > 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
> > >
>
>
> --
> Bill Moran
>