【实操】单表数据量 200 GB，PostgreSQL 怎么应对？？

当前运行的 Oracle 数据库，有个大表数据量几百 GB，担心 PostgreSQL 应付不了？其实，不用担心，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');

关注公众号获取更多优质好文：