Re: Proposal of Table Partition

<div dir="ltr">There is already a recent proposal on hackers about partition support in PostgreSQL by Amit Langote. You
willfind the thread at <a
href="http://www.postgresql.org/message-id/55D3093C.5010800@lab.ntt.co.jp">http://www.postgresql.org/message-id/55D3093C.5010800@lab.ntt.co.jp</a>.
Maybe you can collaborate with the ongoing work.<br /></div><div class="gmail_extra"><br /><div class="gmail_quote">On
Sun,Aug 30, 2015 at 4:28 PM, My Life <span dir="ltr"><<a href="mailto:life.show@qq.com"
target="_blank">life.show@qq.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex">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
tothe PostgreSQL baseline, then I will post the code to the community.<br />(note: my english is not very good.)<br
/><br/>Table Partition Design<br />=====================<br />In this design, partitions are normal tables in
inheritancehierarchies, with the same table structure with the partitioned table.<br /><br />In pg_class we have an
additionalrelpartition field which has following values:<br />'s'        /* single regular table */<br />'r'        /*
partitionedtable by range */<br />'l'        /* partitioned table by list */<br />'h'        /* partitioned table by
hash*/<br />'c'        /* child partition table */<br /><br />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'sname is pg_partition_2586 (2586 is the partitioned table's OID in pg_class).<br />a range or interval
partitioncatalog's structure is as follows:<br />column            data type            comment<br />partname       
name               a partition's name, this is the primary key<br />partid            oid                    a
partition'sOID in pg_class<br />interval        text                a interval partition's interval(maybe a
expression)<br/>partkey1        depends on partitioned table<br />...<br />partkeyN        depends on partitioned
table<br/>partkey1, ..., partkeyN is a partition's upper bound.<br />Finally, make a unique constraint on partkey1,
...,partkeyN.<br />Every time we create a new partition, we insert a new tuple into this partition catalog.<br />Every
timewe drop an old partition, we delete the related tuple in this partition catalog.<br /><br />For a partitioned
table'sCREATE action, we should transform the action into the CREATE action of partitioned table and partitions, and
theINSERT action into the partition catalog.<br /><br />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(assumeit is pg_partition_2586) to find the partition.<br />and a ExecGetPartitionResultRel method, which can
returnthe partition's ResultRelInfo to execute INSERT action.<br /><br />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
intoa list of partitions according to the filter and conditions. and it can expand partitioned table JOIN node into a
listof partitions JOIN node wisely.<br /><br />For pg_dump backup action, we should dump the partition catalog, and
relpartitionfield in pg_class.<br /><br />so these are the main points of the design, and I can show any detail you
wonderedlater.<br /></blockquote></div><br /><br clear="all" /><br />-- <br /><div class="gmail_signature"><div
dir="ltr">BestWishes,<br />Ashutosh Bapat<br />EnterpriseDB Corporation<br />The Postgres Database Company<br
/></div></div></div>