Re: [HACKERS] Proposal of Table Partition
Hello, On 2015-08-30 PM 07:58, My Life wrote: > 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. As Ashutosh suggested, could you please take a look at my proposal at the link he posted? 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
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. On Sun, Aug 30, 2015 at 4:28 PM, My Life wrote: > 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. > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
[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.
