PostgreSQL技术内幕17：PG分区表

0.简介

本文主要介绍PG中分区表的概念，产生分区表技术的原因，使用方式和其内部实现原理，旨在能对PG分区表技术有一个系统的说明。

1.概念介绍

分区表是数据库用于管理大量数据的一种技术，它允许将一个大表分割成多个小表，这些小表在物理上是独立的，但在逻辑上作为一个整体被查询和更新。分区表的主要优势在于提高查询性能，特别是当查询集中在少数几个分区时。此外，分区表还可以简化数据的批量删除和加载，以及将不常用的数据迁移到成本较低的存储介质上实现冷热分离。

1）主表/父表/Master Table：该表是创建子表的模板。它是一个正常的普通表，但正常情况下它并不储存任何数据。
2）子表/分区表/Child Table/Partition Table：这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表

2.分区表技术产生的背景

在使用数据库过程中，随着时间的推移，每张表数据量会不断增加，造成查询速度越来越慢，在分区表之前有很多查询的技术去优化它，比如添加特殊的索引，将磁盘分区（把日志文件放到单独的磁盘分区），调整参数等等。这些优化技术都能对查询性能做出或多或少的提升，但其并没有对于表特点以及局部性的原理进行合理应用，因为对于很多应用来说，许多历史数据对于查询可能并没有太多用处，或者是某一列是特定值时是更为关系的数据，如果能够将不常用数据进行隐藏，就能大大提高查询速度，分区表就是为了解决这个问题而产生的。比如可以按照时间作为分区键进行分区将新老数据分离。

3.分区类型及使用方式

PG 10以后支持三种分区，以下都使用主流的使用方式声明式分区（还有表继承）进行说明：
1）范围（Range）分区

CREATE TABLE students (grade INTEGER) PARTITION BY RANGE(grade);
CREATE TABLE stu_fail PARTITION OF students FOR VALUES FROM (MINVALUE) TO (60);
CREATE TABLE stu_pass PARTITION OF students FOR VALUES FROM (60) TO (MAXVALUE);\d+  studentsTable "public.students"Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------grade  | integer |           |          |         | plain   |              |
Partition key: RANGE (grade)
Partitions: stu_fail FOR VALUES FROM (MINVALUE) TO (60),stu_pass FOR VALUES FROM (60) TO (MAXVALUE)\d+ stu_failTable "public.stu_fail"Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------grade  | integer |           |          |         | plain   |              |
Partition of: students FOR VALUES FROM (MINVALUE) TO (60)
Partition constraint: ((grade IS NOT NULL) AND (grade < 60))

可以看出，其中最大值是小于关系，不是小于等于关系。

2）列表（List）分区
列表分区明确指定根据某字段的某个具体值进行分区，默认分区（可选值）保存不属于任何指定分区的列表值。

CREATE TABLE students (status character varying(30)) PARTITION BY LIST(status);
CREATE TABLE stu_active PARTITION OF students FOR VALUES IN ('ACTIVE');
CREATE TABLE stu_exp PARTITION OF students FOR VALUES IN ('EXPIRED');
CREATE TABLE stu_others PARTITION OF students DEFAULT;\d+  studentsTable "public.students"Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------status | character varying(30) |           |          |         | extended |              |
Partition key: LIST (status)
Partitions: stu_active FOR VALUES IN ('ACTIVE'),stu_exp FOR VALUES IN ('EXPIRED'),stu_others DEFAULT\d+  stu_others;Table "public.stu_others"Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------status | character varying(30) |           |          |         | extended |              |
Partition of: students DEFAULT
Partition constraint: (NOT ((status IS NOT NULL) AND ((status)::text = ANY (ARRAY['ACTIVE'::character varying(30), 'EXPIRED'::character varying(30)]))))

3）哈希（Hash）分区
通过对每个分区使用取模和余数来创建hash分区，modulus指定了对N取模，而remainder指定了除完后的余数。

CREATE TABLE students (id INTEGER) PARTITION BY HASH(id);
CREATE TABLE stu_part1 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE stu_part2 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE stu_part3 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 2);\d+ students;Table "public.students"Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------id     | integer |           |          |         | plain   |              |
Partition key: HASH (id)
Partitions: stu_part1 FOR VALUES WITH (modulus 3, remainder 0),stu_part2 FOR VALUES WITH (modulus 3, remainder 1),stu_part3 FOR VALUES WITH (modulus 3, remainder 2)\d+ stu_part1;Table "public.stu_part1"Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------id     | integer |           |          |         | plain   |              |
Partition of: students FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition('16439'::oid, 3, 0, id)

PG分区还支持创建子分区：LIST-LIST，LIST-RANGE，LIST-HASH，RANGE-RANGE，RANGE-LIST，RANGE-HASH，HASH-HASH，HASH-LIST和HASH-RANGE；以及和普通表之间互相转换，DETACH PARTITION可以将分区表转换为普通表，而attach partition可以将普通表附加到分区表上。

