Re: Partitioning syntax

On Tue, 2010-07-06 at 13:49 -0400, Robert Haas wrote:

> 1. It seems to me that the proposed design for pg_partition is poorly
> thought out.  In particular, I don't see how this would work if we
> wanted to partition on multiple keys, which is a feature supported by
> both Oracle and MySQL. It would also be nice to give at least some
> thought to how we might handle partitioning by list with
> subpartitioning by range or hash, or range partitioning with
> subpartitioning by hash.  We certainly don't need to do
> subpartitioning in the first version of the patch, but I think we
> should have a plan.

Or at least a way to store that information if/when it exists later.

> 2. I am still of the view that the first version of this patch should
> correctly handle routing of INSERT and COPY data to the correct
> partition.  But at a very minimum we need to have a plan for how we're
> going to implement that in a follow-on patch.  I think the way to do
> this is to binary search a sorted array of partition keys (perhaps
> upper bounds for range partitioning, and exact values for list
> partitioning).  When you find the correct key, then you find the index
> of that key and look up that same index in a separate array of table
> OIDs and insert there.  While it's possible to construct such a
> structure from the proposed catalog structure, it requires an index
> scan.  I'm wondering if it might be better to abandon the idea of
> storing the partition values in pg_inherits and instead put
> preconstructed arrays directly into pg_partition.  That way, with a
> single row fetch, you can get all the data you need.  I'm not sure
> this is better, though - other opinions?

Agreed that it is really important. The heart of partitioning is the
metadata that will allow us to do insert routing as well as nested joins
using dynamic routing. We *must* plan for that so that the command
syntax and catalog storage delivers what is required. This patch must
not be just about syntax. The required usage drives the syntax, not the
other way around.

> 3. For a first version of this patch, I would suggest that we only
> allow partitioning by base columns, rather than expressions.  When
> someone goes to do a bulk load of data into the table, and we want to
> do automatic tuple routing, we're going to have to evaluate the
> partitioning expression(s) for every row.  I'm just guessing here, but
> I bet it's a lot cheaper to fetch an attribute by attnum than to
> evaluate an arbitrary expression.  So even if we add partitioning by
> expression later, I don't think that the work to make a special case
> for base columns will be wasted.

Agree that part should come out for now and resubmit as a later patch.
Lets keep it simple in the first version.

> 5. The use of the term "partition" is not very consistent.  For
> example, we use CREATE PARTITION to create a partition, but we use
> DROP TABLE to get rid of it (there is no DROP PARTITION).  I think
> that the right syntax to use here is ALTER TABLE ... ADD/DROP
> PARTITION; both Oracle and MySQL do it that way. And meanwhile
> OCLASS_PARTITION means "the partitioning information associated with
> the parent table", not "a partition of a parent table".

Definitely do not want CREATE PARTITION. ALTER TABLE is the best place.

> 6. There's some kind of magic in here associated with indexes on the
> parent table - it seems that matching indexes or primary keys are
> automatically created on each child table.  But there's no provision
> for keeping them in sync.  If I create a partitioned table with a
> primary key, the key is inherited by all its current children.  If I
> then drop the primary key, it disappears from the parent but it still
> exists on the children.  Any new children created afterwards don't
> have it, however.  I'm not sure whether indices should propagate from
> parent to child or not, but propagating whatever exists at the moment
> of creation and then forgetting about it doesn't seem right.

IMHO it should be optional as to whether all partitions have identical
indexing. It is an important aspect of the design that an historical
table may have different indexes on different parts of the table, since
different users/use cases exist for access to that data. No problem if
some people want that though.

> 7. I'm not convinced that it's a good idea to treat ALTER TABLE parent
> ATTACH/DETACH PARTITION child as basically a synonym for ALTER TABLE
> child [NO] INHERIT parent, but even if it is the current
> implementation seems way too permissive (it also lacks comments and
> adequate documentation).  You can, for example, use ATTACH PARTITION
> to add a new child and then NO INHERIT to detach it again; or you can
> use INHERIT to attach a child even when the parent is partitioned.  It
> does however catch the case of trying to use ATTACH PARTITION to
> attach a child to an unpartitioned parent.

Agreed. If all we are doing is adding synonyms for existing feature then
its not good enough. We need a new syntax that does not need to be
backwards compatible, allowing various code streamlining and more
targeting to the desired use case. Inheritance != partitioning. Similar,
maybe, but not identical. Probably also the only way we can move
forwards without breaking all the existing user code in subtle ways.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services