Very Slow Updates on Compressed Chunks when using Joins

TimescaleDB and PostgreSQL
1

Hi,

We have major performance issues with updating rows in compressed chunks.
We have a strong requirement to be able to update older, already compress data (should happen very rarely though). We don’t want to decompress the whole chunk in such case but only update relevant rows.
Until now we have been using TimescaleDB 2.15.3 on PostgreSQL 14. We just upgraded TimescaleDB to the latest version 2.16.0 (which should significantly improve UPSERT speed in compressed chunks), but we don’t notice any difference.

Our hypertable:

CREATE TABLE IF NOT EXISTS dbo.result_double (
               mprt_fk INTEGER NOT NULL,
               result_timestamp TIMESTAMPTZ NOT NULL,
               result_value DOUBLE PRECISION NOT NULL,
               status INTEGER NOT NULL,
               CONSTRAINT pk_result_double PRIMARY KEY (mprt_fk, result_timestamp)
    );

SELECT create_hypertable('dbo.result_double', 'result_timestamp', chunk_time_interval => interval '1 week', if_not_exists => TRUE);

ALTER TABLE dbo.result_double SET (timescaledb.compress, timescaledb.compress_orderby = 'result_timestamp DESC', timescaledb.compress_segmentby = 'mprt_fk');

-- auto compression is disabled, chunks are compressed manually

We have 8 chunks, 4 of them are already compressed. We noticed that updates work very fast if the rows to be updated are not yet compressed. We update rows in hypertable dbo.result_double from a temporary table update_table:

CREATE TEMP TABLE IF NOT EXISTS update_table (
		mprt_fk INTEGER,
		result_timestamp TIMESTAMPTZ,
		new_result_value DOUBLE PRECISION,
		new_status INTEGER,
		old_result_value DOUBLE PRECISION,
		old_status INTEGER
	) ;

Update statement:

EXPLAIN (VERBOSE, COSTS, SETTINGS, BUFFERS)  
    UPDATE dbo.result_double
    SET
        result_value = t.new_result_value,
        status = t.new_status
    FROM update_table AS t
    WHERE
        result_double.mprt_fk = t.mprt_fk
        AND result_double.result_timestamp = t.result_timestamp
        -- To make chunk pruning work
        AND result_double.result_timestamp >= '2023-02-02 00:00:00+01'
        AND result_double.result_timestamp <= '2023-02-02 06:00:00+01';

When updating data in not compressed chunks it works very fast, below is the EXPLAIN plan:

Custom Scan (HypertableModify)  (cost=0.00..3613.62 rows=1 width=28)
  ->  Update on dbo.result_double  (cost=0.00..3613.62 rows=1 width=28)
        Update on _timescaledb_internal._hyper_102_203_chunk result_double_1
        Update on _timescaledb_internal._hyper_102_226_chunk result_double_2
        ->  Nested Loop  (cost=0.00..3613.62 rows=1 width=28)
              Output: t.new_result_value, t.new_status, t.ctid, result_double.tableoid, result_double.ctid
              ->  Seq Scan on pg_temp_15.update_table t  (cost=0.00..22.70 rows=1270 width=30)
                    Output: t.new_result_value, t.new_status, t.ctid, t.mprt_fk, t.result_timestamp
              ->  Append  (cost=0.00..2.81 rows=2 width=22)
                    ->  Seq Scan on _timescaledb_internal._hyper_102_203_chunk result_double_1  (cost=0.00..0.00 rows=1 width=22)
                          Output: result_double_1.mprt_fk, result_double_1.result_timestamp, result_double_1.tableoid, result_double_1.ctid
                          Filter: ((result_double_1.result_timestamp >= '2023-02-02 00:00:00+01'::timestamp with time zone) AND (result_double_1.result_timestamp <= '2023-02-02 06:00:00+01'::timestamp with time zone) AND (t.mprt_fk = result_double_1.mprt_fk) AND (t.result_timestamp = result_double_1.result_timestamp))
                    ->  Index Scan using 226_215_pk_result_double on _timescaledb_internal._hyper_102_226_chunk result_double_2  (cost=0.57..2.80 rows=1 width=22)
                          Output: result_double_2.mprt_fk, result_double_2.result_timestamp, result_double_2.tableoid, result_double_2.ctid
                          Index Cond: ((result_double_2.mprt_fk = t.mprt_fk) AND (result_double_2.result_timestamp = t.result_timestamp) AND (result_double_2.result_timestamp >= '2023-02-02 00:00:00+01'::timestamp with time zone) AND (result_double_2.result_timestamp <= '2023-02-02 06:00:00+01'::timestamp with time zone))
