Are out of order inserts an issue?

Question

I am running a PostgreSQL database that is logging state-of-health information for a number of systems. At the moment, it is running on a standard PostgreSQL server, but it seems like TimescaleDB has some usable features for this setup.

Most of the systems are logged directly, so the data are stored just after it has been collected. But some of the systems are not always on-line. They will log data locally when they are off-line, then when they are on-line, the collected data will be downloaded and inserted into the database. Will this pattern cause any problems for TimescaleDB? In some of the documentation, it seems to be important that data are written in time order.

Answer 1

Timescale splits the data into "child table" called chunks across the time.

The chunks are based on time interval, and you can choose the time interval.

If you want to compress your data, you can add compression policies that can run every X hours to compress the data older than X time interval.

The problem can come if systems get off-line for more than the X time interval that you're start compressing.

Let's say you have chunks of 1 day and you compress data after 7 days. If your system gets off-line for 8 days, it will try to insert data on compressed chunks then it can become an issue. You'll need to decompress the chunk to insert the data and recompress later.

If this is a common usage of your systems, you can choose for smaller chunks like 4 hours and then it will be smaller amounts of data that can be easily decompressed and recompressed again. The compression will not be that great because it will have too many chunks but that is the trade off the decisions :)