Re: Transparent table partitioning in future version of PG?

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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.

Those are some simpler schemes. Does anyone actively using partitioning
have particular schemes/details that're worth going into?

--
Craig Ringer