PostgreSQL技能内幕17：PG分区表

0.简介

本文告急先容PG中分区表的概念，产生分区表技能的缘故起因，利用方式和其内部实现原理，旨在能对PG分区表技能有一个体系的阐明。
1.概念先容

分区表是数据库用于管理大量数据的一种技能，它答应将一个大表分割成多个小表，这些小表在物理上是独立的，但在逻辑上作为一个团体被查询和更新。分区表的告急上风在于进步查询性能，特殊是当查询会集在少数几个分区时。别的，分区表还可以简化数据的批量删除和加载，以及将不常用的数据迁徙到本钱较低的存储介质上实现冷热分离。

1）主表/父表/Master Table：该表是创建子表的模板。它是一个正常的平凡表，但正常环境下它并不储存任何数据。
2）子表/分区表/Child Table/Partition Table：这些表继承并属于一个主表。子表中存储全部的数据。主表与分区表属于一对多的关系，也就是说，一个主表包罗多个分区表，而一个分区表只附属于一个主表
2.分区表技能产生的配景

在利用数据库过程中，随着时间的推移，每张表数据量会不停增长，造成查询速率越来越慢，在分区表之前有许多查询的技能去优化它，比如添加特殊的索引，将磁盘分区（把日记文件放到单独的磁盘分区），调解参数等等。这些优化技能都能对查询性能做出或多或少的提拔，但其并没有对于表特点以及局部性的原理举行公道应用，由于对于许多应用来说，许多汗青数据对于查询大概并没有太多用处，大概是某一列是特定值时是更为关系的数据，如果可以大概将不常用数据举行隐蔽，就能大大进步查询速率，分区表就是为相识决这个标题而产生的。比如可以按照时间作为分区键举行分区将新老数据分离。
3.分区范例及利用方式

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

1. CREATE TABLE students (grade INTEGER) PARTITION BY RANGE(grade);
2. CREATE TABLE stu_fail PARTITION OF students FOR VALUES FROM (MINVALUE) TO (60);
3. CREATE TABLE stu_pass PARTITION OF students FOR VALUES FROM (60) TO (MAXVALUE);
4. \d+  students
5.                                  Table "public.students"
6. Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
7. --------+---------+-----------+----------+---------+---------+--------------+-------------
8. grade  | integer |           |          |         | plain   |              |
9. Partition key: RANGE (grade)
10. Partitions: stu_fail FOR VALUES FROM (MINVALUE) TO (60),
11.             stu_pass FOR VALUES FROM (60) TO (MAXVALUE)
13. \d+ stu_fail
14.                                  Table "public.stu_fail"
15. Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
16. --------+---------+-----------+----------+---------+---------+--------------+-------------
17. grade  | integer |           |          |         | plain   |              |
18. Partition of: students FOR VALUES FROM (MINVALUE) TO (60)
19. Partition constraint: ((grade IS NOT NULL) AND (grade < 60))

复制代码

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

复制代码

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

1. CREATE TABLE students (status character varying(30)) PARTITION BY LIST(status);
2. CREATE TABLE stu_active PARTITION OF students FOR VALUES IN ('ACTIVE');
3. CREATE TABLE stu_exp PARTITION OF students FOR VALUES IN ('EXPIRED');
4. CREATE TABLE stu_others PARTITION OF students DEFAULT;
6. \d+  students
7.                                          Table "public.students"
8. Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
9. --------+-----------------------+-----------+----------+---------+----------+--------------+-------------
10. status | character varying(30) |           |          |         | extended |              |
11. Partition key: LIST (status)
12. Partitions: stu_active FOR VALUES IN ('ACTIVE'),
13.             stu_exp FOR VALUES IN ('EXPIRED'),
14.             stu_others DEFAULT
16. \d+  stu_others;
17.                                         Table "public.stu_others"
18. Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
19. --------+-----------------------+-----------+----------+---------+----------+--------------+-------------
20. status | character varying(30) |           |          |         | extended |              |
21. Partition of: students DEFAULT
22. 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指定了除完后的余数。

1. CREATE TABLE students (id INTEGER) PARTITION BY HASH(id);
2. CREATE TABLE stu_part1 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 0);
3. CREATE TABLE stu_part2 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 1);
4. CREATE TABLE stu_part3 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 2);
6. \d+ students;
7.                                  Table "public.students"
8. Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
9. --------+---------+-----------+----------+---------+---------+--------------+-------------
10. id     | integer |           |          |         | plain   |              |
11. Partition key: HASH (id)
12. Partitions: stu_part1 FOR VALUES WITH (modulus 3, remainder 0),
13.             stu_part2 FOR VALUES WITH (modulus 3, remainder 1),
14.             stu_part3 FOR VALUES WITH (modulus 3, remainder 2)
16. \d+ stu_part1;
17.                                  Table "public.stu_part1"
18. Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
19. --------+---------+-----------+----------+---------+---------+--------------+-------------
20. id     | integer |           |          |         | plain   |              |
21. Partition of: students FOR VALUES WITH (modulus 3, remainder 0)
22. 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来说现实是一张逻辑表对应多张物理表，下面简朴看创建时其分区表相干的调用流程。

1. --> transformPartitionBound  
2.                     --> RelationGetPartitionKey
3.                     --> get_partition_strategy
4.                     --> transformPartitionBoundValue
5.                     --> transformPartitionRangeBounds
6.                         --> validateInfiniteBounds
7.                 --> check_new_partition_bound
8.                 --> StorePartitionBound // Update pg_class tuple of rel to store the partition bound and set relispartition to true
9.                 --> StoreCatalogInheritance // 向系统表pg_inherits插入信息
10.                 // 处理stmt->partspec
11.                 --> transformPartitionSpec
12.                 --> ComputePartitionAttrs
13.                 --> StorePartitionKey // 向pg_partitioned_table中插入分区键等信息

复制代码

4.2 分区表查询

分区表查询是要根据条件查询肯定命量的子表然后举行返回，其告急分为三步：
1）辨认分区表并找到全部的分区子表

1. /*
2. * expand_inherited_tables
3. *    Expand each rangetable entry that represents an inheritance set
4. *    into an "append relation".  At the conclusion of this process,
5. *    the "inh" flag is set in all and only those RTEs that are append
6. *    relation parents.
7. */
8. void
9. expand_inherited_tables(PlannerInfo *root)
10. {
11.   Index    nrtes;
12.   Index    rti;
13.   ListCell   *rl;
15.   /*
16.    * expand_inherited_rtentry may add RTEs to parse->rtable. The function is
17.    * expected to recursively handle any RTEs that it creates with inh=true.
18.    * So just scan as far as the original end of the rtable list.
19.    */
20.   nrtes = list_length(root->parse->rtable);
21.   rl = list_head(root->parse->rtable);
22.   for (rti = 1; rti <= nrtes; rti++)
23.   {
24.     RangeTblEntry *rte = (RangeTblEntry *) lfirst(rl);
26.     expand_inherited_rtentry(root, rte, rti);
27.     rl = lnext(rl);
28.   }
29. }

复制代码

2）根据束缚条件辨认须要查询的分区,也就是分区裁剪，只读取须要的分区;

1. prune_append_rel_partitions
2. *    Process rel's baserestrictinfo and make use of quals which can be
3. *    evaluated during query planning in order to determine the minimum set
4. *    of partitions which must be scanned to satisfy these quals.  Returns
5. *    the matching partitions in the form of a Relids set containing the
6. *    partitions' RT indexes.
7. *
8. * Callers must ensure that 'rel' is a partitioned table.
9. */
10. Relids
11. prune_append_rel_partitions(RelOptInfo *rel)
12. {
13.   Relids    result;
14.   List     *clauses = rel->baserestrictinfo;
15.   List     *pruning_steps;
16.   GeneratePruningStepsContext gcontext;
17.   PartitionPruneContext context;
18.   Bitmapset  *partindexes;
19.   int      i;
21.   Assert(clauses != NIL);
22.   Assert(rel->part_scheme != NULL);
24.   /* If there are no partitions, return the empty set */
25.   if (rel->nparts == 0)
26.     return NULL;
28.   /*
29.    * Process clauses to extract pruning steps that are usable at plan time.
30.    * If the clauses are found to be contradictory, we can return the empty
31.    * set.
32.    */
33.   gen_partprune_steps(rel, clauses, PARTTARGET_PLANNER,
34.             &gcontext);
35.   if (gcontext.contradictory)
36.     return NULL;
37.   pruning_steps = gcontext.steps;
39.   /* Set up PartitionPruneContext */
40.   context.strategy = rel->part_scheme->strategy;
41.   context.partnatts = rel->part_scheme->partnatts;
42.   context.nparts = rel->nparts;
43.   context.boundinfo = rel->boundinfo;
44.   context.partcollation = rel->part_scheme->partcollation;
45.   context.partsupfunc = rel->part_scheme->partsupfunc;
46.   context.stepcmpfuncs = (FmgrInfo *) palloc0(sizeof(FmgrInfo) *
47.                         context.partnatts *
48.                         list_length(pruning_steps));
49.   context.ppccontext = CurrentMemoryContext;
51.   /* These are not valid when being called from the planner */
52.   context.partrel = NULL;
53.   context.planstate = NULL;
54.   context.exprstates = NULL;
56.   /* Actual pruning happens here. */
57.   partindexes = get_matching_partitions(&context, pruning_steps);
59.   /* Add selected partitions' RT indexes to result. */
60.   i = -1;
61.   result = NULL;
62.   while ((i = bms_next_member(partindexes, i)) >= 0)
63.     result = bms_add_member(result, rel->part_rels[i]->relid);
65.   return result;
66. }

复制代码

3）对效果集实行APPEND,作为最闭幕果输出，这和其他表append利用同等，利用ExecInitAppend和ExecAppend函数。

1. /* ----------------------------------------------------------------
2. *     ExecAppend
3. *
4. *    Handles iteration over multiple subplans.
5. * ----------------------------------------------------------------
6. */
7. static TupleTableSlot *
8. ExecAppend(PlanState *pstate)
9. {
10.   AppendState *node = castNode(AppendState, pstate);
12.   if (node->as_whichplan < 0)
13.   {
14.     /*
15.      * If no subplan has been chosen, we must choose one before
16.      * proceeding.
17.      */
18.     if (node->as_whichplan == INVALID_SUBPLAN_INDEX &&
19.       !node->choose_next_subplan(node))
20.       return ExecClearTuple(node->ps.ps_ResultTupleSlot);
22.     /* Nothing to do if there are no matching subplans */
23.     else if (node->as_whichplan == NO_MATCHING_SUBPLANS)
24.       return ExecClearTuple(node->ps.ps_ResultTupleSlot);
25.   }
27.   for (;;)
28.   {
29.     PlanState  *subnode;
30.     TupleTableSlot *result;
32.     CHECK_FOR_INTERRUPTS();
34.     /*
35.      * figure out which subplan we are currently processing
36.      */
37.     Assert(node->as_whichplan >= 0 && node->as_whichplan < node->as_nplans);
38.     subnode = node->appendplans[node->as_whichplan];
40.     /*
41.      * get a tuple from the subplan
42.      */
43.     result = ExecProcNode(subnode);
45.     if (!TupIsNull(result))
46.     {
47.       /*
48.        * If the subplan gave us something then return it as-is. We do
49.        * NOT make use of the result slot that was set up in
50.        * ExecInitAppend; there's no need for it.
51.        */
52.       return result;
53.     }
55.     /* choose new subplan; if none, we're done */
56.     if (!node->choose_next_subplan(node))
57.       return ExecClearTuple(node->ps.ps_ResultTupleSlot);
58.   }
59. }

复制代码

4.3 分区表写入

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

1. /*
2. * ExecPrepareTupleRouting --- prepare for routing one tuple
3. *
4. * Determine the partition in which the tuple in slot is to be inserted,
5. * and modify mtstate and estate to prepare for it.
6. *
7. * Caller must revert the estate changes after executing the insertion!
8. * In mtstate, transition capture changes may also need to be reverted.
9. *
10. * Returns a slot holding the tuple of the partition rowtype.
11. */
12. static TupleTableSlot *
13. ExecPrepareTupleRouting(ModifyTableState *mtstate,
14.             EState *estate,
15.             PartitionTupleRouting *proute,
16.             ResultRelInfo *targetRelInfo,
17.             TupleTableSlot *slot)
18. {
19.   ModifyTable *node;
20.   int      partidx;
21.   ResultRelInfo *partrel;
22.   HeapTuple  tuple;
24.   /*
25.    * Determine the target partition.  If ExecFindPartition does not find a
26.    * partition after all, it doesn't return here; otherwise, the returned
27.    * value is to be used as an index into the arrays for the ResultRelInfo
28.    * and TupleConversionMap for the partition.
29.    */
30.   partidx = ExecFindPartition(targetRelInfo,
31.                 proute->partition_dispatch_info,
32.                 slot,
33.                 estate);
34.   Assert(partidx >= 0 && partidx < proute->num_partitions);
36.   /*
37.    * Get the ResultRelInfo corresponding to the selected partition; if not
38.    * yet there, initialize it.
39.    */
40.   partrel = proute->partitions[partidx];
41.   if (partrel == NULL)
42.     partrel = ExecInitPartitionInfo(mtstate, targetRelInfo,
43.                     proute, estate,
44.                     partidx);
46.   /*
47.    * Check whether the partition is routable if we didn't yet
48.    *
49.    * Note: an UPDATE of a partition key invokes an INSERT that moves the
50.    * tuple to a new partition.  This check would be applied to a subplan
51.    * partition of such an UPDATE that is chosen as the partition to route
52.    * the tuple to.  The reason we do this check here rather than in
53.    * ExecSetupPartitionTupleRouting is to avoid aborting such an UPDATE
54.    * unnecessarily due to non-routable subplan partitions that may not be
55.    * chosen for update tuple movement after all.
56.    */
57.   if (!partrel->ri_PartitionReadyForRouting)
58.   {
59.     /* Verify the partition is a valid target for INSERT. */
60.     CheckValidResultRel(partrel, CMD_INSERT);
62.     /* Set up information needed for routing tuples to the partition. */
63.     ExecInitRoutingInfo(mtstate, estate, proute, partrel, partidx);
64.   }
66.   /*
67.    * Make it look like we are inserting into the partition.
68.    */
69.   estate->es_result_relation_info = partrel;
71.   /* Get the heap tuple out of the given slot. */
72.   tuple = ExecMaterializeSlot(slot);
74.   /*
75.    * If we're capturing transition tuples, we might need to convert from the
76.    * partition rowtype to parent rowtype.
77.    */
78.   if (mtstate->mt_transition_capture != NULL)
79.   {
80.     if (partrel->ri_TrigDesc &&
81.       partrel->ri_TrigDesc->trig_insert_before_row)
82.     {
83.       /*
84.        * If there are any BEFORE triggers on the partition, we'll have
85.        * to be ready to convert their result back to tuplestore format.
86.        */
87.       mtstate->mt_transition_capture->tcs_original_insert_tuple = NULL;
88.       mtstate->mt_transition_capture->tcs_map =
89.         TupConvMapForLeaf(proute, targetRelInfo, partidx);
90.     }
91.     else
92.     {
93.       /*
94.        * Otherwise, just remember the original unconverted tuple, to
95.        * avoid a needless round trip conversion.
96.        */
97.       mtstate->mt_transition_capture->tcs_original_insert_tuple = tuple;
98.       mtstate->mt_transition_capture->tcs_map = NULL;
99.     }
100.   }
101.   if (mtstate->mt_oc_transition_capture != NULL)
102.   {
103.     mtstate->mt_oc_transition_capture->tcs_map =
104.       TupConvMapForLeaf(proute, targetRelInfo, partidx);
105.   }
107.   /*
108.    * Convert the tuple, if necessary.
109.    */
110.   ConvertPartitionTupleSlot(proute->parent_child_tupconv_maps[partidx],
111.                 tuple,
112.                 proute->partition_tuple_slot,
113.                 &slot);
115.   /* Initialize information needed to handle ON CONFLICT DO UPDATE. */
116.   Assert(mtstate != NULL);
117.   node = (ModifyTable *) mtstate->ps.plan;
118.   if (node->onConflictAction == ONCONFLICT_UPDATE)
119.   {
120.     Assert(mtstate->mt_existing != NULL);
121.     ExecSetSlotDescriptor(mtstate->mt_existing,
122.                 RelationGetDescr(partrel->ri_RelationDesc));
123.     Assert(mtstate->mt_conflproj != NULL);
124.     ExecSetSlotDescriptor(mtstate->mt_conflproj,
125.                 partrel->ri_onConflict->oc_ProjTupdesc);
126.   }
128.   return slot;
129. }

复制代码

4.4 分区表删除

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