Use postgresql 10 native partitioning

[tahajahangir]

One on of the most important changes in Postgresql 10 is native (without triggers) partitioning support.

I think it's not so hard to port the sql files to work with this feature.

[vasekch]

Official PostgreSQL documentation, just for reference (and considerations): https://www.postgresql.org/docs/10/static/ddl-partitioning.html

To use declarative partitioning in this case, use the following steps:

1. Create table as a partitioned table by specifying the PARTITION BY clause...
2. Create partitions...
3. Create an index on the key column(s), as well as any other indexes you might want for every partition...
4. Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf...

The following limitations apply to partitioned tables:

• There is no facility available to create the matching indexes on all partitions automatically...
• Since primary keys are not supported on partitioned tables, foreign keys referencing partitioned tables are not supported, nor are foreign key references from a partitioned table to some other table.
• ...

[tahajahangir]

I personally use following schema to partition history/trend items:

CREATE TABLE public.history_uint (
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value numeric(20,0) NOT NULL DEFAULT (0)::numeric,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY LIST (get_history_bucket(clock, 110, 10));

CREATE OR REPLACE FUNCTION public.get_history_bucket(
    inp integer,
    days_ integer,
    days_in_bucket integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    IMMUTABLE 
AS $BODY$
BEGIN
  -- Return a bucket numbet (11 buckets: 0-10) that each bucket contains 10-day
  RETURN ((inp / 86400) % days_) / days_in_bucket;
END

This creates 11 buckets, each with 10 days of history (buckets are arrange in a round-robin cycle), with default 90-day storage period, hot buckets for insert/delete queries will be different.

[Doctorbal]

@tahajahangir would you mind sharing your SQL scripts for the rest of history/trends? What about the constraints and keys associated that can't be added to the root table?

Also I'm curious as to why you used lists over range with the clock epoch value which a lot of zabbix.org partitioning docs suggest?

PSQL 10 native partitioning is fairly new to me but would like to understand the best way to use this for Zabbix 3.4.8; your experience would help a lot.

[tahajahangir]

This is my sql scripts for partitioning: Note that if you partition over clock (I partitioned on get_history_bucket(clock, K*N, N)) you should create tables manually for all ranges in future (e.g. 2018-12...2019-08...). Also zabbix housekeeper deletes old entries, so there will be many empty partitions (2017-09 ...). But by using modula over clock, we can have a fixed number of partitions.

CREATE OR REPLACE FUNCTION public.get_history_bucket(
    inp integer,
    days_ integer,
    days_in_bucket integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    IMMUTABLE 
AS $BODY$
BEGIN
  -- Return a bucket numbet (11 buckets: 0-10) that each bucket contains 10-day
  RETURN ((inp / 86400) % days_) / days_in_bucket;
END
$BODY$;

CREATE OR REPLACE FUNCTION public.zabbix_partition_table(
    table_name text,
    days_ integer,
    days_in_bucket integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

DECLARE
  bucket_count        INTEGER;
  first_copy_limit    INTEGER;
  new_table_name      TEXT;
  temp_table_name     TEXT;
  orig_table_name     TEXT;
BEGIN
  bucket_count = (days_ + days_in_bucket - 1) / days_in_bucket;
  temp_table_name = table_name || '_tmp';
  orig_table_name = table_name || '_org';

  IF NOT EXISTS ( SELECT 0 FROM pg_class WHERE relname = (table_name || '_clock_idx') ) THEN
    RAISE NOTICE 'CREATE INDEX CONCURRENTLY ON % USING btree(clock);', TABLE_NAME;
    RAISE EXCEPTION 'No `clock` index on this table, create it with above command';
  END IF;

  EXECUTE 'CREATE TABLE ' || temp_table_name || ' (LIKE ' || table_name ||
          ' INCLUDING DEFAULTS) PARTITION BY LIST (get_history_bucket(clock, ' || days_ || ', ' || days_in_bucket ||
          '))';

  FOR i IN 0..(bucket_count - 1) LOOP
    new_table_name    := table_name || '_p' || i;
    EXECUTE 'CREATE TABLE ' || new_table_name || ' PARTITION OF ' || temp_table_name || ' FOR VALUES IN (' || i || ')';
  END LOOP;
  EXECUTE 'SELECT clock - 1000 FROM ' || table_name || ' ORDER BY clock DESC LIMIT 1' INTO first_copy_limit;

  RAISE NOTICE 'Tables created, inserting old data from: %', first_copy_limit;
  EXECUTE 'INSERT INTO ' || temp_table_name || ' SELECT * FROM ' || table_name || ' WHERE clock < ' || first_copy_limit;

  FOR i IN 0..(bucket_count - 1) LOOP
    new_table_name    := table_name || '_p' || i;
    RAISE NOTICE 'CREATE INDEX on %', new_table_name;
    EXECUTE 'CREATE INDEX ' || new_table_name || '_1 ON ' || new_table_name || ' USING btree (itemid, clock)';
  END LOOP;

  RAISE NOTICE 'ALTER TABLE % RENAME TO %', table_name, orig_table_name;
  EXECUTE 'ALTER TABLE ' || table_name || ' RENAME TO ' || orig_table_name;

  RAISE NOTICE 'Inserting remaining data';
  EXECUTE 'INSERT INTO ' || temp_table_name || ' SELECT * FROM ' || orig_table_name || ' WHERE clock >= ' ||
          first_copy_limit;

  RAISE NOTICE 'ALTER TABLE % RENAME TO %', temp_table_name, table_name;
  EXECUTE 'ALTER TABLE ' || temp_table_name || ' RENAME TO ' || table_name;
END
$BODY$;