Improve storage performance using tablespaces

Store database objects in specific physical locations on disk using tablespaces. Learn how tablespaces work for TimescaleDB hypertable chunks and how to automate tablespace management

You use tablespaces to determine the physical location of the tables and indexes in your database. In most cases, you want to use faster storage to store data that is accessed frequently, and slower storage for data that is accessed less often.

s consist of a number of s, and each can be located in a specific tablespace. This allows you to grow your s across many disks. When you create a new , a tablespace is automatically selected to store the ‘s data.

You can attach and detach tablespaces on a . When a disk runs out of space, you can detach the full tablespace from the , and than attach a tablespace associated with a new disk. To see the tablespaces for you , use the show_tablespaces command.

How hypertable chunks are assigned tablespaces

A can be partitioned in multiple dimensions, but only one of the dimensions is used to determine the tablespace assigned to a particular . If a has one or more hash-partitioned, or space, dimensions, it uses the first hash-partitioned dimension. Otherwise, it uses the first time dimension.

This strategy ensures that hash-partitioned s have s co-located according to hash partition, as long as the list of tablespaces attached to the remains the same. Modulo calculation is used to pick a tablespace, so there can be more partitions than tablespaces. For example, if there are two tablespaces, partition number three uses the first tablespace.

s that are only time-partitioned add new partitions continuously, and therefore have s assigned to tablespaces in a way similar to round-robin.

It is possible to attach more tablespaces than there are partitions for the . In this case, some tablespaces remain unused until others are detached or additional partitions are added. This is especially true for hash-partitioned tables.

Automatic tablespace management

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

On , use tiered storage which handles this by providing a tiering policy API to move data to low-cost object storage backed by Amazon S3.

To implement automatic moving with a :

1. Create a procedure that moves chunks to a different tablespace

   This procedure moves s 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_tablespace name;
      index_destination_tablespace name;
      reorder_index REGCLASS;
      chunk REGCLASS;
      tmp_name name;
   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, '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
    LOOP
      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
      );
    END LOOP;
   END
   $$;
   

2. Register the job to run daily

   In the config, set hypertable to metrics to implement automatic moves on the metrics . Set lag to 12 months to move s containing data older than 12 months. Set tablespace to the destination tablespace.

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