PostgreSQL分区表：根本语法与运维实践

弁言

简介：什么是数据库分区

数据库分区是一种将大型表物理上分割成多个较小的部门的技能。每个部门称为一个分区，这些分区可以分布在差别的存储装备上，以进步查询性能和管理服从。
为什么使用分区表

  

• 进步查询性能：通过镌汰须要扫描的数据量，查询速率可以明显提拔。  
  
• 简化数据管理：分区使得数据的备份、规复和归档更加轻易。  
  
• 优化存储：可以将差别分区放在差别的存储介质上，以平衡性能和本钱。  
  
• 增强可维护性：分区表的维护利用（如索引重修、数据整理）可以逐分区举行，镌汰对整个体系的干扰。
  

PostgreSQL中的分区支持概述

PostgreSQL从9.0版本开始引入了分区支持，并在后续版本中不绝改进。现在，PostgreSQL支持多种分区范例，包罗范围分区、列表分区和散列分区。通过这些分区技能，可以有用地管理和优化大规模数据表。
分区范例

范围分区（Range Partitioning）

范围分区是根据一个或多个列的值范围来分别表。比方，可以根据日期或数值范围来创建分区。
示例

1. CREATE TABLE sales (
2.     id SERIAL ,
3.     sale_date DATE NOT NULL,
4.     amount NUMERIC,
5.     PRIMARY KEY (id,sale_date)
6. ) PARTITION BY RANGE (sale_date);
8. CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
9. CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

复制代码

这个语句创建了一个名为 sales 的表，该表有三个字段：id（一个自动递增的主键）、sale_date（一个非空的日期字段）和 amount（一个数值字段）。紧张的是，这个表被声明为一个分区表，基于 sale_date 字段的范围举行分区。并创建了 sales 表的两个分区子表：sales_2022 和 sales_2023。每个子表都负责存储 sales 表中特定日期范围内的数据。此中，sales_2022 存储 sale_date 从 2022-01-01 到 2022-12-31 的数据，sales_2023 存储 sale_date 从 2023-01-01 到 2023-12-31 的数据。
列表分区（List Partitioning）

列表分区是根据一个或多个列的具体值来分别表。实用于列值有限且已知的环境。
示例

1. CREATE TABLE employees (
2.     id SERIAL ,
3.     department VARCHAR(50) NOT NULL,
4.     name VARCHAR(100),
5.     PRIMARY KEY (id,department)
6. ) PARTITION BY LIST (department);
8. CREATE TABLE employees_sales PARTITION OF employees FOR VALUES IN ('Sales');
9. CREATE TABLE employees_marketing PARTITION OF employees FOR VALUES IN ('Marketing');

复制代码

这个语句创建了一个名为 employees 的表，该表有三个字段：id（一个自动递增的主键）、department（一个非空的 VARCHAR 范例字段，用于存储部门名称）和 name（一个 VARCHAR 范例字段，用于存储员工姓名）。这个表被声明为一个分区表，基于 department 字段的列表值举行分区。并创建了 employees 表的两个分区子表：employees_sales 和 employees_marketing。employees_sales 负责存储 department 字段值为 ‘Sales’ 的员工数据，而 employees_marketing 负责存储 department 字段值为 ‘Marketing’ 的员工数据。