六月 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 中,使用表分区将大型数据表拆分为较小的部分。