為什么要對(duì)表進(jìn)行分區(qū)？

表分區(qū)允許將一個(gè)大表拆分為多個(gè)小表，這樣可以帶來以下好處：

• • 較小的表在讀取和寫入方面速度更快。

• • 您可以非常高效地刪除整個(gè)分區(qū)，而不是逐行刪除數(shù)據(jù)。

• • 由于 PostgreSQL 知道如何修剪未用到的分區(qū)，因此您可以將分區(qū)用作一種粗略索引。例如，通過按日期劃分表，您可能不再需要日期字段上的索引，而是使用順序掃描。

• • 很少使用的分區(qū)可以移動(dòng)到更便宜的存儲(chǔ)中。

分區(qū)方式

假設(shè)我們有一個(gè)表：

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

您可以通過提供用作分區(qū)鍵的列，來對(duì)該表進(jìn)行分區(qū)：

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

PostgreSQL 支持多種分區(qū)方式，這些方式的區(qū)別僅在于它們?yōu)榉謪^(qū)鍵指定行值的方式。

按范圍分區(qū)

按范圍分區(qū)允許為分區(qū)指定一個(gè)值的范圍，例如，我們可以將每個(gè)月的數(shù)據(jù)存儲(chǔ)在一個(gè)單獨(dú)的分區(qū)中：

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

按列表分區(qū)

列表分區(qū)允許為分區(qū)指定一個(gè)值列表，例如，我們可以將一小部分經(jīng)常訪問的數(shù)據(jù)存儲(chǔ)在熱分區(qū)中，并將其余的數(shù)據(jù)移動(dòng)到冷分區(qū)：

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列，在分區(qū)之間移動(dòng)行：

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

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

按哈希分區(qū)

按哈希分區(qū)允許將行均勻地分布到一組表中，例如，我們可以為表創(chuàng)建 3 個(gè)分區(qū)，并使用一種相除取余的哈希方法，為行選擇一個(gè)分區(qū)：

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);

由于使用了哈希，這些分區(qū)將會(huì)接收大致相同數(shù)量的行。

管理分區(qū)

PostgreSQL 允許分離和附加分區(qū)：

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

您可以使用這些命令對(duì)現(xiàn)有表進(jìn)行分區(qū)，而無需移動(dòng)任何數(shù)據(jù)：

-- 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');

總結(jié)

本教程介紹了如何在 PostgreSQL 中，使用表分區(qū)將大型數(shù)據(jù)表拆分為較小的部分。