Re: Transparent table partitioning in future version of PG? - Mailing list pgsql-performance

From Simon Riggs
Subject Re: Transparent table partitioning in future version of PG?
Date
Msg-id 1241686458.6109.79.camel@ebony.2ndQuadrant
Whole thread Raw
In response to Re: Transparent table partitioning in future version of PG?  (Craig Ringer <craig@postnewspapers.com.au>)
Responses Re: Transparent table partitioning in future version of PG?
List pgsql-performance
On Thu, 2009-05-07 at 10:56 +0800, Craig Ringer wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> >> I think there should be a way to refer to individual partitions as
> >> objects.
> >
> > Yeah, the individual partitions should be nameable tables, otherwise we
> > will be reinventing a *whole* lot of management stuff to little gain.
> > I don't actually think there is anything wrong with using table
> > inheritance as the basic infrastructure --- I just want more smarts
> > about one particular use pattern of inheritance.
>
> Maybe it's worth examining and documenting existing partition setups,
> the reasoning behind them, and how they're implemented, in order to
> guide any future plans for native partitioning support?
>
> Maybe that's already been/being done. On the off chance that it's not:
>
> Ones I can think of:
>
> - Partitioning an equally active dataset by ranges over a key to improve
>  scan performance, INSERT/UPDATE costs on indexes, locking issues, etc.
>
> - The "classic" active/archive partition scheme where there's only one
> partition growing at any one time, and the others are historical data
> that's nowhere near as "hot".
>
> - A variant on the basic active/archive structure, where query activity
> decreases slowly over time and there are many partitions of recent data.
> Partitions are merged into larger ones as they age, somewhat like a RRD
> database.
>
> I also expect that in the future there will be demand for striping data
> across multiple partitions in different tablespaces to exploit
> in-parallel scanning (when/if supported) for better I/O utilization in
> multiple-disk-array situations. For example, partitioning on
> "MOD(id,10)" across 10 separate volumes, and firing off 10 concurrent
> scans, one per partition, to satisfy a query.

That's a good summary. It has already been documented and discussed, but
saying it again and again is the best way to get this across.

You've highlighted that partitioning is a feature with many underlying
requirements: infrequent access to data (frequently historical),
striping for parallelism and getting around RDBMS flaws (if any). We
must be careful to implement each requirement in full, yet separately,
so we don't end up with 60% functionality in each case by delivering an
average or least common denominator solution.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


pgsql-performance by date:

Previous
From: Dimitri
Date:
Subject: Re: Any better plan for this query?..
Next
From: Oleg Bartunov
Date:
Subject: Re: GiST index performance