I am working on a date-based partitioning framework and I would really like to have a single function that could be used as trigger for any table that needs to be partitioned by day. I am working in a rails environment, so every table has a created_at datetime field.
I created my generic function:
create or replace function day_partition_insert_trigger()
returns trigger as $$
declare
ins_tbl varchar;
begin
ins_tbl := TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || '_' || to_char(NEW.created_at,'YYYYMMDD');
execute 'insert into '|| ins_tbl ||' select ($1).*' using NEW;
return null;
end;
$$ language plpgsql;
And then I assigned the function to two different test tables to make sure it would work:
create trigger insert_daily_trigger before insert on testdailyone for each row execute procedure day_partition_insert_trigger();
create trigger insert_daily_trigger before insert on testdailytwo for each row execute procedure day_partition_insert_trigger();
Inserts work fine, and I was able to validate records are being inserted into the correct child tables.
I began to wonder if there would be a performance degradation, so I changed the testdailytwo trigger function the typical if, elsif described in the partitioning documentation and then ran pgbench against both tables.
I noticed that with 7 partitions, the if, elsif was slightly faster (~8%). However, when adding 30 partitions, the if, elsif version became slower. I'd sort of expected this.
So, my conclusion is that the generic function will work, and it will make administration (even automated administration) of partitioned tables much simpler.
My question is... Is there a compelling reason why I should NOT do this. I must confess, it seems so straightforward that I feel like I must be missing something.
Thanks,
Greg Haase