Re: [HACKERS] [PROPOSAL] Table Partition
> There is already a recent proposal on hackers about partition support in > PostgreSQL by Amit Langote. > You will find the thread at > http://www.postgresql.org/message-id/55d3093c.5010...@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.ba...@enterprisedb.com>; Date: Mon, Aug 31, 2015 02:43 PM To: "My Life"<life.s...@qq.com>; Copy: "pgsql-hackers"<pgsql-hackers@postgresql.org>; "tgl"<t...@sss.pgh.pa.us>; "bruce"<br...@momjian.us>; "robertmhaas"<robertmh...@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 http://www.postgresql.org/message-id/55d3093c.5010...@lab.ntt.co.jp. May be you can collaborate with the ongoing work. -- Original ------ From: "Amit Langote";<langote_amit...@lab.ntt.co.jp>; Date: Mon, Aug 31, 2015 03:14 PM To: "My Life"<life.s...@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
[HACKERS] Proposal of 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: columndata typecomment partnamenamea partition's name, this is the primary key partidoida partition's OID in pg_class intervaltexta interval partition's interval(maybe a expression) partkey1depends on partitioned table ... partkeyNdepends 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. 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.
[HACKERS] [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: columndata typecomment partnamenamea partition's name, this is the primary key partidoida partition's OID in pg_class intervaltexta interval partition's interval(maybe a expression) partkey1depends on partitioned table ... partkeyNdepends 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.