Settings: effective_cache_size = '193302MB', effective_io_concurrency = '256', max_parallel_workers = '32', max_parallel_workers_per_gather = '16', random_page_cost = '1.1', work_mem = '200618kB'
Query Identifier: 414143609897445276
Planning:
  Buffers: shared hit=110

But when we want to update data in already compressed chunks the operation does not finish, not even in 10 mins. This happens also if we want to update only 1 row. Explain plan:

Custom Scan (HypertableModify)  (cost=30.89..40686.19 rows=1 width=28)
  ->  Update on dbo.result_double  (cost=30.89..40686.19 rows=1 width=28)
        Update on _timescaledb_internal._hyper_102_197_chunk result_double_1
        ->  Nested Loop  (cost=30.89..40686.19 rows=1 width=28)
              Output: t.new_result_value, t.new_status, t.ctid, result_double_1.tableoid, result_double_1.ctid
              ->  Seq Scan on pg_temp_15.update_table t  (cost=0.00..22.70 rows=1270 width=30)
                    Output: t.new_result_value, t.new_status, t.ctid, t.mprt_fk, t.result_timestamp
              ->  Bitmap Heap Scan on _timescaledb_internal._hyper_102_197_chunk result_double_1  (cost=30.89..32.01 rows=1 width=22)
                    Output: result_double_1.mprt_fk, result_double_1.result_timestamp, result_double_1.tableoid, result_double_1.ctid
                    Recheck Cond: ((result_double_1.result_timestamp = t.result_timestamp) AND (result_double_1.result_timestamp >= '2023-01-02 00:00:00+01'::timestamp with time zone) AND (result_double_1.result_timestamp <= '2023-01-02 06:00:00+01'::timestamp with time zone))
                    Filter: (t.mprt_fk = result_double_1.mprt_fk)
                    ->  Bitmap Index Scan on _hyper_102_197_chunk_result_double_result_timestamp_idx  (cost=0.00..30.89 rows=1 width=0)
                          Index Cond: ((result_double_1.result_timestamp = t.result_timestamp) AND (result_double_1.result_timestamp >= '2023-01-02 00:00:00+01'::timestamp with time zone) AND (result_double_1.result_timestamp <= '2023-01-02 06:00:00+01'::timestamp with time zone))
Settings: effective_cache_size = '193302MB', effective_io_concurrency = '256', max_parallel_workers = '32', max_parallel_workers_per_gather = '16', random_page_cost = '1.1', work_mem = '200618kB'
Query Identifier: 414143609897445276
Planning:
  Buffers: shared hit=218

On the other hand “regular” updates that do not join other table work fast, example:

Query:


EXPLAIN (VERBOSE, COSTS, SETTINGS, BUFFERS)  
UPDATE dbo.result_double SET STATUS = 1 where mprt_fk = 1115 and result_timestamp >= '2023-01-05 00:00:00+01' and  result_timestamp < '2023-01-08 09:00:00+01';

Explain plan:

