Skip to content
Database

分区表

表分区是一种技术，它允许你将一个大表划分成更小、更易管理的部分，称为“分区”。

🌐 Table partitioning is a technique that allows you to divide a large table into smaller, more manageable parts called “partitions”.

下面的图表显示了一个大表被拆分成几个较小的分区，每个分区保存其行的一个子集。

🌐 The diagram below shows a single large table split into several smaller partitions, each holding a subset of its rows.

Diagram showing a single large table divided into several smaller partitions, each holding a subset of the table's rows.

每个分区都包含基于特定条件（比如数值范围或特定条件）的一部分数据。分区可以显著提升查询性能，并简化大数据集的管理。

🌐 Each partition contains a subset of the data based on a specified criteria, such as a range of values or a specific condition. Partitioning can significantly improve query performance and simplify data management for large datasets.

表分区的好处 #

🌐 Benefits of table partitioning

  • 查询性能提升： 允许查询针对特定分区，从而减少扫描的数据量，提高查询执行速度。
  • 可扩展性： 通过分区，你可以随着数据的增长或变化添加或移除分区，从而实现更好的可扩展性和灵活性。
  • **高效的数据管理：**通过对较小的分区进行操作，而不是整个表格，简化了数据加载、归档和删除等任务。
  • 增强的维护操作： 可以优化真空清理和索引，从而加快维护任务的速度。

分区方法 #

🌐 Partitioning methods

Postgres 支持多种分区方法，取决于你想如何分区数据。常用的方法有：

🌐 Postgres supports various partitioning methods based on how you want to partition your data. The commonly used methods are:

  1. 范围分区：数据根据指定的值范围被分成不同的分区。例如，你可以按日期对销售表进行分区，每个分区代表一个特定的时间范围（比如每个月一个分区）。
  2. 列表分区：数据根据指定的值列表被划分为不同的分区。例如，你可以按地区对客户表进行分区，每个分区包含来自特定地区的客户（例如，一个分区存放美国的客户，另一个分区存放欧洲的客户）。
  3. 哈希分区：数据通过哈希函数分布到各个分区。这种方法可以把数据均匀分配到不同的分区，对于负载均衡很有用。不过，它不支持基于特定数值的直接查询。

创建分区表 #

🌐 Creating partitioned tables

下面的例子使用范围分区对销售表按照订单日期进行分区。我们将创建按月分区来存储每个月的数据：

🌐 The following example uses range partitioning for a sales table based on the order date. We'll create monthly partitions to store data for each month:

1
create table sales (
2
    id bigint generated by default as identity,
3
    order_date date not null,
4
    customer_id bigint,
5
    amount bigint,
6
7
    -- We need to include all the
8
    -- partitioning columns in constraints:
9
    primary key (order_date, id)
10
)
11
partition by range (order_date);
12
13
create table sales_2000_01
14
	partition of sales
15
  for values from ('2000-01-01') to ('2000-02-01');
16
17
create table sales_2000_02
18
	partition of sales
19
	for values from ('2000-02-01') to ('2000-03-01');

要创建分区表，你需要在表创建语句后加上 partition by range (<column_name>)。用于分区的列 必须 包含在任何唯一索引中，这就是我们在这里指定复合主键 (primary key (order_date, id)) 的原因。

查询分区表 #

🌐 Querying partitioned tables

要查询分区表，你有两个选择：

🌐 To query a partitioned table, you have two options:

  1. 查询父表
  2. 查询特定分区

查询父表 #

🌐 Querying the parent table

当你查询父表时，Postgres 会根据查询中指定的条件自动将查询路由到相关分区。这让你可以同时从所有分区中获取数据。

🌐 When you query the parent table, Postgres automatically routes the query to the relevant partitions based on the conditions specified in the query. This allows you to retrieve data from all partitions simultaneously.

示例：

🌐 Example:

1
select *
2
from sales
3
where order_date >= '2000-01-01' and order_date < '2000-03-01';

这个查询会从 sales_2000_01 和 sales_2000_02 分区获取数据。

🌐 This query will retrieve data from both the sales_2000_01 and sales_2000_02 partitions.

查询特定分区 #

🌐 Querying specific partitions

如果你只需要从特定分区获取数据，你可以直接查询那个分区，而不是父表。当你想针对分区内的特定范围或条件时，这种方法很有用。

