In this example the tables history, history_uint, trends and trends_uint are partitioned on a monthly basis.
To partition the tables with this example you need a trigger (the trigger calls the function on every INSERT in the table), a function (the function does all the partition stuff) and an inherited table (the new place for the data).
psql -f <name>.sql <dbname>
For this example you need 4 functions (you may find the functions at the end of this wiki entry)
I have another function which creates the partitions:
CREATE OR REPLACE FUNCTION create_zbx_partitions(INTEGER DEFAULT 0) RETURNS VOID AS $$ DECLARE history_part TEXT; history_uint_part TEXT; trends_part TEXT; trends_uint_part TEXT; index_history_part TEXT; index_history_uint_part TEXT; index_trends_part TEXT; index_trends_uint_part TEXT; alter_history_part TEXT; alter_history_uint_part TEXT; alter_trends_part TEXT; alter_trends_uint_part TEXT; next_partition TEXT; current_check TEXT; next_check TEXT; BEGIN IF $1 > 0 then SELECT extract(epoch FROM date_trunc('month',current_timestamp)) INTO current_check; SELECT extract(epoch FROM date_trunc('month',current_timestamp + interval '1 month')) INTO next_check; SELECT to_char(current_timestamp,'mmyyyy') INTO next_partition; else SELECT extract(epoch FROM date_trunc('month',current_timestamp + interval '1 month')) INTO current_check; SELECT extract(epoch FROM date_trunc('month',current_timestamp + interval '2 month')) INTO next_check; SELECT to_char(current_timestamp + interval '1 month','mmyyyy') INTO next_partition; end IF; --History history_part:='create table history_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock < ' || next_check || '),like history including defaults including storage) with oids'; alter_history_part:='alter table history_' || next_partition || ' inherit history'; index_history_part:='create index history_' || next_partition || '_1 on history_' || next_partition || '(itemid,clock)'; --History Uint history_uint_part:='create table history_uint_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock < ' || next_check || '), like history_uint including defaults including storage) with oids'; alter_history_uint_part:='alter table history_uint_' || next_partition || ' inherit history_uint'; index_history_uint_part:='create index history_uint_' || next_partition || '_1 on history_uint_' || next_partition || '(itemid,clock)'; --Trends trends_part:='create table trends_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock < ' || next_check || '), like trends including defaults including storage) with oids'; alter_trends_part:='alter table trends_' || next_partition || ' inherit trends'; index_trends_part:='create index trends_' || next_partition || '_1 on trends_' || next_partition || '(itemid,clock)'; --Trends Uint trends_uint_part:='create table trends_uint_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock <' || next_check || '), like trends_uint including defaults including storage) with oids'; alter_trends_uint_part:='alter table trends_uint_' || next_partition || ' inherit trends_uint'; index_trends_uint_part:='create index trends_uint_' || next_partition || '_1 on trends_uint_' || next_partition || '(itemid,clock)'; execute history_part; execute alter_history_part; execute index_history_part; execute history_uint_part; execute alter_history_uint_part; execute index_history_uint_part; execute trends_part; execute alter_trends_part; execute index_trends_part; execute trends_uint_part; execute alter_trends_uint_part; execute index_trends_uint_part; END $$ LANGUAGE plpgsql;
SELECT create_zbx_partitions();
SELECT create_zbx_partitions(1);
CREATE OR REPLACE FUNCTION trends_insert_trigger() RETURNS TRIGGER AS $$ DECLARE insert_sql TEXT; BEGIN insert_sql:= 'INSERT INTO trends_' || to_char(to_timestamp(NEW.clock),'mmyyyy') || '(itemid,clock,num,value_min,value_avg,value_max) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.num || ',' || NEW.value_min || ',' || NEW.value_avg || ',' || NEW.value_max || ')'; EXECUTE insert_sql; RETURN NULL; END $$ LANGUAGE plpgsql; CREATE TRIGGER insert_trends_trigger BEFORE INSERT ON trends FOR EACH ROW EXECUTE PROCEDURE trends_insert_trigger();
CREATE OR REPLACE FUNCTION trends_uint_insert_trigger() RETURNS TRIGGER AS $$ DECLARE insert_sql TEXT; BEGIN insert_sql:= 'INSERT INTO trends_uint_' || to_char(to_timestamp(NEW.clock),'mmyyyy') || '(itemid,clock,num,value_min,value_avg,value_max) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.num || ',' || NEW.value_min || ',' || NEW.value_avg || ',' || NEW.value_max || ')'; EXECUTE insert_sql; RETURN NULL; END $$ LANGUAGE plpgsql; CREATE TRIGGER insert_trends_uint_trigger BEFORE INSERT ON trends_uint FOR EACH ROW EXECUTE PROCEDURE trends_uint_insert_trigger();
CREATE OR REPLACE FUNCTION history_insert_trigger() RETURNS TRIGGER AS $$ DECLARE insert_sql TEXT; BEGIN insert_sql:= 'INSERT INTO history_' || to_char(to_timestamp(NEW.clock),'mmyyyy') || '(itemid,clock,value) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.value || ')'; EXECUTE insert_sql; RETURN NULL; END $$ LANGUAGE plpgsql; CREATE TRIGGER insert_history_trigger BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE history_insert_trigger();
CREATE OR REPLACE FUNCTION history_uint_insert_trigger() RETURNS TRIGGER AS $$ DECLARE insert_sql TEXT; BEGIN insert_sql:= 'INSERT INTO history_uint_' || to_char(to_timestamp(NEW.clock),'mmyyyy') || '(itemid,clock,value) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.value || ')'; EXECUTE insert_sql; RETURN NULL; END $$ LANGUAGE plpgsql; CREATE TRIGGER insert_history_uint_trigger BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE history_uint_insert_trigger();