Custom Scan (HypertableModify)  (cost=0.54..103.05 rows=998 width=14)
  ->  Update on dbo.result_double  (cost=0.54..103.05 rows=998 width=14)
        Update on _timescaledb_internal._hyper_102_197_chunk result_double_1
        Update on _timescaledb_internal._hyper_102_198_chunk result_double_2
        ->  Result  (cost=0.54..103.05 rows=998 width=14)
              Output: 1, result_double.tableoid, result_double.ctid
              ->  Append  (cost=0.54..93.07 rows=998 width=10)
                    ->  Index Scan using _hyper_102_197_chunk_result_double_result_timestamp_idx on _timescaledb_internal._hyper_102_197_chunk result_double_1  (cost=0.54..30.26 rows=1 width=10)
                          Output: result_double_1.tableoid, result_double_1.ctid
                          Index Cond: ((result_double_1.result_timestamp >= '2023-01-05 00:00:00+01'::timestamp with time zone) AND (result_double_1.result_timestamp < '2023-01-08 09:00:00+01'::timestamp with time zone))
                          Filter: (result_double_1.mprt_fk = 1115)
                    ->  Seq Scan on _timescaledb_internal._hyper_102_198_chunk result_double_2  (cost=0.00..57.81 rows=997 width=10)
                          Output: result_double_2.tableoid, result_double_2.ctid
                          Filter: ((result_double_2.result_timestamp >= '2023-01-05 00:00:00+01'::timestamp with time zone) AND (result_double_2.result_timestamp < '2023-01-08 09:00:00+01'::timestamp with time zone) AND (result_double_2.mprt_fk = 1115))
Settings: effective_cache_size = '193302MB', effective_io_concurrency = '256', max_parallel_workers = '32', max_parallel_workers_per_gather = '16', random_page_cost = '1.1', work_mem = '200618kB'
Query Identifier: 99819599961504784
Planning:
  Buffers: shared hit=146

Any help would be highly appreciated

2

Hi Marcel, do you see any locks going on in background?

My guess is that result_timestamp >= '2023-01-05 00:00:00+01' and result_timestamp < '2023-01-08 09:00:00+01' is going over multiple chunks and causing some kind of lock between the work and starving in somehow.

Have you tried anything minimal like update day by day or week by week instead of getting update all 3 months at once?

Let’s see if @sven has any insights to share. This is first time I see Custom Scan (HypertableModify) in the output.

1 Like
3

Hi,

Thank you for your reply.
Date range filter isn’t going over multiple chunks in most cases the range is within 1 day (in the example above is 3 days but still within the same chunk), chunk pruning works.
I noticed that whenever I update data (doesn’t matter which chunk) 3 RowExclusiveLock are created for ALL chunks which seems very strange. Below is an example for locks on one of the chunks (not the one being updated):

image
image1453×195 26.4 KB

