Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:

> CREATE PARTITION transaction_2008_11 ON transaction WHERE record_date
> BETWEEN '2008-11-01' AND '2008-11-30';

I think the main advantage to a better partitioning method would be teaching
Postgres about the partition key. Instead of a collection of different
constraints Postgres would know that "record_date" is *always* the partition
key. So it wouldn't have to be specified every time you declare a partition.

> I like the idea of using table inheritance as a foundation for this
> feature, but I think it's not going to be very useful for real-world
> applications without cross-table indexes.

Well we could add support for cross-table indexes. It's not hard from the
point of low level implementation -- just include the table oid in the index
pointers. Figuring out how to represent such a thing at the index description
point of view would be quite tricky though.

*But*... in practice I would suggest that cross-table indexes are actually
very rarely useful. Having them defeats much of the advantage of partitioning
in the first place. Suddenly you would not be able to instantly drop and load
whole partitions. They're a big check-list item that people want to have
before they partition in case they need them but then they find out that the
down-sides of actually using them makes them quite useless.

Postgres's current architecture actually has a big advantage over more
methodical partitioning methods in this case. You can always add additional
constraints on other columns even if they aren't the "real" partitioning key.
So for example if you partition the invoice table by month once you close the
books for a previous month you can add a constraint WHERE invoice_id < 'xxx'.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!