PostgreSQL table partitioning automation (prtitioning daily).

Short example how to create daily based table partitioning.

It allows to create tables even if some of the tables already exists, it will check table existance.

crtime = timestamp column in table, which is tha base for partitioning logic.

stats_history = parent table

stats_history_unknown = table for records with timestamp < 2012-01-01

stats_history_YYYY_MM_DD is the format for child tables.

If  following lines are inserted you have to run it.
ie:  "SELECT create_history_partitions('2012-01-01', '2012-05-01');
Put it into some script with START,STOP date creation logic and activate the script via cron. Now you have all the necessary table partitions prepared.


Activate language:
CREATE LANGUAGE plpgsql;

Create function for partition creation:
CREATE OR REPLACE FUNCTION create_history_partitions( DATE, DATE )
returns void AS $$

DECLARE
    create_query text;
    index_query text;
    new_table_name text;
BEGIN
        FOR create_query, index_query, new_table_name IN SELECT
                        'create table stats_history_'
                        || TO_CHAR( d, 'YYYY_MM_DD' )
                        || ' ( check( crtime >= date '''
                        || TO_CHAR( d, 'YYYY_MM_DD' )
                        || ''' and crtime < date '''
                        || TO_CHAR( d + INTERVAL '1 day', 'YYYY_MM_DD' )
                        || ''' ) ) inherits ( stats_history );',
                        'create index stats_history_'
                        || TO_CHAR( d, 'YYYY_MM_DD' )
                        || '_crtime on stats_history_'
                        || TO_CHAR( d, 'YYYY_MM_DD' )
                        || ' ( crtime );'
                       'create index stats_history_'
                        || TO_CHAR( d, 'YYYY_MM_DD' )
                        || '_sid on stats_history_'
                        || TO_CHAR( d, 'YYYY_MM_DD' )
                        || ' ( sid );',
                  'stats_history_'
                        ||TO_CHAR(d, 'YYYY_MM_DD')
                FROM generate_series( $1, $2, '1 day' ) AS d
        LOOP
    IF NOT EXISTS (SELECT * FROM pg_tables WHERE tablename = new_table_name) THEN
    RAISE INFO 'Creating new table: % and adding needed indexes', new_table_name;
               EXECUTE create_query;
               EXECUTE index_query;
     END IF;
     END LOOP;
END;
$$
language plpgsql;

 

Now create the trigger function:

CREATE OR REPLACE FUNCTION stats_history_partition_inserts()
returns TRIGGER AS $$
BEGIN
IF ( NEW.crtime >= DATE '2012-01-01' ) THEN
execute 'INSERT INTO history_'
     ||TO_CHAR(NEW.crtime,'YYYY_MM_DD')
     ||' VALUES( ($1).*)' USING NEW;
ELSE
INSERT INTO stats_history_unknown values(NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Activate trigger on stats_history

CREATE TRIGGER stats_history
	before INSERT
	ON stats_history
	FOR each row
	execute procedure stats_history_partition_inserts() ;

 

Set constraint exclusion on with following row on postgresql.conf:

constraint_exclusion = on

Set constraint exclusion on following command on psql or psqlrc

SET constraint_exclusion = ON;