Todo:PartitionedTables

From pgwiki

 WARNING: This page has been migrated to the PostgreSQL Wiki. Please do not edit this page or your changes may be lost!

Status Quo

Currently we allow the user to create arbitrary nested tables with arbitrary constraints and then the planner tries to detect at run-time which child tables are candidates for the query.

Limitations

• We never exclude the parent table
• We don't handle inserts on the parent table
• It requires a lot of manual work to set it up

Possible Directions

Oracle-Style: Allow users to declare their intention with partitioned tables. Ie, declare what the partition key is and what range or values are covered by each partition.

I think this would mean two new types of relation. One "meta-table" that acts like a view, in that it doesn't have an attached filenode. It would also have some kind of meta data about the partition key but no view definition, it would act like parent tables in nested table structure do now. The other would be "partition" which would be a separate namespace from tables and would have attached information about what values of the partition key it covered.

Pros:

• Makes it more reasonable to handle inserts automatically since the structure is explicit and doesn't require making logical deductions.
• More idiot-proof, ie you can't set up nonsensical combinations of constraints.
• Consistent with other databases and DBA expectations.

Cons:

• Less flexible, you can't set up arbitrary non-traditional structures such as having some data in the parent table or having extra columns in some children.