PostgreSQL的表分区技术介绍

PostgreSQL的表分区技术是数据库性能优化的一个重要手段。它允许将一个大型表分割成多个物理上的小表，这些小表分布在不同的存储介质上，但对于用户来说，这些分表仍然表现为一个逻辑上的单一表。这样做的主要目的是为了突破单一存储介质的I/O限制，从而提高数据库的读写效率及整体性能。

什么时候考虑使用表分区

根据官方的建议，当一个表的大小超过了数据库服务器的物理内存时，使用表分区可以获得性能上的提升。实际生产环境中，是否采用分区表的决定可以更灵活，比如当表的大小达到2GB或10GB时，就可以考虑进行分区。

表分区可能带来的性能问题

虽然表分区在很多情况下可以提高性能，但也有可能导致性能下降。这种情况通常发生在两种情况下：一是如果分区的表实际上很小，使用分区表反而会增加额外的管理开销；二是如果查询操作没有利用到分区键，那么性能可能会下降。

PostgreSQL中的表分区技术

PostgreSQL提供了两种表分区的方式：表继承（传统分区表）和声明式表分区。

表继承（传统分区表）

在PostgreSQL 10之前，表分区主要通过表继承实现。这种方式需要定义父表和子表，为子表设置约束和索引，并且通过触发器来实现数据的插入、删除和修改。子表可以有额外的列，而且表继承支持多继承，使得数据可以根据用户的需求进行划分。不过，这种方法实现起来较为复杂，需要编写更多的存储过程和触发器代码。

声明式表分区

从PostgreSQL 10开始，引入了内置的声明式分区功能，简化了分区表的使用和维护。在声明式分区中，分区必须和父表有完全相同的列集合，并且支持范围分区（range）、列表分区（list）和哈希分区（hash）。声明式分区的优势在于用户不需要在父表上定义insert、delete、update触发器，对父表的DML操作会自动路由到相应的分区中，这大幅降低了维护成本，并支持子分区，即每个分区还可以进一步定义自己的分区。

分区策略

PostgreSQL支持以下三种分区策略：

1. 范围分区（Range Partitioning）：根据键值的范围来分区，使得不同分区包含的键值范围不重叠。
2. 列表分区（List Partitioning）：通过显式指定每个分区包含哪些键值来进行分区。
3. 哈希分区（Hash Partitioning）：从PostgreSQL 11开始支持，根据分区键的哈希值进行分区。

分区表的发展

• PostgreSQL 10：引入声明式分区，支持范围和列表分区，简化了分区表的使用。
• PostgreSQL 11：增加哈希分区，支持多字段组合分区，提供主键、外键、索引的自动继承，支持更新分区键等功能。
• PostgreSQL 12 和 13：性能大幅提升，支持逻辑复制，分区裁剪和智能join等高级功能。
• PostgreSQL 14：进一步提升性能，所有分区子表支持reindex，实现部分操作的非阻塞执行。
• PostgreSQL 15及以后：持续增强分区表功能，提升性能和易用性。

创建分区表的基本步骤

1. 创建父表：首先，需要创建一个父表，并指定分区键和分区策略（范围、列表或哈希）。
2. 创建分区表：然后，为每个预定的分区范围创建一个分区表，并指定它们的父表。分区键的范围不得重叠，否则会报错。
3. 在分区上创建索引：通常，在分区键上创建索引是必须的，以提高查询效率。
4. 导入/写入数据：数据可以直接写入父表，PostgreSQL会根据分区键自动将数据路由到相应的分区。

范围分区表的创建

范围分区通常用于按时间段或数值范围将数据分配到不同的分区。创建范围分区表涉及以下步骤：

1. 创建父表：

   CREATE TABLE table_name (    column_name1 data_type,    column_name2 data_type)PARTITION BY RANGE (range_column);
   

   这里，range_column是用来定义分区范围的字段。

2. 创建分区：

   CREATE TABLE partition_name PARTITION OF table_name FOR VALUES FROM (min_value) TO (max_value);
   

   每个分区指定了范围的起始值和结束值，用于存储落在这个范围内的数据。

案例

假设我们有一个需要按年进行分区的表，我们可以这样操作：

