Use a user-defined action to implement automatic tablespace management

Moving older data to a different tablespace can help you save on storage costs. TimescaleDB supports automatic tablespace management by providing the move_chunk function to move chunks between tablespaces. To schedule the moves automatically, you can write a user-defined action.

Using a user-defined action to implement automatic chunk moving

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

``` 1. CREATE OR REPLACE PROCEDURE move_chunks (job_id int, config jsonb)

  1. LANGUAGE PLPGSQL

  2. AS $$

  3. DECLARE

  4. ht REGCLASS;

  5. lag interval;

  6. destination name;

  7. chunk REGCLASS;

  8. BEGIN

  9. SELECT jsonb_object_field_text (config, 'hypertable')::regclass INTO STRICT ht;

  10. SELECT jsonb_object_field_text (config, 'lag')::interval INTO STRICT lag;

  11. SELECT jsonb_object_field_text (config, 'tablespace') INTO STRICT destination;

  12. IF ht IS NULL OR lag IS NULL OR destination IS NULL THEN

  13. RAISE EXCEPTION 'Config must have hypertable, lag and destination';

  14. END IF;

  15. FOR chunk IN

  16. SELECT show.oid

  17. FROM show_chunks(ht, older_than => lag)

  18. SHOW (oid)

  19. INNER JOIN pg_class pgc ON pgc.oid = show.oid

  20. INNER JOIN pg_tablespace pgts ON pgts.oid = pgc.reltablespace

  21. WHERE pgts.spcname != destination

  22. LOOP

  23. RAISE NOTICE 'Moving chunk: %', chunk::text;

  24. EXECUTE format('ALTER TABLE %s SET TABLESPACE %I;', chunk, destination);

  25. END LOOP;

  26. END

  27. $$; ```

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

``` 1. SELECT add_job(

  1. 'move_chunks',

  2. '1d',

  3. config => '{"hypertable":"metrics","lag":"12 month","tablespace":"old_chunks"}'

  4. ); ```

note

This procedure uses PostgreSQL’s regular ALTER TABLE ... SET TABLESPACE syntax to move chunks. You could also write the procedure using TimescaleDB’s move_chunk function. The move_chunk function reorders the data as part of the move, which makes subsequent queries faster. It also requires lower lock levels, so the chunk remains available for reads during the move.