4.实现原理

4.1 分区表创建

分区表创建相对简单，对PG来说实际是一张逻辑表对应多张物理表，下面简单看创建时其分区表相关的调用流程。

--> transformPartitionBound  --> RelationGetPartitionKey--> get_partition_strategy--> transformPartitionBoundValue--> transformPartitionRangeBounds--> validateInfiniteBounds--> check_new_partition_bound--> StorePartitionBound // Update pg_class tuple of rel to store the partition bound and set relispartition to true--> StoreCatalogInheritance // 向系统表pg_inherits插入信息// 处理stmt->partspec--> transformPartitionSpec--> ComputePartitionAttrs--> StorePartitionKey // 向pg_partitioned_table中插入分区键等信息

4.2 分区表查询

分区表查询是要根据条件查询一定数量的子表然后进行返回，其主要分为三步：
1）识别分区表并找到所有的分区子表

/** expand_inherited_tables*    Expand each rangetable entry that represents an inheritance set*    into an "append relation".  At the conclusion of this process,*    the "inh" flag is set in all and only those RTEs that are append*    relation parents.*/
void
expand_inherited_tables(PlannerInfo *root)
{Index    nrtes;Index    rti;ListCell   *rl;/** expand_inherited_rtentry may add RTEs to parse->rtable. The function is* expected to recursively handle any RTEs that it creates with inh=true.* So just scan as far as the original end of the rtable list.*/nrtes = list_length(root->parse->rtable);rl = list_head(root->parse->rtable);for (rti = 1; rti <= nrtes; rti++){RangeTblEntry *rte = (RangeTblEntry *) lfirst(rl);expand_inherited_rtentry(root, rte, rti);rl = lnext(rl);}
}

2）根据约束条件识别需要查询的分区,也就是分区裁剪，只读取需要的分区;

prune_append_rel_partitions*    Process rel's baserestrictinfo and make use of quals which can be*    evaluated during query planning in order to determine the minimum set*    of partitions which must be scanned to satisfy these quals.  Returns*    the matching partitions in the form of a Relids set containing the*    partitions' RT indexes.** Callers must ensure that 'rel' is a partitioned table.*/
Relids
prune_append_rel_partitions(RelOptInfo *rel)
{Relids    result;List     *clauses = rel->baserestrictinfo;List     *pruning_steps;GeneratePruningStepsContext gcontext;PartitionPruneContext context;Bitmapset  *partindexes;int      i;Assert(clauses != NIL);Assert(rel->part_scheme != NULL);/* If there are no partitions, return the empty set */if (rel->nparts == 0)return NULL;/** Process clauses to extract pruning steps that are usable at plan time.* If the clauses are found to be contradictory, we can return the empty* set.*/gen_partprune_steps(rel, clauses, PARTTARGET_PLANNER,&gcontext);if (gcontext.contradictory)return NULL;pruning_steps = gcontext.steps;/* Set up PartitionPruneContext */context.strategy = rel->part_scheme->strategy;context.partnatts = rel->part_scheme->partnatts;context.nparts = rel->nparts;context.boundinfo = rel->boundinfo;context.partcollation = rel->part_scheme->partcollation;context.partsupfunc = rel->part_scheme->partsupfunc;context.stepcmpfuncs = (FmgrInfo *) palloc0(sizeof(FmgrInfo) *context.partnatts *list_length(pruning_steps));context.ppccontext = CurrentMemoryContext;/* These are not valid when being called from the planner */context.partrel = NULL;context.planstate = NULL;context.exprstates = NULL;/* Actual pruning happens here. */partindexes = get_matching_partitions(&context, pruning_steps);/* Add selected partitions' RT indexes to result. */i = -1;result = NULL;while ((i = bms_next_member(partindexes, i)) >= 0)result = bms_add_member(result, rel->part_rels[i]->relid);return result;
}

3）对结果集执行APPEND,作为最终结果输出，这和其他表append操作一致，使用ExecInitAppend和ExecAppend函数。

/* ----------------------------------------------------------------*     ExecAppend**    Handles iteration over multiple subplans.* ----------------------------------------------------------------*/
static TupleTableSlot *
ExecAppend(PlanState *pstate)
{AppendState *node = castNode(AppendState, pstate);if (node->as_whichplan < 0){/** If no subplan has been chosen, we must choose one before* proceeding.*/if (node->as_whichplan == INVALID_SUBPLAN_INDEX &&!node->choose_next_subplan(node))return ExecClearTuple(node->ps.ps_ResultTupleSlot);/* Nothing to do if there are no matching subplans */else if (node->as_whichplan == NO_MATCHING_SUBPLANS)return ExecClearTuple(node->ps.ps_ResultTupleSlot);}for (;;){PlanState  *subnode;TupleTableSlot *result;CHECK_FOR_INTERRUPTS();/** figure out which subplan we are currently processing*/Assert(node->as_whichplan >= 0 && node->as_whichplan < node->as_nplans);subnode = node->appendplans[node->as_whichplan];/** get a tuple from the subplan*/result = ExecProcNode(subnode);if (!TupIsNull(result)){/** If the subplan gave us something then return it as-is. We do* NOT make use of the result slot that was set up in* ExecInitAppend; there's no need for it.*/return result;}/* choose new subplan; if none, we're done */if (!node->choose_next_subplan(node))return ExecClearTuple(node->ps.ps_ResultTupleSlot);}
}

