A complicated scenario with TimescaleDB and compression

Question

We have a timescaleDB with a fairly large data set (> 1.5TB, >1B rows).

The project has been running into one delay after another because we just can't make the queries we need to perform fast enough (large scans over many rows to find specific conditions, etc.)

Recently, we've started to experiment with compression and saw that it had quite a significant impact on performance, but we've been running into a wall.

We record financial trades and, at the core, the trades have a timestamp, the symbol being transacted and various other info.

There are roughly 140 different symbols, and each of them run independently of one another. They don't know about each another.

All transactions end up in a single table. We can't have multiple tables because some queries require access to the different symbols at once.

The chunks are 1 day long, and we write a whole day at once.

Since we process the symbols separately, sometimes symbol A may be writing day 2, while symbol B writes day 5.

This works very well until compression gets enabled:

Each chunk represents one day of ALL symbols, so if a chunk gets compressed while one symbol hasn't been written yet, the write operation will fail.

We can't manually 'close' a day/chunk because some data for some symbol may arrive a few days later or some days may be empty, so there is no way to determine that a day is closed.

So my question is: how can we partition things knowing that:

• we want to use compression.
• we need access to all symbols within a single query, so we can't have many tables.
• we can't determine when the daily data for a symbol will arrive; it's not uncommon to have 3-4 days difference between symbols.

If we could create a chunk per timestamp and per symbol, it would work, but I haven't found anything indicating that it would be possible.

---

Edit:

I've experimented with partitioning the hypertable, but this doesn't provide the expected result.

Let's take one thread:

• it gets live data that gets written as it comes.
• it gets authoritative data by blocks of 24h. It is sequential (days arrive in order) and can be a bit old (sometimes it takes a few days to get an update), but it has to replace whatever is in the table, since it takes precedence over other data. Prior to inserting the day into the database, it'll erase the whole day in question, which can be populated from the live data.
• I have compression set so that it will not compress the last 3 days of data, but everything older

So, in theory, I'm writing live data and trusted data arrives in 24h blocks, sometimes a few days later and overwrites everything on its way. These blocks can be compressed.

Now, there are about 140 of these threads, each representing a symbol, and while symbol A may be on day 5, symbol B can be on day 3 and since the chunks represent a day, I can only compress days 0, 1 and 2.

I was hoping that partitionning with:

SELECT create_hypertable(
    'exchange.{tableName}',
    'ts',
    partitioning_column => 'ticker',
    number_partitions => 150,
    create_default_indexes => false,
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE);

would create one chunk per symbol ('ticker') per day. So symbol A could have its own compression, symbol B could have its own compression, etc. because each symbol has its own chunk / day, and I wouldn't have to look at the most laggy symbol to decide where compression stops.

But this doesn't seem to be the case.

Is there any solution for this?

Answer 1

The Timescale compression docs contain info, how you could decompress chunks manually and the "Future Work" part (at the bottom) indicates that future versions may be able to insert old data automatically.

In the meantime, you may want to choose a meaningful compression duration: e.g. when you expect most data to arrive within 4 days, compress data older than 5 days).

And when some late data arrives, write it to a separate table (with the same structure). Since this is the exceptional case, the table will only contain little data, so maybe you don't even need a hypertable.

When you query your data you must now combine the data of both tables to get the desired result.