PostgreSQL 教程: 表分区

六月 19, 2024

摘要:在本教程中,您将学习如何在 PostgreSQL 中使用表分区。

目录

为什么要对表进行分区?

表分区允许将一个大表拆分为多个小表,这样可以带来以下好处:

  • 较小的表在读取和写入方面速度更快。
  • 您可以非常高效地删除整个分区,而不是逐行删除数据。
  • 由于 PostgreSQL 知道如何修剪未用到的分区,因此您可以将分区用作一种粗略索引。例如,通过按日期划分表,您可能不再需要日期字段上的索引,而是使用顺序扫描。
  • 很少使用的分区可以移动到更便宜的存储中。

分区方式

假设我们有一个表:

CREATE TABLE measurements (
  id int8 NOT NULL,
  value float8 NOT NULL,
  date timestamptz NOT NULL
);

您可以通过提供用作分区键的列,来对该表进行分区:

CREATE TABLE measurements (
  id int8 NOT NULL,
  value float8 NOT NULL,
  date timestamptz NOT NULL
) PARTITION BY RANGE (date);

PostgreSQL 支持多种分区方式,这些方式的区别仅在于它们为分区键指定行值的方式。

按范围分区

按范围分区允许为分区指定一个值的范围,例如,我们可以将每个月的数据存储在一个单独的分区中:

CREATE TABLE measurements_y2021m01 PARTITION OF measurements
FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');

按列表分区

列表分区允许为分区指定一个值列表,例如,我们可以将一小部分经常访问的数据存储在热分区中,并将其余的数据移动到冷分区:

CREATE TABLE measurements (
  id int8 PRIMARY KEY,
  value float8 NOT NULL,
  date timestamptz NOT NULL,
  hot boolean
) PARTITION BY LIST (hot);

CREATE TABLE measurements_hot PARTITION OF measurements
FOR VALUES IN (TRUE);

CREATE TABLE measurements_cold PARTITION OF measurements
FOR VALUES IN (NULL);

然后,您可以通过更改hot列,在分区之间移动行:

-- Move rows to measurements_hot
UPDATE measurements SET hot = TRUE;

-- Move rows to measurements_cold
UPDATE measurements SET hot = NULL;

按哈希分区

按哈希分区允许将行均匀地分布到一组表中,例如,我们可以为表创建 3 个分区,并使用一种相除取余的哈希方法,为行选择一个分区:

CREATE TABLE measurements (
  id int8 PRIMARY KEY,
  value float8 NOT NULL,
  date timestamptz NOT NULL
) PARTITION BY HASH (id);

CREATE TABLE measurements_1 PARTITION OF measurements
FOR VALUES WITH (MODULUS 3, REMAINDER 0);

CREATE TABLE measurements_2 PARTITION OF measurements
FOR VALUES WITH (MODULUS 3, REMAINDER 1);

CREATE TABLE measurements_3 PARTITION OF measurements
FOR VALUES WITH (MODULUS 3, REMAINDER 2);

由于使用了哈希,这些分区将会接收大致相同数量的行。

管理分区

PostgreSQL 允许分离和附加分区:

ALTER TABLE measurements DETACH PARTITION measurements_y2021m01;

ALTER TABLE measurements ATTACH PARTITION measurements_y2021m01
FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');

您可以使用这些命令对现有表进行分区,而无需移动任何数据:

-- Use the existing table as a partition for the existing data.
ALTER TABLE measurements RENAME TO measurements_y2021m01;

-- Create the partitioned table.
CREATE TABLE measurements (LIKE measurements_y2021m01 INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
PARTITION BY RANGE (date);

-- Attach the existing partition with open left constraint.
ALTER TABLE measurements ATTACH PARTITION measurements_y2021m01
FOR VALUES FROM ('0001-01-01') TO ('2021-02-01');

-- Use proper constraints for new partitions.
CREATE TABLE measurements_y2021m02 PARTITION OF measurements
FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

总结

本教程介绍了如何在 PostgreSQL 中,使用表分区将大型数据表拆分为较小的部分。