Use a user-defined action to implement automatic data tiering

Using a user-defined action to implement automatic data tiering

1. Create a procedure that moves chunks to a different tablespace if they contain data older than the lag parameter.

   CREATE OR REPLACE PROCEDURE move_chunks (job_id int, config jsonb)
   LANGUAGE PLPGSQL
   AS $$
   DECLARE
     ht REGCLASS;
     lag interval;
     destination name;
     chunk REGCLASS;
   BEGIN
     SELECT jsonb_object_field_text (config, 'hypertable')::regclass INTO STRICT ht;
     SELECT jsonb_object_field_text (config, 'lag')::interval INTO STRICT lag;
     SELECT jsonb_object_field_text (config, 'tablespace') INTO STRICT destination;
   
     IF ht IS NULL OR lag IS NULL OR destination IS NULL THEN
       RAISE EXCEPTION 'Config must have hypertable, lag and destination';
     END IF;
   
     FOR chunk IN
     SELECT show.oid
     FROM show_chunks(ht, older_than => lag)
     SHOW (oid)
       INNER JOIN pg_class pgc ON pgc.oid = show.oid
       INNER JOIN pg_tablespace pgts ON pgts.oid = pgc.reltablespace
     WHERE pgts.spcname != destination
     LOOP
       RAISE NOTICE 'Moving chunk: %', chunk::text;
       EXECUTE format('ALTER TABLE %s SET TABLESPACE %I;', chunk, destination);
     END LOOP;
   END
   $$;

   Copy

2. Register the job to run daily. In the config, set hypertable to metrics to implement data tiering on the metrics hypertable. Set lag to 12 months to move chunks containing data older than 12 months. Set tablespace to the destination tablespace.

   SELECT add_job(
     'move_chunks',
     '1d',
     config => '{"hypertable":"metrics","lag":"12 month","tablespace":"old_chunks"}'
   );

   Copy