When you backfill data, you are inserting data into a chunk that has already been compressed. This section contains procedures for bulk backfilling, taking you through these steps:
- Temporarily turning off any existing compression policy. This stops the policy from trying to compress chunks that you are currently working on.
- Decompressing chunks.
- Performing the insertion or backfill.
- Re-enabling the compression policy. This re-compresses the chunks you worked on.
This section shows you how to bulk backfill data using a temporary table. Temporary tables only exist for the duration of the database session, and then are automatically dropped. 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. In this case, after you are done backfilling the data, clean up by truncating your table in preparation for the next backfill.
To make backfilling easier, you can use the
backfilling functions in the
TimescaleDB extras GitHub repository. In particular, the
decompress_backfill procedure automates many of the backfilling steps for you.
At the psql prompt, create a temporary table with the same schema as the hypertable you want to backfill into. In this example, the table is named
example, and the temporary table is named
cpu_temp:CREATE TEMPORARY TABLE cpu_temp AS SELECT * FROM example WITH NO DATA;
Insert your data into the temporary table.
decompress_backfillprocedure. This procedure halts the compression policy, identifies the compressed chunks that the backfilled data corresponds to, decompresses the chunks, inserts data from the backfill table into the main hypertable, and then re-enables the compression policy: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.
At the psql prompt, find the
job_idof the policy:SELECT j.job_idFROM timescaledb_information.jobs jWHERE j.proc_name = 'policy_compression'AND j.hypertable_name = <target table>;
Pause compression, to prevent the policy from trying to compress chunks that you are currently working on:SELECT alter_job(<job_id>, scheduled => false);
Decompress the chunks that you want to modify.SELECT decompress_chunk('_timescaledb_internal._hyper_2_2_chunk');
Repeat for each chunk. Alternatively, you can decompress a set of chunks based on a time range using
show_chunks:SELECT decompress_chunk(i)FROM show_chunks('conditions', newer_than, older_than) i;
When you have decompressed all the chunks you want to modify, perform the
UPDATEcommands to backfill the data.
Restart the compression policy job. The next time the job runs, it recompresses any chunks that were decompressed.SELECT alter_job(<job_id>, scheduled => true);
Alternatively, to recompress chunks immediately, use the
Found an issue on this page?Report an issue!