Downsample and compress

Action that downsamples and compresses chunks on hypertable metrics older than a certain age. The example query computes a simple avg over hourly data for downsampling, but this query can be arbitrarily complex.

  1. CREATE OR REPLACE PROCEDURE downsample_compress (job_id int, config jsonb)
  2. LANGUAGE PLPGSQL
  3. AS $$
  4. DECLARE
  5.   lag interval;
  6.   chunk REGCLASS;
  7.   tmp_name name;
  8. BEGIN
  9.   SELECT jsonb_object_field_text (config, 'lag')::interval INTO STRICT lag;
  11.   IF lag IS NULL THEN
  12.     RAISE EXCEPTION 'Config must have lag';
  13.   END IF;
  15.   FOR chunk IN
  16.     SELECT show.oid
  17.     FROM show_chunks('metrics', older_than => lag) SHOW (oid)
  18.       INNER JOIN pg_class pgc ON pgc.oid = show.oid
  19.       INNER JOIN pg_namespace pgns ON pgc.relnamespace = pgns.oid
  20.       INNER JOIN timescaledb_information.chunks chunk ON chunk.chunk_name = pgc.relname
  21.         AND chunk.chunk_schema = pgns.nspname
  22.     WHERE chunk.is_compressed::bool = FALSE
  23.   LOOP
  24.     RAISE NOTICE 'Processing chunk: %', chunk::text;
  26.     -- build name for temp table
  27.     SELECT '_tmp' || relname
  28.     FROM pg_class
  29.     WHERE oid = chunk INTO STRICT tmp_name;
  31.     -- copy downsampled chunk data into temp table
  32.     EXECUTE format($sql$ CREATE UNLOGGED TABLE %I AS
  33.       SELECT time_bucket('1h', time), device_id, avg(value) FROM %s GROUP BY 1, 2;
  34.     $sql$, tmp_name, chunk);
  36.     -- clear original chunk
  37.     EXECUTE format('TRUNCATE %s;', chunk);
  39.     -- copy downsampled data back into chunk
  40.     EXECUTE format('INSERT INTO %s(time, device_id, value) SELECT * FROM %I;', chunk, tmp_name);
  42.     -- drop temp table
  43.     EXECUTE format('DROP TABLE %I;', tmp_name);
  45.     PERFORM compress_chunk (chunk);
  47.     COMMIT;
  48.   END LOOP;
  49. END
  50. $$;

Register job to run daily downsampling and compressing chunks older than 12 months.

  1. SELECT add_job('downsample_compress','1d', config => '{"lag":"12 month"}');