1. 创建父表：

   -- 假设time是分区键  CREATE TABLE wuyang (    id SERIAL PRIMARY KEY,    event_date DATE NOT NULL,    data TEXT) PARTITION BY RANGE (event_date);
   

2. 创建分区：

   -- 为2010年之前的数据创建一个分区 CREATE TABLE wuyang_p BEFORE 2010 PARTITION OF wuyang FOR VALUES FROM (MINVALUE) TO ('2010-01-01');-- 为2010年的数据创建一个分区CREATE TABLE wuyang_p2010 PARTITION OF wuyang FOR VALUES FROM ('2010-01-01') TO ('2011-01-01');
   

3. 创建索引：

   -- 在父表上创建全局索引  CREATE INDEX idx_event_date ON wuyang USING BTREE (event_date);

   该索引会自动应用于所有分区。

4. 导入/写入数据：直接向父表插入数据，PostgreSQL会根据分区键自动将数据路由到正确的分区。

在PostgreSQL中，列表分区是一种根据字段的特定值来分区数据的技术。这种分区方式非常适合于字段值种类有限且已知的情况。此外，PostgreSQL还支持默认分区，用于存储不匹配任何已定义分区条件的数据。下面，我们通过一个实际的列表分区创建案例，详细介绍如何在PostgreSQL中使用这一功能。

列表分区：

列表分区允许开发者根据表中某个字段的具体值来组织数据。这意味着，可以为这个字段的每个特定值或值的集合创建一个分区。这样做的好处是，查询针对特定值的数据时，数据库只需扫描包含这些值的分区，而不是整个表，从而提高查询效率。

创建列表分区表的步骤

以下是在PostgreSQL中创建列表分区表的步骤，以及如何管理这些分区：

1. 创建父表：首先，需要定义一个父表，指定分区键和分区策略为列表（LIST）。

   CREATE TABLE wuyang_t1 (    id SERIAL,    name TEXT) PARTITION BY LIST (name);
   

2. 创建分区：然后，根据需要为不同的值或值的集合创建分区。如果有些数据不属于任何特定分区的值，还可以创建一个默认分区来存储这些数据。

   -- 为特定值创建分区 CREATE TABLE wuyang_t1_pt1 PARTITION OF wuyang_t1 FOR VALUES IN ('1111', '2222');-- 创建默认分区CREATE TABLE wuyang_t1_def PARTITION OF wuyang_t1 DEFAULT;
   

3. 在分区上创建索引：为了提高查询性能，可以在分区键上创建索引。索引会自动应用到所有分区上。

   CREATE INDEX idx_wuyang_t1_name ON wuyang_t1 USING BTREE (name);
   

4. 导入/写入数据：数据可以直接写入父表，PostgreSQL会根据分区键的值自动将数据路由到正确的分区。

   -- 示例插入数据   INSERT INTO wuyang_t1 (name) VALUES ('1111'), ('unknown');
   

在这个例子中，值为’1111’的记录会被存储在wuyang_t1_pt1分区中，而’unknown’（或任何不是’1111’或’2222’的值）的记录会被存储在默认分区wuyang_t1_def中。

哈希分区：

PostgreSQL的哈希分区是一种根据分区键的哈希值来分配数据的策略，特别适合于需要均匀分布数据以避免单个表的访问集中的场景。这种分区方法通过对每个分区使用不同的模数（modulus）和余数（remainder）来定义，确保数据能够被均匀分配到各个分区中。接下来，我们将通过一个具体的示例来说明如何在PostgreSQL中创建和使用哈希分区。

创建哈希分区表的步骤

1. 创建父表：首先定义一个父表，并指定分区策略为哈希以及分区键。

   CREATE TABLE wuyang_h1 (    id INT,    name TEXT) PARTITION BY HASH (id);
   

2. 创建分区：为父表创建具体的分区，并指定模数和余数。这里的模数和余数决定了哪些数据行会被分配到该分区。

   CREATE TABLE wuyang_h1_p1 PARTITION OF wuyang_h1 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
   

   这条命令创建了一个分区，用于存储分区键id的哈希值除以4余数为0的所有行。

