move_chunk()
Move a chunk and its indexes to a different tablespace
TimescaleDB allows you to move data and indexes to different tablespaces. This allows you to move data to more cost-effective storage as it ages.
The move_chunk function acts like a combination of the
PostgreSQL CLUSTER command and
PostgreSQL ALTER TABLE...SET TABLESPACE commands. Unlike
these PostgreSQL commands, however, the move_chunk function uses lower lock
levels so that the chunk and hypertable are able to be read for most of the
process. This comes at a cost of slightly higher disk usage during the
operation. Writes to the chunk are blocked until the move completes. For a more detailed discussion of this capability, see the
documentation on managing storage with tablespaces.
If the chunk is in the columnstore, move_chunk does not reorder it and ignores reorder_index.
It takes an ACCESS EXCLUSIVE lock for the whole operation, so queries that read or write the chunk,
including queries on the hypertable that are not limited to other chunks, wait until the move completes.
To use move_chunk(), you must own the hypertable, or have the privileges of its owner,
and have the CREATE privilege on each destination tablespace that is not the database default.
Samples
Section titled “Samples”SELECT move_chunk( chunk => '_timescaledb_internal._hyper_1_4_chunk', destination_tablespace => 'tablespace_2', index_destination_tablespace => 'tablespace_3', reorder_index => 'conditions_device_id_time_idx', verbose => TRUE);Arguments
Section titled “Arguments”The syntax is:
SELECT move_chunk( chunk => '<chunk_name>', destination_tablespace => '<tablespace_name>', index_destination_tablespace => '<tablespace_name>', reorder_index => '<index_name>', verbose => true | false);| Name | Type | Default | Required | Description |
|---|---|---|---|---|
chunk | REGCLASS | - | ✔ | Name of chunk to be moved |
destination_tablespace | NAME | - | ✔ | Target tablespace for chunk being moved |
index_destination_tablespace | NAME | - | ✔ | Target tablespace for index associated with the chunk you are moving |
reorder_index | REGCLASS | - | ✖ | The name of the index (on either the hypertable or chunk) to order by. Required unless the chunk has been clustered before, in which case that index is used. Ignored for a chunk in the columnstore |
verbose | BOOLEAN | FALSE | ✖ | Setting to true displays messages about the progress of the move_chunk command. |
Returns
Section titled “Returns”This function returns void.