4.3 分区表写入

分区表写入分为两个阶段，一个是查找到要写入的分区，然后就是正常去做写入，下面来看查找分区的函数。

/** ExecPrepareTupleRouting --- prepare for routing one tuple** Determine the partition in which the tuple in slot is to be inserted,* and modify mtstate and estate to prepare for it.** Caller must revert the estate changes after executing the insertion!* In mtstate, transition capture changes may also need to be reverted.** Returns a slot holding the tuple of the partition rowtype.*/
static TupleTableSlot *
ExecPrepareTupleRouting(ModifyTableState *mtstate,EState *estate,PartitionTupleRouting *proute,ResultRelInfo *targetRelInfo,TupleTableSlot *slot)
{ModifyTable *node;int      partidx;ResultRelInfo *partrel;HeapTuple  tuple;/** Determine the target partition.  If ExecFindPartition does not find a* partition after all, it doesn't return here; otherwise, the returned* value is to be used as an index into the arrays for the ResultRelInfo* and TupleConversionMap for the partition.*/partidx = ExecFindPartition(targetRelInfo,proute->partition_dispatch_info,slot,estate);Assert(partidx >= 0 && partidx < proute->num_partitions);/** Get the ResultRelInfo corresponding to the selected partition; if not* yet there, initialize it.*/partrel = proute->partitions[partidx];if (partrel == NULL)partrel = ExecInitPartitionInfo(mtstate, targetRelInfo,proute, estate,partidx);/** Check whether the partition is routable if we didn't yet** Note: an UPDATE of a partition key invokes an INSERT that moves the* tuple to a new partition.  This check would be applied to a subplan* partition of such an UPDATE that is chosen as the partition to route* the tuple to.  The reason we do this check here rather than in* ExecSetupPartitionTupleRouting is to avoid aborting such an UPDATE* unnecessarily due to non-routable subplan partitions that may not be* chosen for update tuple movement after all.*/if (!partrel->ri_PartitionReadyForRouting){/* Verify the partition is a valid target for INSERT. */CheckValidResultRel(partrel, CMD_INSERT);/* Set up information needed for routing tuples to the partition. */ExecInitRoutingInfo(mtstate, estate, proute, partrel, partidx);}/** Make it look like we are inserting into the partition.*/estate->es_result_relation_info = partrel;/* Get the heap tuple out of the given slot. */tuple = ExecMaterializeSlot(slot);/** If we're capturing transition tuples, we might need to convert from the* partition rowtype to parent rowtype.*/if (mtstate->mt_transition_capture != NULL){if (partrel->ri_TrigDesc &&partrel->ri_TrigDesc->trig_insert_before_row){/** If there are any BEFORE triggers on the partition, we'll have* to be ready to convert their result back to tuplestore format.*/mtstate->mt_transition_capture->tcs_original_insert_tuple = NULL;mtstate->mt_transition_capture->tcs_map =TupConvMapForLeaf(proute, targetRelInfo, partidx);}else{/** Otherwise, just remember the original unconverted tuple, to* avoid a needless round trip conversion.*/mtstate->mt_transition_capture->tcs_original_insert_tuple = tuple;mtstate->mt_transition_capture->tcs_map = NULL;}}if (mtstate->mt_oc_transition_capture != NULL){mtstate->mt_oc_transition_capture->tcs_map =TupConvMapForLeaf(proute, targetRelInfo, partidx);}/** Convert the tuple, if necessary.*/ConvertPartitionTupleSlot(proute->parent_child_tupconv_maps[partidx],tuple,proute->partition_tuple_slot,&slot);/* Initialize information needed to handle ON CONFLICT DO UPDATE. */Assert(mtstate != NULL);node = (ModifyTable *) mtstate->ps.plan;if (node->onConflictAction == ONCONFLICT_UPDATE){Assert(mtstate->mt_existing != NULL);ExecSetSlotDescriptor(mtstate->mt_existing,RelationGetDescr(partrel->ri_RelationDesc));Assert(mtstate->mt_conflproj != NULL);ExecSetSlotDescriptor(mtstate->mt_conflproj,partrel->ri_onConflict->oc_ProjTupdesc);}return slot;
}

4.4 分区表删除

分区表的删除即为先删除其分区，然后整体删除。