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
