Re: Declarative partitioning - Mailing list pgsql-hackers
| From | Simon Riggs |
|---|---|
| Subject | Re: Declarative partitioning |
| Date | |
| Msg-id | CANP8+jL3ApRqNRa0M5f9pfth91NrSAtvVr+sEQCdNZGr+1tafA@mail.gmail.com Whole thread Raw |
| In response to | Declarative partitioning (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Responses |
Re: Declarative partitioning
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
| List | pgsql-hackers |
On 18 August 2015 at 11:30, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
src/backend/access/nbtree/README
--
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/READMEsrc/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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: