PostgreSQL/分割槽

如果您有一個包含大量資料的表，將資料分散到共享相同資料結構的不同物理表中可能會有所幫助。在這種情況下，如果 DML 語句只涉及其中一個物理表，那麼您可以從分割槽中獲得巨大的效能優勢。通常情況下，如果某個列的值存在時間線或地理分佈，就會出現這種情況。

宣告式分割槽語法：從版本 10 開始

Postgres 10 在之前的表繼承語法基礎上引入了宣告式分割槽定義語法。使用這種語法，不再需要定義額外的觸發器，但與之前的解決方案相比，功能保持不變。

首先，您定義一個主表，其中包含分割槽方法，在本例中為 PARTITION BY RANGE (column_name)。

CREATE TABLE log (
  id       int  not null,
  logdate  date not null,
  message  varchar(500)
) PARTITION BY RANGE (logdate);

接下來，您建立與主表結構相同的分割槽，並確保只有預期資料範圍內的行才能儲存在那裡。這些分割槽是傳統的物理表。

CREATE TABLE log_2015_01 PARTITION OF log FOR VALUES FROM ('2015-01-01') TO ('2015-02-01');
CREATE TABLE log_2015_02 PARTITION OF log FOR VALUES FROM ('2015-02-01') TO ('2015-03-01');
...
CREATE TABLE log_2015_12 PARTITION OF log FOR VALUES FROM ('2015-12-01') TO ('2016-01-01');
CREATE TABLE log_2016_01 PARTITION OF log FOR VALUES FROM ('2016-01-01') TO ('2016-02-01');
...

表繼承語法

首先，您定義一個主表，它是一個傳統的表。

CREATE TABLE log (
  id       int  not null,
  logdate  date not null,
  message  varchar(500)
);

接下來，您使用表繼承機制 INHERITS (table_name) 建立與主表結構相同的分割槽。此外，您必須確保只有預期資料範圍內的行才能儲存在派生表中。

CREATE TABLE log_2015_01 (CHECK (logdate >= DATE '2015-01-01' AND logdate < DATE '2015-02-01')) INHERITS (log);
CREATE TABLE log_2015_02 (CHECK (logdate >= DATE '2015-02-01' AND logdate < DATE '2015-03-01')) INHERITS (log);
...
CREATE TABLE log_2015_12 (CHECK (logdate >= DATE '2015-12-01' AND logdate < DATE '2016-01-01')) INHERITS (log);
CREATE TABLE log_2016_01 (CHECK (logdate >= DATE '2016-01-01' AND logdate < DATE '2016-02-01')) INHERITS (log);
...

您需要一個函式，將行轉移到適當的分割槽。

CREATE OR REPLACE FUNCTION log_ins_function() RETURNS TRIGGER AS $$
BEGIN
  IF (NEW.logdate >= DATE '2015-01-01' AND NEW.logdate < DATE '2015-02-01' ) THEN
        INSERT INTO log_2015_01 VALUES (NEW.*);
  ELSIF (NEW.logdate >= DATE '2015-02-01' AND NEW.logdate < DATE '2015-03-01' ) THEN
        INSERT INTO log_2015_02 VALUES (NEW.*);
  ELSIF ...
    ...
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

觸發器會呼叫該函式。

CREATE TRIGGER log_ins_trigger
  BEFORE INSERT ON log
  FOR EACH ROW EXECUTE PROCEDURE log_ins_function();

後續步驟

建立一個索引是個好主意。

CREATE INDEX log_2015_01_idx ON log_2015_01 (logdate);
CREATE INDEX log_2015_02_idx ON log_2015_02 (logdate);
...

許多 DML 語句，如 SELECT * FROM log WHERE logdate = '2015-01-15'; 只作用於一個分割槽，可以忽略所有其他分割槽。這在需要進行全表掃描的情況下非常有用。查詢最佳化器有機會生成避免掃描不必要分割槽的執行計劃。

在所示示例中，新行主要進入最新的分割槽。幾年後，您可以將舊分割槽整體刪除。這應該使用 DROP TABLE 命令完成 - 而不是 DELETE 命令。DROP TABLE 命令比 DELETE 命令快得多，因為它可以一步刪除整個分割槽，而不是觸碰每行。