But as I understand this is a known limitation which probably hasn’t been solved yet? (I found an older topic on GitHub: Update/delete of current chunk data blocked during compression of old data (reproducible on test data from official documentation) · Issue #4432 · timescale/timescaledb · GitHub. This now also relates to a different problem that we also have that we cannot write in the latest chunk (where every row is first inserted and then updated) and compress older chunks at the same time because deadlocks happen (if we would only insert this would not happen, updates make locks on other chunks):

ERROR: Process 1832588 (compression) waits for AccessExclusiveLock on relation 2365510283 of database 17094; blocked by process 1832720. Process 1832720 waits for RowExclusiveLock on relation 2365510283 of database 17094; blocked by process 1832588.deadlock detected ERROR: deadlock detected SQL state: 40P01 Detail: Process 1832588 waits for AccessExclusiveLock on relation 2365510283 of database 17094; blocked by process 1832720. Process 1832720 waits for RowExclusiveLock on relation 2365510283 of database 17094; blocked by process 1832588. Hint: See server log for query details.

But if I return back to the original problem in this topic: slow updates on compressed data when doing bulk update from other table (join). I noticed that the same query works better if I use a concrete mprt_fk (this is of course just for testing, doesn’t solve our problem):

    UPDATE dbo.result_double
    SET
        result_value = t.new_result_value,
        status = t.new_status
    FROM update_table AS t
    WHERE
        result_double.mprt_fk = 1111 --t.mprt_fk
        AND result_double.result_timestamp = t.result_timestamp
        -- Without this date range join filter chunk pruning doesn't work
        AND result_double.result_timestamp >= '2023-01-02 03:00:00+01'
        AND result_double.result_timestamp <= '2023-01-02 05:00:00+01';

Explain plan:

Custom Scan (HypertableModify)  (cost=10.41..14677.54 rows=45689 width=28)
  ->  Update on dbo.result_double  (cost=10.41..14677.54 rows=45689 width=28)
        Update on _timescaledb_internal._hyper_102_197_chunk result_double_1
        ->  Nested Loop  (cost=10.41..14677.54 rows=45689 width=28)
              Output: t.new_result_value, t.new_status, t.ctid, result_double_1.tableoid, result_double_1.ctid
              ->  Seq Scan on pg_temp_8.update_table t  (cost=0.00..22.70 rows=1270 width=26)
                    Output: t.new_result_value, t.new_status, t.ctid, t.result_timestamp
              ->  Bitmap Heap Scan on _timescaledb_internal._hyper_102_197_chunk result_double_1  (cost=10.41..11.53 rows=1 width=18)
                    Output: result_double_1.result_timestamp, result_double_1.tableoid, result_double_1.ctid
                    Recheck Cond: ((result_double_1.result_timestamp = t.result_timestamp) AND (result_double_1.result_timestamp >= '2023-01-02 03:00:00+01'::timestamp with time zone) AND (result_double_1.result_timestamp <= '2023-01-02 05:00:00+01'::timestamp with time zone))
                    Filter: (result_double_1.mprt_fk = 1111)
                    ->  Bitmap Index Scan on _hyper_102_197_chunk_result_double_result_timestamp_idx  (cost=0.00..10.41 rows=1 width=0)
                          Index Cond: ((result_double_1.result_timestamp = t.result_timestamp) AND (result_double_1.result_timestamp >= '2023-01-02 03:00:00+01'::timestamp with time zone) AND (result_double_1.result_timestamp <= '2023-01-02 05:00:00+01'::timestamp with time zone))
Settings: effective_cache_size = '193302MB', effective_io_concurrency = '256', max_parallel_workers = '32', max_parallel_workers_per_gather = '16', random_page_cost = '1.1', work_mem = '200618kB'
Query Identifier: 6373572558673308133
Planning:
  Buffers: shared hit=133 dirtied=1

It also works better when using UPSERT instead of UPDATE (when writing to compressed chunks):

  INSERT INTO dbo.result_double (mprt_fk, result_timestamp, result_value, status)
    SELECT 
        t.mprt_fk,
        t.result_timestamp,
        t.new_result_value,
        t.new_status
    FROM 
        update_table AS t    
    ON CONFLICT (mprt_fk, result_timestamp) 
    DO UPDATE SET
        result_value = EXCLUDED.result_value,
        status = EXCLUDED.status;

Expain plan:

Custom Scan (HypertableModify)  (cost=0.00..22.70 rows=1270 width=24)
  ->  Insert on dbo.result_double  (cost=0.00..22.70 rows=1270 width=24)
        Conflict Resolution: UPDATE
        Conflict Arbiter Indexes: pk_result_double
        ->  Custom Scan (ChunkDispatch)  (cost=0.00..22.70 rows=1270 width=24)
              Output: t.mprt_fk, t.result_timestamp, t.new_result_value, t.new_status
              ->  Seq Scan on pg_temp_8.update_table t  (cost=0.00..22.70 rows=1270 width=24)
                    Output: t.mprt_fk, t.result_timestamp, t.new_result_value, t.new_status
Settings: effective_cache_size = '193302MB', effective_io_concurrency = '256', max_parallel_workers = '32', max_parallel_workers_per_gather = '16', random_page_cost = '1.1', work_mem = '200618kB'
Query Identifier: 168594051192710239
Planning:
  Buffers: shared hit=6
4

Most of the optimizations in 2.16.0 for compressed DML are around reducing the amount of decompressions necessary to satisfy DML queries. To see any of these optimizations you need to actually run those queries (EXPLAIN with ANALYZE) to see their effect in EXPLAIN output. You can wrap it in a transaction that you roll back to make it repeatable. The initial queries you posted were not doing UPSERT so it makes sense that they dont benefit from all of the 2.16.0 optimizations.

5

Ok, thank you.
We will replace UPDATEs with UPSERTs.

6

i didn’t tried 2.16.0 yet