Moving older data to a different tablespace can help you save on storage costs. Timescale 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.


On the Timescale cloud platform, use the Tiered Storage offering which handles this by providing a tiering policy API to move data to low-cost object storage backed by Amazon S3.

  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)
    AS $$
    ht REGCLASS;
    lag interval;
    destination_tablespace name;
    index_destination_tablespace name;
    reorder_index REGCLASS;
    chunk REGCLASS;
    tmp_name name;
    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, 'destination_tablespace') INTO STRICT destination_tablespace;
    SELECT jsonb_object_field_text (config, 'index_destination_tablespace') INTO STRICT index_destination_tablespace;
    SELECT jsonb_object_field_text (config, 'reorder_index') INTO STRICT reorder_index;
    IF ht IS NULL OR lag IS NULL OR destination_tablespace IS NULL THEN
    RAISE EXCEPTION 'Config must have hypertable, lag and destination_tablespace';
    END IF;
    IF index_destination_tablespace IS NULL THEN
    index_destination_tablespace := destination_tablespace;
    END IF;
    FOR chunk IN
    SELECT c.oid
    FROM pg_class AS c
    LEFT JOIN pg_tablespace AS t ON (c.reltablespace = t.oid)
    JOIN pg_namespace AS n ON (c.relnamespace = n.oid)
    JOIN (SELECT * FROM show_chunks(ht, older_than => lag) SHOW (oid)) AS chunks ON (chunks.oid::text = n.nspname || '.' || c.relname)
    WHERE t.spcname != destination_tablespace OR t.spcname IS NULL
    RAISE NOTICE 'Moving chunk: %', chunk::text;
    PERFORM move_chunk(
    chunk => chunk,
    destination_tablespace => destination_tablespace,
    index_destination_tablespace => index_destination_tablespace,
    reorder_index => reorder_index
  2. 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.

    SELECT add_job(
    config => '{"hypertable":"metrics","lag":"12 month","tablespace":"old_chunks"}'

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.


Found an issue on this page?Report an issue or Edit this page in GitHub.