PostgreSQL技术内幕17:PG分区表（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+  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 分区表删除

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