🌐 If you only need to retrieve data from a specific partition, you can directly query that partition instead of the parent table. This approach is useful when you want to target a specific range or condition within a partition.

1
select *
2
from sales_2000_02;

这个查询只会从 sales_2000_02 分区获取数据。

🌐 This query will retrieve data only from the sales_2000_02 partition.

什么时候分区你的表 #

🌐 When to partition your tables

没有一个真正的门槛来决定什么时候应该使用分区。分区会增加复杂性，应该尽量避免复杂性，除非确实需要。这里有一些指南：

🌐 There is no real threshold to determine when you should use partitions. Partitions introduce complexity, and complexity should be avoided until it's needed. A few guidelines:

  • 如果你在考虑性能，最好不要轻易分区，除非你在非分区表上看到性能下降。
  • 如果你把分区当作管理工具，什么时候创建分区都可以。
  • 如果你不知道该如何划分你的数据，那可能还为时过早。

示例 #

🌐 Examples

这里有一些 Postgres 各种分区类型的基本示例。

🌐 Here are basic examples for each of the partitioning types in Postgres.

范围分区 #

🌐 Range partitioning

下面的范围分区示例根据订单日期存储销售数据。我们将创建每月分区来存储每个月的数据。

🌐 The following range partitioning example stores sales data based on the order date. We'll create monthly partitions to store data for each month.

在这个例子中，sales 表被分成了两个分区：sales_january 和 sales_february。这些分区中的数据是根据指定的订单日期范围划分的：

🌐 In this example, the sales table is partitioned into two partitions: sales_january and sales_february. The data in these partitions is based on the specified range of order dates:

1
create table sales (
2
    id bigint generated by default as identity,
3
    order_date date not null,
4
    customer_id bigint,
5
    amount bigint,
6
7
    -- We need to include all the
8
    -- partitioning columns in constraints:
9
    primary key (order_date, id)
10
)
11
partition by range (order_date);
12
13
create table sales_2000_01
14
	partition of sales
15
  for values from ('2000-01-01') to ('2000-02-01');
16
17
create table sales_2000_02
18
	partition of sales
19
	for values from ('2000-02-01') to ('2000-03-01');

列表分区 #

🌐 List partitioning

下面这个列表分区示例是根据客户所在的地区来存储客户数据的。我们将创建分区来存储来自不同地区的客户。

🌐 The following list partitioning example stores customer data based on their region. We'll create partitions to store customers from different regions.

在这个例子里，customers 表被分成了两个分区：customers_americas 和 customers_asia。这些分区中的数据是根据指定的区域列表来划分的：

🌐 In this example, the customers table is partitioned into two partitions: customers_americas and customers_asia. The data in these partitions is based on the specified list of regions:

1
-- Create the partitioned table
2
create table customers (
3
    id bigint generated by default as identity,
4
    name text,
5
    country text,
6
7
    -- We need to include all the
8
    -- partitioning columns in constraints:
9
    primary key (country, id)
10
)
11
partition by list(country);
12
13
create table customers_americas
14
	partition of customers
15
	for values in ('US', 'CANADA');
16
17
create table customers_asia
18
	partition of customers
19
  for values in ('INDIA', 'CHINA', 'JAPAN');

哈希分区 #

🌐 Hash partitioning

你可以用哈希分区来均匀分配数据。

🌐 You can use hash partitioning to evenly distribute data.

在这个例子中，products 表被分成两个分区：products_one 和 products_two。数据通过哈希函数分布到这些分区中：

🌐 In this example, the products table is partitioned into two partitions: products_one and products_two. The data is distributed across these partitions using a hash function:

1
create table products (
2
    id bigint generated by default as identity,
3
    name text,
4
    category text,
5
    price bigint
6
)
7
partition by hash (id);
8
9
create table products_one
10
	partition of products
11
  for values with (modulus 2, remainder 1);
12
13
create table products_two
14
	partition of products
15
  for values with (modulus 2, remainder 0);

其他工具 #

🌐 Other tools

Postgres 分区还有其他几个工具可用，最著名的要数 pg_partman。Postgres 10 引入了原生分区，普遍认为性能更好。

🌐 There are several other tools available for Postgres partitioning, most notably pg_partman. Native partitioning was introduced in Postgres 10 and is generally thought to have better performance.