On Tuesday, September 3, 2013, Gregory Haase wrote:
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()
execute 'insert into '|| ins_tbl ||' select ($1).*' using NEW;
return null;
end;
$$ language plpgsql;
...
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.
Did you try an if, elsif, version structured like a binary search rather than a linear search?
Also, did you try them with a \copy rather than insert in a loop?
Cheers,
Jeff
I experimented with trigger based inserts and rule based inserts.
In my case I insert many rows at a time and in that case, rule based inserts performed better.
Here is an example from me and it is based on the online postgres documents.
CREATE TABLE test ( id integer, ts timestamp without time zone, value real );
-- create each partition, example for a single one
CREATE INDEX idx_test_2013_08_16_ts ON test_partition_2013_08_16 USING btree (ts);
-- for each partition create a rule like the following:
CREATE OR REPLACE RULE test_partition_2013_08_16_rule AS ON INSERT TO test WHERE new.ts >= '2013-08-16'::date AND new.ts < '2013-08-17'::date DO INSTEAD INSERT INTO test_partition_2013_08_16 (id, ts, value) VALUES (new.id, new.ts, new.value);
I have create a function/procedure that creates and drops the partitions for me and run it from crontab.
