Partitioning of Zabbix Database (PostgreSQL)

 We are using highloaded Zabbix database (currently PostgreSQL 9.1). The database contains tables with approximately 1700 million (2 billion) rows. The common technique to optimize bulk INSERTs and DELETEs with tables like this is to use Table Partitioning. I managed to do this by this guidelines (in Russian): http://www.zabbix.com/wiki/non-english/ru/partitioning_in_postgresql. 
 Although the manual is extensive, during the production usage some problem have arose. 

 First, it was a problem with SELECT queries to partitioned tables that was fixed by a separate patch before Zabbix v.1.8.13. As of this version the patch is no more required. It brings a significant performance gain. 

 Secondly, it's about the fact that procedures in the manual above have an unpleasant effect. The data of first INSERT query that triggers a partition creation are always lost. I didn't have any desire to rewrite the proposed SQL, instead I implemented the obvious fix. To make INSERTs into history* tables beforehand. This also helps to solve the problem with table load. Partitioning procedures put a lock on tables and this can easily results in Zabbix malfunction. Planing will help you to avoid being awaken at 0.00 when Partitioning usually starts.
Example:

INSERT INTO history_uint (itemid, clock, value) VALUES ('1', extract( epoch FROM date_trunc('hour', now() + interval '12 hour')), '1')";

  The third fix is related to conditions that are wrongly used in the recipe above. I just put the corrected versions here. Corresponding lines are in the check_condition definition and I made them bold.


  Hope, this notes will help someone.

CREATE OR REPLACE FUNCTION "public"."partition_every_day" (in parentoid oid, in scheme varchar, in clock int4) RETURNS text AS
$BODY$
declare
  parent text := parentoid::regclass;
  suffix text := to_char (to_timestamp(clock), '_YYYY_MM_DD');
  child  text := scheme || (select relname from pg_class where oid  = parentoid) || suffix;
  check_beg varchar;
  check_end varchar;
  check_condition varchar;
  check_field     varchar := null;
  tmp record;
  script text := '';
  i int := 0;
  j int := 0;
begin
  
  perform child::regclass;
  return child;
exception
  when undefined_table then
    
    check_beg = extract(epoch FROM date_trunc('day', to_timestamp(clock)));
    check_end =  extract(epoch FROM date_trunc('day', to_timestamp(clock) + interval '1 day'));
 
    
    j = (select count(*) from pg_attribute where attrelid = parentoid  and attnum >0);
    for tmp in
    select attname from pg_attribute where attrelid = parentoid and attnum >0 order by attnum loop
      i = i + 1;
      script = script || 'NEW.' || tmp.attname || case i when j then '' else ',' end;
      
      if (col_description (parentoid, i) ~* 'partition') and (check_field is null)
        then check_field = tmp.attname; end if;
    end loop;
    script = script || ')';
 
    
    check_condition =
    '(
       ' || check_field || ' >= ' || quote_literal (check_beg) || ' and
       ' || check_field || ' < ' || quote_literal (check_end) || '
     )';
 
    
    execute
    'create table ' || child || '
    (
     constraint partition' || suffix || ' check '
      || check_condition || '
    )
    inherits (' || parent || ')';
 
    
    execute
    'create rule route' || suffix || ' as '
      || ' on insert to ' || parent || ' where '
      || check_condition
      || ' do instead insert into ' || child
      || ' values (' || script;
 
    
    perform copy_constraints(parent, child);
    perform copy_indexes(parent, child);
    execute 'GRANT SELECT ON ' || child || ' TO some_other_user';
    execute 'GRANT ALL ON ' || child || ' TO zabbix';
    
  return child;
end;
$BODY$
LANGUAGE 'plpgsql'

-----

CREATE OR REPLACE FUNCTION "public"."partition_every_month" (in parentoid oid, in scheme varchar, in clock int4) RETURNS text AS
$BODY$
declare
  parent text := parentoid::regclass;
  suffix text := to_char (to_timestamp(clock), '_YYYY_MM');
  child  text := scheme || (select relname from pg_class where oid  = parentoid) || suffix;
  check_beg varchar;
  check_end varchar;
  check_condition varchar;
  check_field     varchar := null;
  tmp record;
  script text := '';
  i int := 0;
  j int := 0;
begin
  
  perform child::regclass;
  return child;
exception
  when undefined_table then
    
    check_beg = extract(epoch FROM date_trunc('month', to_timestamp(clock)));
    check_end =  extract(epoch FROM date_trunc('month', to_timestamp(clock) + interval '1 month'));
 
    
    j = (select count(*) from pg_attribute where attrelid = parentoid  and attnum >0);
    for tmp in
    select attname from pg_attribute where attrelid = parentoid and attnum >0 order by attnum loop
      i = i + 1;
      script = script || 'NEW.' || tmp.attname || case i when j then '' else ',' end;
      
      if (col_description (parentoid, i) ~* 'partition') and (check_field is null)
        then check_field = tmp.attname; end if;
    end loop;
    script = script || ')';
 
    
    check_condition =
    '(
       ' || check_field || ' >= ' || quote_literal (check_beg) || ' and
       ' || check_field || ' < ' || quote_literal (check_end) || '
     )';
 
    
    execute
    'create table ' || child || '
    (
     constraint partition' || suffix || ' check '
      || check_condition || '
    )
    inherits (' || parent || ')';
 
    
    execute
    'create rule route' || suffix || ' as '
      || ' on insert to ' || parent || ' where '
      || check_condition
      || ' do instead insert into ' || child
      || ' values (' || script;
 
    
    perform copy_constraints(parent, child);
    perform copy_indexes(parent, child);
    execute 'GRANT SELECT ON ' || child || ' TO xxx';
    execute 'GRANT ALL ON ' || child || ' TO zabbix';

    
  return child;
end;
$BODY$
LANGUAGE 'plpgsql'