Automatic partitioning in PostgreSQL

This is just for test purposes. Do not use in production environment. As Pavel stated it will be terrible under high load. This post is just to show
it is possible to do it but not recommended. 

Last week I eed to do a partitioning on a big table but the problem is, i need to create partition for everyday. Everyday more than 15M records will be inserted into this table so, I try to automatic partitions. I don't mean creating partitions with cron or other system tools. I need to create partitions on postgreSQL using sql. So I use triggers and some system tables and it's done.

Above example is aim to do followings,

• Create tables and data for testing purposes
• Create partition tables automatically so when new record is inserted, if it's appropriate table is not found, create it.
• After creating appropriate partition table then insert data to corresponding, partition

Let's start with creating tables and a function to create data for testing,

--create database first
--create  tables
--create database first
create database icecream_co_2;
comment on database icecream_co is 'daily partition test';
--create  tables
create table market (id serial primary key,name varchar(50));
--insert data for a market
insert into market (name) values ('test');
create table sales(id serial primary key, market_id integer references market(id),amount real ,created_at date);

Now we need data function (i prefer to write in pl/pgsql you can write where you want to) This function will insert data to sales table having created_at column (we will use this column in partitioning) is date. Our test data is starting from 01.06.2012 to 30.06.2012, for each day it executes 100 insert statements.

CREATE OR REPLACE FUNCTION createDailyDataForPartition() RETURNS void  AS
$$
DECLARE
    c date;
BEGIN
    FOR c IN SELECT CAST(d AS date) FROM generate_series(CAST('2012-06-01' AS timestamp with time zone),'2012-06-30','1 day') AS g(d)
    LOOP
        FOR i IN 1..100 LOOP
            insert into sales (amount,market_id,created_at) values (cast(i as real),1,cast(c as date));
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE 'plpgsql' ;

Ok. We will use this function after creating partitions. Now.. here is the function that automatically creates partitions.

CREATE OR REPLACE FUNCTION create_daily_partition_tables(partition_id date) RETURNS void AS
$$
BEGIN

EXECUTE format('CREATE TABLE sales_%s (
    CHECK ( created_at >= %L AND created_at <= %L)
    ) INHERITS (sales)',to_char(partition_id,'yyyymmdd'),partition_id,partition_id+1);
END;
$$ LANGUAGE 'plpgsql';

As you see this function takes date as parameter. This date will be used for partition names and check constraints. This function is quite simple to write if anyone needs explanation refer to postgresql documentation or ask here. Now we need a trigger function which will insert data to appropriate partitions and if partition not found must call the function create_daily_partition_tables

-- trigger function
CREATE OR REPLACE FUNCTION sales_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
flag boolean;
tmp_partition text;
BEGIN
    --check if the partition exist if not create partition
    tmp_partition:='sales_'||to_char(NEW.created_at,'yyyymmdd');
    SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END INTO flag FROM pg_class WHERE relname::text=tmp_partition;
    IF (flag) THEN

        EXECUTE 'INSERT INTO ' || tmp_partition
        ||' (amount,market_id,created_at) '
        || ' VALUES (' || NEW.amount
        ||','||NEW.market_id||','
        ||quote_literal(NEW.created_at) ||')' ;

    ELSE
        EXECUTE create_daily_partition_tables(NEW.created_at);
        --we should also send NEW record to partition

        EXECUTE 'INSERT INTO ' || tmp_partition
        ||' (amount,market_id,created_at) '
        || ' VALUES (' || NEW.amount
        ||','||NEW.market_id||','
        ||quote_literal(NEW.created_at) ||')' ;


    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Ok. After creating that function we should add trigger.

    CREATE TRIGGER insert_sales_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE sales_insert_trigger();

Lets insert some data and look at output.

select createDailyDataForPartition();

and thats all.. lets check partitions and EXPLAIN ANALYZE output.

select count(*) from sales;

and partitions...

postgresql version 9.1.3