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 命令快得多，因为它可以一步删除整个分区，而不是触碰每行。

复制

PostgreSQL
分区

表空间