Re: On partitioning - Mailing list pgsql-hackers

Hi,

> From: Robert Haas [mailto:robertmhaas@gmail.com]
> Sent: Saturday, November 08, 2014 5:41 AM
>
> I'd be in favor of that.

Thanks!

>  I am not sure whether the code is close
> enough to what we need to be really useful, but that's for you to
> decide.

Hmm, I'm not entirely convinced about the patch as it stands either but, I will 
try to restate below what the patch in its current state does anyway (just to 
refresh):

The patch provides syntax to:* Specify partitioning key, optional partition definitions within CREATE TABLE,* A few
ALTERTABLE commands that let you define a partitioning key 
 
(partitioning a table after the fact), attach/detach an existing table as a 
partition of a partitioned table,* CREATE PARTITION to create a new partition on a partitioned table.

Above commands are merely transformed into ALTER TABLE subcommands that arrange 
partitioned table and partitions into inheritance hierarchy, but with extra 
information, that is, allowed values for the partition in a new anyarray column 
called 'pg_inherits.values'. A special case of ATExecAddInherit() namely 
ATExecAttachPartitionI(), as part of its processing, also adds partition 
constraints in the form of appropriate CHECK constraints. So, a few of the 
manual steps are automated and additional (IMHO non-opaque) metadata (namely 
partition boundaries/list values) is added.

Additionally, defining a partitioning key (PARTITION BY) creates a pg_partition 
entry that specifies for a partitioned table the following - partition kind 
(range/list),  an opclass for the key value comparison  and a key 'expression' 
(say, "colname % 10").

A few key things I can think of as needing improvement would be  (perhaps just 
reiterating a review of the patch):
* partition pruning would still depend on constraint exclusion using the CHECK 
constraints (same old)* there is no tuple-routing at all (same can be said of partition pruning 
above)* partition pruning or tuple-routing would require a scan over pg_inherits 
(perhaps inefficient)* partitioning key is an expression which might not be a good idea in early 
stages of the implementation (might be better off with just the attnum of the 
column to partition on?)* there is no DROP PARTITION (in fact, it is suggested not to go CREATE/DROP 
PARTITION route at all) -> ALTER TABLE ... ADD/DROP PARTITION?

Some other important ones:* dependency handling related oversights* constraint propagation related oversights

And then some of the oddities of behaviour that I am seeing while trying out 
things that the patch does. Please feel free to suggest those that I am not 
seeing. I am sure these improvements need more than just tablecmds.c hacking 
which is what the current patch mostly does.

The first two points could use separate follow-on patches as I feel they need 
extensive changes unless I am missing something. I will try to post possible 
solutions to these issues provided metadata in current form is OK to proceed.

> In my view, the main problem we should be trying to solve
> here is "avoid relying on constraint exclusion".  In other words, the
> syntax for adding a partition should put some metadata into the system
> catalogs that lets us do partitioning pruning very very quickly,
> without theorem-proving.  For example, for list or range partitioning,
> a list of partition bounds would be just right: you could
> binary-search it.  The same metadata should also be suitable for
> routing inserts to the proper partition, and handling partition motion
> when a tuple is updated.
>
> Now there's other stuff we might very well want to do, but I think
> making partition pruning and tuple routing fast would be a pretty big
> win by itself.
>

Those are definitely the goals worth striving for.

Thanks for your time.

Regards,
Amit