-- partition by month
-- https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html
begin;

drop trigger if exists insert_master_trigger on master;
drop function if exists master_insert();
drop table if exists master cascade;

create table master(
    id bigserial primary key,
    ts bigint not null,
    data text
);

create or replace function master_insert() 
returns trigger as $$
declare
    partition_n bigint;
    begin_id bigint;
    end_id bigint;
    max_child_table_size bigint = 10240;
    table_master varchar := 'master';
    table_child varchar;
begin
    partition_n = 1 + (new.id-1) / max_child_table_size;
    table_child := table_master || '_' || partition_n;
    perform 1 from pg_class where relname = table_child limit 1;
    if not found
    then
        begin_id = 1 + (partition_n-1) * max_child_table_size;
        end_id = begin_id + max_child_table_size;
        execute format('create table %s (check (id>=%s and id < %s)) inherits (%s);', 
            table_child, begin_id, end_id, table_master);
        execute format('create index %s_id_idx on %s (id);', table_child, table_child);
    end if;
    execute 'insert into ' || table_child || ' values ( ($1).* )' using new;
    return null;
end;
$$ 
language plpgsql;

create trigger insert_master_trigger before insert on master for each row execute procedure master_insert();

commit;