3

For PostgreSQL (v13.4) with TimescaleDB (v2.4.1)

Created a hyper table, with each day as a chunk, and I found a day in last month is missing, so I've inserted those data of the missing day into the old chunk with simple insert statement.

No compression is enabled for the hypertable.

The questions are:

  • Is this backfill operation fine? Probably yes, I guess, just try to confirm.
  • Is there anything else I should / better do, after the backfilling, to make it correct or optimized?

BTW, I've read the official doc of TimescaleDB, only found a topic about backfilling for compressed chunk, which is different from the uncompressed chunk, I guess.

Thanks.

1 Answer 1

4

The backfill itself might be slower than insertion of new data normally is because the indexes needing maintenance might not be in cache to start with. And then once it is done then insertions into the current chunk may also be slower for a while while the evicted cache is repopulated.

And once backfilled, the clustering of the old chunk on its time column will be permanently lower than usual (unless you run a CLUSTER command to restore it). So queries depending on good clustering will be slower than otherwise.

The importance of either one of there is hard to predict, but will probably be fairly small.

2
  • So, the answer mean: it works, though it may cause query be slower for a while, and even for long term for cluster.
    – Eric
    Oct 31, 2021 at 16:58
  • And if I don't have a cluster yet, the long term (ignoring the cache status) performance won't be effected right?
    – Eric
    Oct 31, 2021 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.