Skip to content

Instantly share code, notes, and snippets.

@basilfx

basilfx/collectd_metrics_timescale.sql

Last active June 21, 2021 08:23
Show Gist options
  • Save basilfx/aa858a4c69ec3bb44c98061a50c63376 to your computer and use it in GitHub Desktop.
Save basilfx/aa858a4c69ec3bb44c98061a50c63376 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
collectd_metrics_timescale.sql
CREATE TABLE IF NOT EXISTS collectd_metrics (
time TIMESTAMPTZ NOT NULL,
plugin TEXT,
plugin_instance TEXT,
type_instance TEXT,
type TEXT,
host TEXT,
metric TEXT,
value DOUBLE PRECISION
);
SELECT create_hypertable('collectd_metrics', 'time', chunk_time_interval => interval '1 day');
ALTER TABLE collectd_metrics SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'plugin',
timescaledb.compress_orderby = 'time'
);
SELECT add_compression_policy('collectd_metrics', INTERVAL '7 days');
SELECT add_retention_policy('collectd_metrics', INTERVAL '90 days');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment