[PROPOSAL] Table Partition
Hi, everyone! I'd like to propose a postgres partition implementation. First, I would show the design to everyone, and talk about it. If we think the design is not very bad, and can be commit to the PostgreSQL baseline, then I will post the code to the community.
(note: my english is not very good.)
Table Partition Design
=====================
In this design, partitions are normal tables in inheritance hierarchies, with the same table structure with the partitioned table.
In pg_class we have an additional relpartition field which has following values:
's' /* single regular table */
'r' /* partitioned table by range */
'l' /* partitioned table by list */
'h' /* partitioned table by hash */
'c' /* child partition table */
Add a new system schema named 'pg_partition', just like 'pg_toast', we can create the partition catalog table to store the partition entries. let's assume the partition catalog's name is pg_partition_2586 (2586 is the partitioned table's OID in pg_class).
a range or interval partition catalog's structure is as follows:
column data type comment
partname name a partition's name, this is the primary key
partid oid a partition's OID in pg_class
interval text a interval partition's interval(maybe a expression)
partkey1 depends on partitioned table
...
partkeyN depends on partitioned table
partkey1, ..., partkeyN is a partition's upper bound.
Finally, make a unique constraint on partkey1, ..., partkeyN.
Every time we create a new partition, we insert a new tuple into this partition catalog.
Every time we drop an old partition, we delete the related tuple in this partition catalog.
For a partitioned table's CREATE action, we should transform the action into the CREATE action of partitioned table and partitions, and the INSERT action into the partition catalog.
For INSERT action, we implement a RelationGetTuplePartid method, which can find the partition the tuple belongs to. It will do an index scan on the partition catalog table(assume it is pg_partition_2586) to find the partition.
and a ExecGetPartitionResultRel method, which can return the partition's ResultRelInfo to execute INSERT action.
For partitioned table's scan action, and JOIN action, we implemented a plan node named 'PartitionExpand'. the plan node can expand the partitioned table scan node into a list of partitions according to the filter and conditions. and it can expand partitioned table JOIN node into a list of partitions JOIN node wisely.
We implemented a DynamicPrunePartition method, which can expand the partitioned table's scan node into a list of partition's scan node.
We implemented a DynamicPrunePartitionJoin method, which can expand the partitioned table's JOIN node into a list of partition's JOIN node.
These expand action happend in ExecInitPartitionExpand function, when initialize the executor. and all these action implemented based on the partition catalog.
For UPDATE and DELETE action, we just set real partition as the ResultRelInfo, when ExecPartitionExpand is running.
For pg_dump backup action, we should dump the partition catalog, and relpartition field in pg_class.
so these are the main points of the design, and I can show any detail you wondered later.
Hello,
On 2015-08-30 PM 10:42, My Life wrote:
For partitioned table's scan action, and JOIN action, we implemented
a plan node named 'PartitionExpand'. the plan node can expand the
partitioned table scan node into a list of partitions according to
the filter and conditions. and it can expand partitioned table JOIN
node into a list of partitions JOIN node wisely.
We implemented a DynamicPrunePartition method, which can expand the
partitioned table's scan node into a list of partition's scan node.
We implemented a DynamicPrunePartitionJoin method, which can expand
the partitioned table's JOIN node into a list of partition's JOIN node.
These expand action happend in ExecInitPartitionExpand function, when
initialize the executor. and all these action implemented based on the
partition catalog.
In your design, can index scan be used for individual partition? If yes,
can you share how it is handled?
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
There is already a recent proposal on hackers about partition support in PostgreSQL by Amit Langote.
You will find the thread at /messages/by-id/55D3093C.5010800@lab.ntt.co.jp.
Actually, I have seen this design before, and it was not just a design, it has been implemented. I agree with it although I still have some reservations, because I think it is a little more complicated.
1. you store all partition info into 2 system catalogs: pg_partitioned_rel, pg_partition. it will be less efficient to access and maintain the partition info, include scan, add, delete, modify action, maybe concurrency. And the data volumn will get larger and larger.
2. the column 'anyarray partrangebounds' in pg_partition is not very accurate, and we cannot use the index access operators associated with some data type.
In your design, can index scan be used for individual partition? If yes,
can you share how it is handled?
of course, index scan can be used for individual partition.
because we make a unique constraint on partkey1, ..., partkeyN on pg_partition.pg_partition_2586.
the unique constraint is really a unique index.
if a index scan's index condition involved the partkey, we can use this unique index to choose the matched partitions in a specified order. and expand the partitioned table's index scan node into a list of partition's index scan node. In this way, the 'PartitionExpand' plan node likes the 'Append' plan node, but has some difference.
------------------ Original ------------------
From: "Ashutosh Bapat";<ashutosh.bapat@enterprisedb.com>;
Date: Mon, Aug 31, 2015 02:43 PM
To: "My Life"<life.show@qq.com>;
Copy: "pgsql-hackers"<pgsql-hackers@postgresql.org>; "tgl"<tgl@sss.pgh.pa.us>; "bruce"<bruce@momjian.us>; "robertmhaas"<robertmhaas@gmail.com>;
Subject: Re: [HACKERS] Proposal of Table Partition
There is already a recent proposal on hackers about partition support in PostgreSQL by Amit Langote. You will find the thread at /messages/by-id/55D3093C.5010800@lab.ntt.co.jp. May be you can collaborate with the ongoing work.
------------------ Original ------------------
From: "Amit Langote";<Langote_Amit_f8@lab.ntt.co.jp>;
Date: Mon, Aug 31, 2015 03:14 PM
To: "My Life"<life.show@qq.com>; "pgsql-hackers"<pgsql-hackers@postgresql.org>;
Subject: Re: [HACKERS] [PROPOSAL] Table Partition
Hello,
On 2015-08-30 PM 10:42, My Life wrote:
For partitioned table's scan action, and JOIN action, we implemented
a plan node named 'PartitionExpand'. the plan node can expand the
partitioned table scan node into a list of partitions according to
the filter and conditions. and it can expand partitioned table JOIN
node into a list of partitions JOIN node wisely.
We implemented a DynamicPrunePartition method, which can expand the
partitioned table's scan node into a list of partition's scan node.
We implemented a DynamicPrunePartitionJoin method, which can expand
the partitioned table's JOIN node into a list of partition's JOIN node.
These expand action happend in ExecInitPartitionExpand function, when
initialize the executor. and all these action implemented based on the
partition catalog.
In your design, can index scan be used for individual partition? If yes,
can you share how it is handled?
Thanks,
Amit