If you need backfill or update data in a compressed chunk, you should decompress the chunk first. Inserting data into a compressed chunk is more computationally expensive than inserting data into an uncompressed chunk, so decompressing the chunk is also a good idea if you need to backfill large amounts of data.
These are the main steps for decompressing chunks in preparation for inserting or backfilling data:
There are several methods for selecting chunks and decompressing them.
To decompress a single chunk by name, run this command:
To decompress a set of chunks based on a time range, you can use the output of
show_chunks to decompress each one:
SELECT decompress_chunk(i) from show_chunks('table_name', newer_than, older_than) i;
If you want to use more precise matching constraints, for example space partitioning, you can construct a command like this:
SELECT tableoid::regclass FROM metrics WHERE time = '2000-01-01' AND device_id = 1 GROUP BY tableoid; tableoid ------------------------------------------ _timescaledb_internal._hyper_72_37_chunk
When you backfill data, you are inserting data that has a timestamp in the past into a corresponding chunk that has already been compressed.
In this section, we explain how to backfill data into a temporary table. Temporary tables only exist for the duration of the database session, and then are automatically dropped, This is the simplest method for doing a large backfill operation.
If you backfill regularly, you might prefer to use a regular table instead, so
that multiple writers can insert into the table at the same time before the
decompress_backfill process. In this case, after you are done backfilling the
data, clean up by truncating your table in preparation for the next backfill, or
drop it completely.
If you need to insert a batch of backfilled data, the TimescaleDB
extras GitHub repository includes functions for
backfilling batch data to compressed chunks. In this procedure, we describe how to use the
example, and the data column is
CREATE TEMPORARY TABLE cpu_temp AS SELECT * FROM example WITH NO DATA;
CALL decompress_backfill(staging_table=>'cpu_temp', destination_hypertable=>'example');
If you don't want to use a supplied function, you can perform the steps manually. In this procedure, we describe how to identify and turn off your compression policy, before manually decompressing chunks.
job_idof the policy:
SELECT s.job_id FROM timescaledb_information.jobs j INNER JOIN timescaledb_information.job_stats s ON j.job_id = s.job_id WHERE j.proc_name = 'policy_compression' AND s.hypertable_name = <target table>;
SELECT alter_job(<job_id>, scheduled => false);
SELECT decompress_chunk(i) from show_chunks('conditions', newer_than, older_than) i;
UPDATEcommands to backfill the data.
SELECT alter_job(<job_id>, scheduled => true);
Found an issue on this page?Report an issue!