5 second planning for updating a single row. Execution 0.8 ms

Hi

I currently have a hypertable with ~5 billion rows. I have set compression to 3 weeks prior to DeliveryStartUTC column which is of type timezone. I have data from a few years back and 5 years in the future. I rarely write back in time. This setup gives me 297 chunks. When I try to write some time in the future with a query like this:

EXPLAIN ANALYZE
UPDATE public."DataPoints"
SET "TimeRegisteredUTC" = '2025-01-27 08:44:43.506065Z'
WHERE "DeliveryStartUTC" = '2026-09-01 03:45:00Z'
AND "Id" = 21011571099

It takes several seconds due to the planner being very slow. This is the query plan:

Custom Scan (HypertableModify)  (cost=0.43..2.66 rows=1 width=18) (actual time=0.724..0.725 rows=0 loops=1)
  ->  Update on "DataPoints"  (cost=0.43..2.66 rows=1 width=18) (actual time=0.724..0.725 rows=0 loops=1)
        Update on _hyper_26_24547_chunk "DataPoints_1"
        ->  Result  (cost=0.43..2.66 rows=1 width=18) (actual time=0.084..0.086 rows=1 loops=1)
              ->  Index Scan using _hyper_26_24547_chunk_idx_datapoints_update on _hyper_26_24547_chunk "DataPoints_1"  (cost=0.43..2.65 rows=1 width=10) (actual time=0.083..0.085 rows=1 loops=1)
                    Index Cond: (("DeliveryStartUTC" = '2026-09-01 03:45:00+00'::timestamp with time zone) AND ("Id" = '21011571099'::bigint))
Planning Time: 5322.877 ms
Execution Time: 0.844 ms

As you can tell, it is using an index to find the correct row and execution time is fast. However, planning time is crazy high. Even with an index on DeliveryStartUTC and Id which it seems to utilize.

Any suggestions on how to fix this?

when planning is taking longer, consider that you may have too many chunks.

Can you share a bit of your context and configuration of this database?

Do you use skipscan?

Thanks for the suggestion. I can try to give a short overview of the stats:

I have in total 297 chunks of which 209 of them is compressed. So essentially, my updates are on the 97 chunks, since I never update in the chunked areas.

My table is like this