3. 批量添加分区：如果需要创建多个分区，可以使用PL/pgSQL脚本来批量生成。

   DO $$DECLARE    i INT;BEGIN    FOR i IN 0..3 LOOP        EXECUTE FORMAT('CREATE TABLE wuyang_h1_p%d PARTITION OF wuyang_h1 FOR VALUES WITH (MODULUS 4, REMAINDER %d);', i, i);    END LOOP;END;$$ LANGUAGE plpgsql;
   

   这个脚本为wuyang_h1表创建了总共4个分区，分别覆盖了模数为4时的所有余数情况。

4. 在分区上创建索引：创建索引可以提高查询效率，特别是在分区键上创建。

   CREATE INDEX idx_wuyang_h1_id ON wuyang_h1 USING BTREE (id);
   

   这条命令在父表上创建了一个索引，PostgreSQL会自动在所有分区上创建相同的索引。

5. 导入/写入数据：直接向父表插入数据，PostgreSQL会根据分区键的哈希值自动分配数据到相应的分区。

   INSERT INTO wuyang_h1 SELECT * FROM wuyang;
   

6. 查询分区数据：查询时，可以通过tableoid来查看数据存储在哪个分区。

   SELECT v.tableoid::regclass, COUNT(*) FROM wuyang_h1 v GROUP BY v.tableoid;
   

在使用PostgreSQL进行大规模数据管理时，表分区是一项关键技术，它允许数据根据某些规则分布在多个表中，这些表从逻辑上构成一个大表。本文将介绍如何在PostgreSQL中管理和操作分区表，包括创建分区、查询分区信息、更新分区数据、增加和删除分区，以及分区表和普通表之间的转换等。

查看分区表信息

• 查看所有分区表：

  SELECT partrelid::regclass, * FROM pg_partitioned_table;
  

• 查看不包括子分区的分区表：

  \dP+
  

• 查看特定分区表或某类分区表的定义：

  \d+ wuyang_t1*\dt+ wuyang_t1*
  

• 查看分区包含的数据量：

  SELECT tableoid::regclass, COUNT(*) FROM wuyang_t1 GROUP BY tableoid ORDER BY 1;
  

分区裁剪

分区裁剪是一种性能优化技术，通过在查询时避免扫描不包含目标数据的分区来提高查询效率。在PostgreSQL 11及以上版本，这通过enable_partition_pruning参数控制，通常默认开启。

VACUUM 和 ANALYZE

• VACUUM或ANALYZE操作会对主表及其所有分区起作用，但VACUUM主表时只会作用于主表。

• 查看分区表最后一次VACUUM和ANALYZE时间：

  SELECT relname, last_vacuum, last_analyze FROM pg_stat_all_tables WHERE relname LIKE '%wuyang_t1%';
  

更新分区表数据

• 更新分区键字段时，如果导致数据需要移动到另一个分区，PostgreSQL会自动处理这一过程。

• 增加和删除分区：

• 增加分区时，不需要重建索引，系统会自动更新。

  CREATE TABLE wuyang_t1_p2021 PARTITION OF wuyang_t1 FOR VALUES FROM 开始值 TO 结束值;
  

• 删除分区时，如果分区数据不再使用，可以直接删除子表（分区）。

  DROP TABLE wuyang_t1_p2020;
  

分区表与普通表的转换

• 将分区表转换成普通表：

• 使用DETACH PARTITION命令可以把分区表转换成普通表。

  ALTER TABLE wuyang_t1 DETACH PARTITION wuyang_t1_p2012;
  

• 将普通表转换成分区表：

• 通过ATTACH PARTITION命令可以将普通表附加到分区表上。

  ALTER TABLE wuyang_t1 ATTACH PARTITION wuyang_t1_p2012 FOR VALUES FROM 开始值 TO 结束值;
  

分区表的拆分和合并

• 拆分分区：首先解绑（detach）需要拆分的分区，然后根据新的规则创建子分区，并将数据迁移过去。
• 合并分区：解绑（detach）需要合并的分区，创建一个新的合并后的分区，并将各个旧分区的数据迁移到新分区中。

关于DEFAULT分区

从PostgreSQL 11开始，引入了DEFAULT分区，用于存储不满足任何其他分区条件的数据。这对于数据范围不确定或不完全匹配分区键的情况非常有用。