> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

export const JOB = 'job';

export const HYPERTABLE = 'hypertable';

export const CHUNK = 'chunk';

export const CLOUD_LONG = 'Tiger Cloud';

export const TIMESCALE_DB = 'TimescaleDB';

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.

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

You can attach and detach tablespaces on a {HYPERTABLE}. When a disk runs out of space, you can
[detach][detach_tablespace] the full tablespace from the {HYPERTABLE}, and than [attach][attach_tablespace] a
tablespace associated with a new disk. To see the tablespaces for you {HYPERTABLE}, use the
[`show_tablespaces`][show_tablespaces] command.

## How hypertable chunks are assigned tablespaces

A {HYPERTABLE} can be partitioned in multiple dimensions, but only one of the dimensions is used to determine the
tablespace assigned to a particular {HYPERTABLE} {CHUNK}. If a {HYPERTABLE} 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 {HYPERTABLE}s have {CHUNK}s co-located according to hash partition, as
long as the list of tablespaces attached to the {HYPERTABLE} 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.

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

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

## Automatic tablespace management

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

<Note>
  On {CLOUD_LONG}, use [tiered storage][tiered-storage] which handles this by providing a [tiering policy API][tiering-policy-api] to move data to low-cost object storage backed by Amazon S3.
</Note>

To implement automatic {CHUNK} moving with a {JOB}:

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

   This procedure moves {CHUNK}s to a different tablespace if they contain data older than the `lag` parameter.

   ```sql theme={"dark"}
   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 {CHUNK} moves on the `metrics` {HYPERTABLE}. Set `lag` to 12 months to move {CHUNK}s containing data older than 12 months. Set `tablespace` to the destination tablespace.

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

[tiered-storage]: https://www.tigerdata.com/docs/use-timescale/latest/data-tiering/

[tiering-policy-api]: https://www.tigerdata.com/docs/use-timescale/latest/data-tiering/enabling-data-tiering/#add-a-tiering-policy

[attach_tablespace]: /api-reference/timescaledb/hypertables/attach_tablespace

[detach_tablespace]: /api-reference/timescaledb/hypertables/detach_tablespace

[show_tablespaces]: /api-reference/timescaledb/hypertables/show_tablespaces