Re: Declarative partitioning

On 18 August 2015 at 11:30, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> I would like propose $SUBJECT for this development cycle. Attached is a
> WIP patch that implements most if not all of what's described below. Some
> yet unaddressed parts are mentioned below, too. I'll add this to the
> CF-SEP.
>

Thanks for working on this. It's a great start.

> 3. Multi-level partitioning
>
> CREATE TABLE table_name
> PARTITION OF partitioned_table_name
> FOR VALUES values_spec
> PARTITION BY {RANGE|LIST} ON (columns_list)
>
> This variant implements a form of so called composite or sub-partitioning
> with arbitrarily deep partitioning structure. A table created using this
> form has both the relkind RELKIND_PARTITIONED_REL and
> pg_class.relispartition set to true.
>

Multi-level partitioning is probably going to complicate things beyond
sanity.

One RELKIND_PARTITIONED_REL with lots of partitions sounds best to me. We
can still have N dimensions of partitioning (or partitioning and
subpartitioning, if you prefer that term)

The patch does not yet implement any planner changes for partitioned
> tables, although I'm working on the same and post updates as soon as
> possible. That means, it is not possible to run SELECT/UPDATE/DELETE
> queries on partitioned tables without getting:
>
> postgres=# SELECT * FROM persons;
> ERROR: could not open file "base/13244/106975": No such file or directory
>
> Given that there would be more direct ways of performing partition pruning
> decisions with the proposed, it would be nice to utilize them.
> Specifically, I would like to avoid having to rely on constraint exclusion
> for partition pruning whereby subquery_planner() builds append_rel_list
> and the later steps exclude useless partitions.
>

This is really the heart of this patch/design. You can work for months on
all the rest of this, but you will live or die by how the optimization
works because that is the thing we really need to work well. Previous
attempts ignored this aspect and didn't get committed. It's hard, perhaps
even scary, but its critical. It's the 80/20 rule in reverse - 20% of the
code is 80% of the difficulty.

I suggest you write a partition query test script .sql and work towards
making this work. Not exhaustive and weird tests, but 5-10 key queries that
need to be optimized precisely and quickly. I'm sure that's been done
before.

Will include the following once we start reaching consensus on main parts
> of the proposed design/implementation:
>
> * New regression tests
> * Documentation updates
> * pg_dump, psql, etc.
>
> For reference, some immediately previous discussions:
>
> * On partitioning *
>
> http://www.postgresql.org/message-id/20140829155607.GF7705@eldon.alvh.no-ip.org
>
> * Partitioning WIP patch *
> http://www.postgresql.org/message-id/54EC32B6.9070605@lab.ntt.co.jp

If you want to achieve consensus, please write either docs or README files
that explain how this works.

It took me a few seconds to notice deviations from Alvaro's original post.
I shouldn't have to read a full thread to see what the conclusions were,
you need to record them coherently.

Some great examples of such things are
src/backend/optimizer/README
src/backend/access/nbtree/README
Please imagine how far such code would have got without them, then look at
the code comments on the top of each of the functions in that area for
examples of the clarity of design this needs.

Comments welcome!
>

Yes, comments in code are indeed welcome, as well as the README/docs.

I couldn't see why you invented a new form of Alter Table recursion.

We will need to support multi-row batched COPY.

I'm pleased to see this patch and will stay with it to completion, perhaps
others also. We have 3 more CFs in this release, Nov, Jan, Mar - so this
has a great chance of making it into 9.6. The current patch implements a
bunch of stuff, but its hard to say what, how or why it does it and without
the planner stuff its all moot. My recommendation is we say "Returned with
Feedback" on this now, looking forward to next patch.

If you submit another patch before Nov, I will review it without waiting
for Nov 1.

There will be much discussion on syntax, but that is not the key point. DDL
Support routines are usually pretty straightforward too, so that can be
left for now.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services