CREATE TABLE IF NOT EXISTS public."DataPoints"
(
    "Id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "DeliveryStartUTC" timestamp with time zone NOT NULL,
    "Val" numeric NOT NULL,
    "CurveId" integer NOT NULL,
    "TimeRegisteredUTC" timestamp with time zone NOT NULL,
    "InsertedAtUTC" timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "FK_DataPoints_Curves_CurveId" FOREIGN KEY ("CurveId")
        REFERENCES public."Curves" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

I have an index on “DeliveryStartUTC” and “Id” in the same b-tree to make my update query faster. As seen in the query plan, it also utilize the query.

The update statement is a part of a trigger. The trigger checks if the value of the current newest record is the same. If the same, don’t insert the new one. If different then insert. If the record has an older TimeRegisteredUTC time than the current record, then it updates the current record with the older/new oldest seen TimeRegisteredUTC to always have TimeRegisteredUTC reflect when, we could in reality expect to see the value.

The trigger runs pretty smooth. It is just the update which kills performance from my findings. Changing update to insert helps quite a bit, but still sometimes falls a bit behind. I have dropped my hyper table serveral times as well, due to me testing out things. I feel my first runs with same chunk size was significantly faster. Maybe it is just placebo effect, of the update being slow? Just want to make sure I should not clear up something from the past dropped compressed hyper tables.

As mentioned, there is an index on the UPDATE and all the select statements with order by included in the index. I am not using skip scan, but running the SELECT statements myself seems to be performing very well without it.

The trigger is here:

CREATE FUNCTION public.before_insert_on_datapoints()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    prev_rec RECORD;
    next_rec RECORD;
BEGIN
    -- Find the record with the maximum "TimeRegisteredUTC" that is less than new."TimeRegisteredUTC"
    SELECT "DeliveryStartUTC", "Val", "CurveId"
    FROM public."DataPoints"
    WHERE "CurveId" = new."CurveId"
    AND "DeliveryStartUTC" = new."DeliveryStartUTC"
    AND "TimeRegisteredUTC" <= new."TimeRegisteredUTC"
    ORDER BY "TimeRegisteredUTC" DESC
    LIMIT 1
    INTO prev_rec;

    -- Check if a previous record exists and matches new."TimeRegisteredUTC"
    IF prev_rec = (new."DeliveryStartUTC", new."Val", new."CurveId") THEN
        RETURN NULL;
    END IF;

    -- Find the record with the minimum "TimeRegisteredUTC" that is greater than new."TimeRegisteredUTC"
    SELECT "DeliveryStartUTC", "Val", "CurveId", "TimeRegisteredUTC", "Id", "InsertedAtUTC"
    FROM public."DataPoints"
    WHERE "CurveId" = new."CurveId"
    AND "DeliveryStartUTC" = new."DeliveryStartUTC"
    AND "TimeRegisteredUTC" >= new."TimeRegisteredUTC"
    ORDER BY "TimeRegisteredUTC" ASC
    LIMIT 1
    INTO next_rec;

    -- Check if a next record exists and matches new."TimeRegisteredUTC"
    IF next_rec IS NOT NULL
        AND next_rec."CurveId" = new."CurveId"
        AND next_rec."DeliveryStartUTC" = new."DeliveryStartUTC"
        AND next_rec."Val" = new."Val" THEN
        -- Update the existing record only if new TimeRegisteredUTC is older
        -- so it always sets TimeRegisteredUTC to the oldest time seen.
        -- This is used when getting historical data.
        IF new."TimeRegisteredUTC" < next_rec."TimeRegisteredUTC" THEN
            UPDATE public."DataPoints"
            SET "TimeRegisteredUTC" = new."TimeRegisteredUTC"
            WHERE "DeliveryStartUTC" = next_rec."DeliveryStartUTC"
            AND "Id" = next_rec."Id"
        END IF;
        
        -- Don't insert the new record
        RETURN NULL;
    END IF;

    -- If no matches found, allow insertion of new record
    RETURN new;
END $BODY$;

Thanks for all the details @jacobmb. It seems the problem is related to how you’re working on the upserts.

Hypertables can receive updates but think that it’s all about generating work for vacuum and unordered data.

Alternatively, you can leverage the ability of Timescale to handle large amounts of inserts, and just re-build the final table as a continuous aggregates of the latest values that you need.

Also, remember that compression is very efficient, for same values it will just be an array of deltas or deltas of deltas. If you just make it raw you may get the performance from it: Time-series compression algorithms, explained | Timescale.

My last two cents:

think that your hypertable can be a pipeline to compute and store the data you’ll access later without interfering with triggers during the insert. With this little addition of raw data you can also collect some information about it and learn more about the behavior along the time.

Thanks for the clarification. In our use-case we aim to react to data changes in sub 100 milliseconds (worst case). I think if we have a continous aggregation, this would not be possible? With our current approach this is easily possible even with the trigger running first.

Currently our table increase by about 30-40 million rows a day. If we allow duplicate data I think we would easily hit 300 million rows a day. While Timescale might be able to handle this insert traffic I am not sure, that we would be able to react the same way? Currently our code reacts when an insert actually happens which it only does whenever a value is changed. Not sure, we would be able to do the same if we were to insert all the time?

As a “work around” for the update we instead do an insert. It generates a little more data (rarely) and still fits our use-case.

Also, do you think we could split on our CurveId (similar to a Device Id)? We would probably get around 40-50k CurveIds, however, I think it would give great performance. Maybe the planner does not agree?

You can join materialized results with raw data using real-time continuous aggregates and make it work. Let’s say you have a 1-second continuous aggregate, refreshing every second and getting only the latest second from raw data, too. It will just select fewer records.

Yes! Please benchmark it. It’s the fastest way to discover if that would work or not.
Also, give a try to experiment with add_dimension.