Unable to DELETE data when chunk compression is undergoing

TimescaleDB and PostgreSQL
,
1

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?

2

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.

3

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
4

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

1 Like
5

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

6

Hello, I am using Postgres 14.7 and Timescaledb 2.11.1 and I still have the same problem. During compression, the possibility of deleting data from the table that is not subject to compression is blocked.
Tabele:
CREATE TABLE IF NOT EXISTS public.sec
(
tagno integer NOT NULL,
“timestamp” timestamp with time zone NOT NULL,
value real,
confidence smallint DEFAULT 100
)
interval_length=24h

Manually invoked compression:
SELECT compress_chunk(i, if_not_compressed => true) FROM show_chunks(‘sec’,‘2023-09-03 00:00:00+00’,‘2023-09-02 00:00:00+00’)i;

Bloked query:
delete from sec where (timestamp > ‘2023-09-07’) and tagno=1000000000 and timestamp = ‘2023-09-07 12:00:00+00’;
I haven’t changed the constraint exclusion parameter yet
Maybe a bug in the compression settings. Below is the view from the query:
SELECT * FROM timescaledb_information.compression_settings WHERE hypertable_name = ‘sec’;

hypertable_schema hypertable_name attname segmentby_column_index orderby_column_index orderby_asc orderby_nullsfirst
public sec tagno 1
public sec timestamp 1 false true
Anyone have an idea why?
Does anyone have an idea why? Any suggestions ?
7

Thanks for reporting @Pawel_Wronski, feel free to open a new issue on github. As you got a reproducible example, it can help the team to figure out and fix it fast.