Automatic partitioning by day - PostgreSQL

Question

I would like to do a daily partitions. I know with oracle is something like this.

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

What about PostgreSQL?

NEW EDIT: SAMPLE SCRIPT:

The script which will maintain first 15 days data in one table say "p1" and remaining days data in another partition.

1- Creating automatic partion depends on the date range of insert command

2- In script i have also mentioned that how we can add index on the required column's.

3- Data from date range from 1st to 14th will be added in partition "p1" and remaining will be added in partition "p2".

Sample Script :

    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

Farleigh

Answer 1

You can use extension pg_partman for automatic partition creation. https://github.com/pgpartman/pg_partman

or you can even use scheduler pg_agent where you will execute a procedure every day at say 18:00:00 to create next days partition.

Ulrick

Answer 2

As of Postgres 12, PARTITION BY RANGE is supported. However, automatic creation of partition (like Oracle's interval) is not supported. You have to manually create each partition.

Also the partition concept in Postgres is different from Oracle. In Oracle partition is considered as an Object and in Postgres, partition is considered as a table. In Postgres, a partitioned table does not itself contain data. It is composed of partitions.

Table creation:

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

Partition creation:

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

See https://www.postgresql.org/docs/current/ddl-partitioning.html for full documentation

Muckworm

According to doc PARTITION BY RANGE is already supported in PG 10 postgresql.org/docs/10/… – Semimonthly

It's worth noticing that you'll get PRIMARY KEY constraint on table "test" lacks column "log_date" which is part of the partition key. error if the ID column is the primary key, which in most situations would be it. I've got it on PostgreSQL 13.2. However I'm not interested in adding log_date to primary key – Shumate

"automatic creation of partition (like Oracle's interval) is not supported. You have to manually create each partition." so why is this an answer? – Smolensk

Answer 3

Postgres does support partitioning on values. However, it won't be automatic because you will need to manually create the partitions after the base table gets created, as of Postgres 10, they do not automatically get generated.

Please see the following link: https://www.postgresql.org/docs/10/ddl-partitioning.html

See if this example makes sense:

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

Trample

Thank you for your reply! So even the latest version of Postgres doesn't something like this "INTERVAL (NUMTODSINTERVAL(1,'DAY'))" to make it dynamic? or create a function to trigger "manually creation of my partitions"? Best regards – Farleigh

That is correct. I double checked version 11 (latest major release), and you still need to specify a list of values to create the partition against, or a range of values. I do not see a way of generating a partition based on a function. – Trample

A BEFORE trigger might be able to create the partition if required, not sure. – Citral

Maybe it's possible by trigger like the (I put above the sample script): The USE CASE it's different but maybe I can apply for daily. – Farleigh