自动按天分区 - PostgreSQL

Question

我希望每天都能进行分区。我知道在Oracle中是这样做的。

CREATE TABLE "PUBLIC"."TEST"     
(   
"ID" NUMBER(38,0) NOT NULL ENABLE, 
"SOME_FIELD" VARCHAR2(20 BYTE) NOT NULL ENABLE,     
"ANOTHER_FIELD" VARCHAR2(36 BYTE) NOT NULL ENABLE,    
TABLESPACE "PUBLIC"."TEST_DATA" 
PARTITION BY RANGE ("TEST_DATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))  
(PARTITION "TEST_P1"  
VALUES LESS THAN (TIMESTAMP' 2019-01-01 00:00:00')   TABLESPACE "TEST_DATA" );

PostgreSQL怎么样？

新编辑：

示例脚本：

该脚本将在一个表中维护前15天的数据，称为“p1”，并将其余天数的数据存储在另一个分区中。

1- 创建自动分区取决于插入命令的日期范围

2- 在脚本中，我还提到了如何在需要的列上添加索引。

3- 从第1天到第14天的日期范围的数据将添加到分区“p1”中，其余数据将添加到分区“p2”中。

示例脚本：

    CREATE TABLE measurement (
        city_id         int not null,
        logdate         date not null,
        peaktemp        int,
        unitsales       int
    );

    CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_date TEXT;
      partition TEXT;
      partition_day int;
      startdate date;
      enddate date;
    BEGIN
      partition_day := to_char(NEW.logdate,'DD');
      partition_date := to_char(NEW.logdate,'YYYY_MM');

         IF partition_day < 15 THEN
      partition := TG_RELNAME || '_' || partition_date || '_p1';
      startdate := to_char(NEW.logdate,'YYYY-MM-01');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      ELSE 
      partition := TG_RELNAME || '_' || partition_date || '_p2';
      startdate := to_char(NEW.logdate,'YYYY-MM-15');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      END IF;

      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( logdate >= DATE ''' || startdate || '''  AND logdate <=  DATE ''' ||  enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
        EXECUTE 'CREATE INDEX ' || partition || '_logdate ON '  || partition || '(logdate)';
        EXECUTE 'ALTER TABLE ' || partition || ' add primary key(city_id);';       
        END IF;
        EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100; 


    CREATE TRIGGER testing_partition_insert_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE new_partition_creator();


    postgres=# insert into measurement values(1,'2017-10-11',10,10);
    NOTICE:  A partition has been created measurement_2017_10_p1
    INSERT 0 0

- R. Pereira

Answer 1

您可以使用扩展程序pg_partman来进行自动分区创建。 https://github.com/pgpartman/pg_partman 或者您甚至可以使用调度程序pg_agent，在每天的18:00:00执行一次过程来创建第二天的分区。

- Amol Tarte

Answer 2

从Postgres 12开始，支持PARTITION BY RANGE。

然而，不支持自动创建分区（像Oracle的interval一样）。您必须手动创建每个分区。

此外，Postgres中的分区概念与Oracle不同。在Oracle中，分区被视为对象，在Postgres中，分区被视为表。在Postgres中，分区表本身不包含数据。它由分区组成。

表创建：

CREATE TABLE TEST (
ID INT NOT NULL,
LOG_DATE DATE)
PARTITION BY RANGE (LOG_DATE);

分区创建：

CREATE TABLE TEST_MAR21 
PARTITION OF TEST
FOR VALUES FROM ('01-MAR-2021') TO ('31-MAR-2021');

CREATE TABLE TEST_APR21 
PARTITION OF TEST
FOR VALUES FROM ('01-APR-2021') TO ('30-APR-2021');

详细的文档请参见https://www.postgresql.org/docs/current/ddl-partitioning.html

- Nithin Mathew

---

根据文档，PG 10已经支持按范围分区 PARTITION BY RANGE https://www.postgresql.org/docs/10/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE - Amit Goldstein

值得注意的是，如果ID列是主键（在大多数情况下都是这样），则在表“test”上的“PRIMARY KEY”约束缺少分区键的一部分“log_date”时会出现错误。我在PostgreSQL 13.2上遇到了这个问题。然而，我不想将log_date添加到主键中。 - ka3ak

Answer 3

Postgres支持按数值进行分区。不过，这并不是自动完成的，因为在创建基础表之后，您需要手动创建分区，截至Postgres 10版本，它们不会自动生成。

请参见以下链接：https://www.postgresql.org/docs/10/ddl-partitioning.html

看看这个示例是否清晰易懂：

CREATE TABLE PartTest
(
    idx INTEGER,
    partMe Date
) PARTITION BY LIST (partMe);

CREATE TABLE PartTest_2019_04_11 PARTITION OF PartTest
    FOR VALUES IN ('2019-04-11');

- bogertron

---

谢谢您的回复！那么即使是最新版本的Postgres也没有像这样的“INTERVAL（NUMTODSINTERVAL（1，'DAY'））”来使其动态化？或者创建一个函数来手动触发“我的分区的手动创建”？此致敬意。 - R. Pereira

没错。我仔细检查了版本11（最新的主要发布版），你仍然需要指定一个值列表来创建分区，或者一个值范围。我没有看到基于函数生成分区的方法。 - bogertron

一个 BEFORE 触发器可能能够在需要时创建分区，但不确定。 - Laurenz Albe

也许可以通过触发器实现，就像（我在示例脚本上方放置的那样）： 用例不同，但也许我可以每天应用它。 - R. Pereira