On Fri, 13 Feb 2015 16:29:02 +0000
Seref Arikan <serefarikan@kurumsalteknoloji.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@potentialtech.com>
> wrote:
>
> > 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
> >
--
Bill Moran