Unable to DELETE data when chunk compression is undergoing

Hey!
I have a hypertable with daily chunks and compression policy “older than 3 days”.
I regularly make DELETE request for data that is newer then 24 hours.
But when compression kicks in for 4-day-old chunk, it blocks my DELETEs for today’s data.
I even rewritten my DELETE requests to show Timescale, that I don’t want to touch old data:

DELETE FROM “my_table” WHERE (time >= ‘2022-05-23 09:43:00’) AND “my_tabe”.“user_id” = 123 AND “my_table”.“time” = ‘2022-05-23 09:43:00’

But with no success. And this DELETE is blocked by compression of the chunk 2022-05-17 00:00:00+00 → 2022-05-18 00:00:00+00

Is it a known bug? Is there a workaround for this?

Hello @alxgsv welcome to the forum.

Can I check what version of TimescaleDB you’re using? It sounds similar to issue 3719, I wonder if you could review that and confirm? And just to check, too, that this is a single node database (not multi-node).

If it is the same issue, it would be worth you upvoting and/or adding a comment there. Reproducible examples are ALWAYS very welcome.

After some research we found that issue fixed by change constraint_exclusion postgresql setting from default value partition to more generic value ‘on’
see PostgreSQL: Documentation: 14: 20.7. Query Planning

I can’t find any mention of recommended constraint_exclusion setting in timescaledb docs though.

2 Likes

Ah interesting, thank you for this @Maxim_Boguk … I will mark this for attention from our docs team.

1 Like

Hi @LorraineP !!! we just got a response from the SME: “The default value for constraint exclusion should be sufficient and users don’t need to change this. There have been improvements to this area of code in recent version so i would definitely recommend upgrading to PG14 and latest timescaledb version (2.7.0).” Here’s the issue for your reference:[Docs RFC] Update recommended constraint_exclusion setting · Issue #1134 · timescale/docs · GitHub