Re: about partitioning

hei,

thanks everybody for this great feedback.

I'll sure look deeper into this.

Joao

On Sat, 2008-09-13 at 16:48 -0400, Robert Treat wrote:
> On Thursday 11 September 2008 07:47:00 Joao Ferreira gmail wrote:
> > Hello all,
> >
> > my application is coming to a point on which 'partitioning' seems to be
> > the solution for many problems:
> >
> > - query speed up
> > - data elimination speed up
> >
> > I'dd like to get the feeling of it by talking to people who use
> > partitioning, in general..
> >
> > - good, bad,
>
> good :-)
>
> > - hard to manage, easy to manage,
>
> I think the upfront costs for managing a partitioning setup are higher with
> postgres than other systems, but there is nothing that you shouldn't be able
> to automate in a cron script (at which point management becomes easy), plus
> postgres gives you some interesting flexibility that is harder to find in
> other setups.
>
> > - processing over-head during INSERT/UPDATE,
>
> you can setup inserts to have relativly little overhead, but it requires more
> management/maintence work up front. Updates within a partition also have
> relativly little extra overhead, especially if you put in a little
> application logic to figure out how to work on a partition directly. Updates
> where you are changing the partition key value are always more problematic
> though.
>
> > - stability/compatibility of pg_dump and restore operations,
>
> no real issues here as long as your on recent enough versions to do wildcard
> table matching for individual tables.
>
> > - how many partitions would be reasonable for read _and_ write access
> > optimal speed;
> >
>
> again, this depends on how exactly your working on the data. For example, we
> have tables with over a thousand partitions on them; in those scenarios all
> data is written into a single partition (with a new partition created daily),
> and the qeury patterns are really straightforward... last month gets a lot of
> queries, lasat three months not so much, last year barely any, and beyond
> that is pretty much just archive info. That said, we have other systems where
> that wouldnt work at all (for example, a static number of partitions, all of
> which are queried activly).
>
> For some more info, I've given at least one presentation on the topic, which
> seems to be missing from the omniti site, but I've uploaded it to
> slideshare...
> http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation
>
> HTH.
>