Manual compression

In most cases, an automated compression policy is sufficient. However, if you want more control over compression, you can also manually compress specific chunks.

warning

Compression alters data on your disk, so always back up before you start!

Compress chunks manually

Before you start, you need a list of chunks to compress. In this example, we are using a hypertable called example, and compressing these chunks that are older than three days.

Procedure: Selecting chunks to compress

  1. At the psql prompt, select all chunks in the table example that are older than three days:

    SELECT show_chunks('example', older_than => INTERVAL '3 days');
  2. This returns a list of chunks. Take a note of the chunk names:

    show_chunks
    1_timescaledb_internal_hyper_1_2_chunk
    2_timescaledb_internal_hyper_1_3_chunk

When you are happy with the list of chunks, you can use the chunk names to manually compress each one.

Procedure: Compressing chunks manually

  1. At the psql prompt, compress the chunk:

    SELECT compress_chunk( '<chunk_name>');
  2. Check the results of the compression with this command:

    SELECT *
    FROM chunk_compression_stats('example');

    The results show the chunks for the given hypertable, their compression status, and some other statistics:

    chunk_schemachunk_namecompression_statusbefore_compression_table_bytesbefore_compression_index_bytesbefore_compression_toast_bytesbefore_compression_total_bytesafter_compression_table_bytesafter_compression_index_bytesafter_compression_toast_bytesafter_compression_total_bytesnode_name
    _timescaledb_internal_hyper_1_1_chunkCompressed8192 bytes16 kB8192 bytes32 kB8192 bytes16 kB8192 bytes32 kB
    _timescaledb_internal_hyper_1_20_chunkUncompressed
  3. Repeat for all chunks you want to compress.

Manually compress chunks in a single command

Alternatively, you can select the chunks and compress them in a single command by using the output of the show_chunks command to compress each one, like this:

SELECT compress_chunk(i) from show_chunks('example', now() - interval '1 week', now() - interval '3 weeks') i;

Found an issue on this page?

Report an issue!

Keywords

Related Content