2

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?

5
  • What is wrong with create_hypertable() with more than two arguments?
    – jjanes
    Jun 28, 2022 at 23:40
  • If you don't know when the data will show up, how do you know when you can query for it without getting the wrong result? Why can't you just compress every partition on the 6th day, if 5 is the largest delay?
    – jjanes
    Jun 28, 2022 at 23:42
  • Have you tried a custom partitioning_func?
    – jonatasdp
    Jun 29, 2022 at 11:21
  • @jjanes, I didn't realize you could partition them. I spent so much time around compression, I forgot the very first step. Will each partition have its own independent chunks?
    – Thomas
    Jun 29, 2022 at 13:40
  • @jjanes, whenever there is a hole in the data, we consider that segment as not valid yet
    – Thomas
    Jun 29, 2022 at 13:41

1 Answer 1

0

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.

1
  • I looked at waiting for all chunks to be there and then run manual compression, but when we get a batch of data is a bit random, so the most late data is holding compression for everything else
    – Thomas
    Jun 29, 2022 at 13:42

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.