Skip to content

move_chunk()

Move a chunk and its indexes to a different tablespace

Since 1.3.0

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.

Important

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.

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
);

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
);
NameTypeDefaultRequiredDescription
chunkREGCLASS-✔Name of chunk to be moved
destination_tablespaceNAME-✔Target tablespace for chunk being moved
index_destination_tablespaceNAME-✔Target tablespace for index associated with the chunk you are moving
reorder_indexREGCLASS-✖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
verboseBOOLEANFALSE✖Setting to true displays messages about the progress of the move_chunk command.

This function returns void.