PostgreSQL技术内幕17：PG分区表
文章目录
- 0.简介
- 1.概念介绍
- 2.分区表技术产生的背景
- 3.分区类型及使用方式
- 4.实现原理
- 4.1 分区表创建
- 4.2 分区表查询
- 4.3 分区表写入
- 4.4 分区表删除
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+ students
Table "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_fail
Table "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+ students
Table "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 分区表删除
分区表的删除即为先删除其分区，然后整体删除。