Re: Transparent table partitioning in future version of PG?

On Thu, 2009-05-07 at 10:56 +0800, Craig Ringer wrote:
> Tom Lane wrote:
> > Alvaro Herrera <> 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