collectd-excerpt.conf

<Plugin postgresql>
	<Writer sqlstore>
		# See /usr/share/doc/collectd-core/examples/postgresql/collectd_insert.sql for details
		Statement "SELECT collectd_insert($1, $2, $3, $4, $5, $6, $7, $8, $9);"
		StoreRates true
	</Writer>
	<Database collectd>
		Host "REDACTED"
		Port "5433"
		User "REDACTED"
		Password "REDACTED"
		SSLMode "disable"
		Writer sqlstore
		# see collectd.conf(5) for details
		# CommitInterval 10
	</Database>
</Plugin>

collectd-postgresql.sql

CREATE TABLE identifiers (
    id serial primary key,
    host text NOT NULL,
    plugin text NOT NULL,
    plugin_instance text,
    type text NOT NULL,
    type_instance text,
    constraint unique_identifier unique(host, plugin, plugin_instance, type, type_instance)
);

create table metrics (
  identifier_id integer not null references identifiers(id),
  created_at timestamp without time zone not null,
  name text not null,
  measure double precision not null
) partition by range (created_at);

create index idx_metrics_identifier_id on metrics(identifier_id);
create index idx_metrics_created_at on metrics(created_at);
create index idx_metrics_name on metrics(name);

{% for i in range(365) %}
create table metrics_{{ i | days_from_today | time_format('%Y%m%d') }}
  partition of metrics
  for values from ('{{ i | days_from_today | time_format("%Y-%m-%d") }}') to ('{{ (i+1) | days_from_today | time_format("%Y-%m-%d") }}');
{% endfor %}


CREATE OR REPLACE FUNCTION collectd_insert(
    timestamp without time zone   -- timestamp
  , text                          -- hostname
  , text                          -- plugin
  , text                          -- plugin instance
  , text                          -- type
  , text                          -- type instance
  , text[]                        -- names (array)
  , text[]                        -- types of the measure (array)
  , double precision[]            -- measures (array)
  ) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    p_time alias for $1;
    p_host alias for $2;
    p_plugin alias for $3;
    p_plugin_instance alias for $4;
    p_type alias for $5;
    p_type_instance alias for $6;
    p_value_names alias for $7;
    -- don't use the type info; for 'StoreRates true' it's 'gauge' anyway
    -- p_type_names alias for $8;
    p_values alias for $9;
    ds_id integer;
    i integer;
BEGIN
    SELECT id INTO ds_id
        FROM identifiers
        WHERE host = p_host
            AND plugin = p_plugin
            AND COALESCE(plugin_instance, '') = COALESCE(p_plugin_instance, '')
            AND type = p_type
            AND COALESCE(type_instance, '') = COALESCE(p_type_instance, '');
    IF NOT FOUND THEN
        INSERT INTO identifiers (host, plugin, plugin_instance, type, type_instance)
            VALUES (p_host, p_plugin, p_plugin_instance, p_type, p_type_instance)
            RETURNING id INTO ds_id;
    END IF;
    i := 1;
    LOOP
        EXIT WHEN i > array_upper(p_value_names, 1);
        INSERT INTO metrics (identifier_id, created_at, name, measure)
            VALUES (ds_id, p_time, p_value_names[i], p_values[i]);
        i := i + 1;
    END LOOP;
END;
$$;


create or replace view view_metrics AS
  SELECT
    m.created_at
  , i.host
  , i.plugin
  , i.plugin_instance
  , i.type
  , i.type_instance
  , m.name
  , m.measure
  from metrics m
  left join identifiers i on i.id=m.identifier_id;