dropping chunks doesn't release space at disk and Database level

[Hrishikesh-tsdb]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Platform/OS, User-Defined Action (UDA)

What happened?

I see a wired thing on our cluster where even after dropping the older chunks, space have not been released at the DB level and at OS mount even after running vacuum full.. logs have been attached..

TimescaleDB version affected

2.8.1

PostgreSQL version used

14.1

What operating system did you use?

Ubuntu 20.04.1

What installation method did you use?

Deb/Apt

What platform did you run on?

Amazon Web Services (AWS)

Relevant log output and stack trace

postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
--------------+----------+----------+---------+---------+------------------------+---------+------------+--------------------------------------------
timescaleDB | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 1398 GB | pg_default |

chunk_size | chunk_name | range_start | range_end
------------+------------------------------------------+------------------------+------------------------
80 GB | _timescaledb_internal._hyper_1_219_chunk | 2024-10-10 00:00:00+00 | 2024-10-17 00:00:00+00
160 GB | _timescaledb_internal._hyper_1_204_chunk | 2024-10-03 00:00:00+00 | 2024-10-10 00:00:00+00
235 GB | _timescaledb_internal._hyper_1_197_chunk | 2024-09-26 00:00:00+00 | 2024-10-03 00:00:00+00
212 GB | _timescaledb_internal._hyper_1_189_chunk | 2024-09-19 00:00:00+00 | 2024-09-26 00:00:00+00
223 MB | _timescaledb_internal._hyper_1_181_chunk | 2024-09-12 00:00:00+00 | 2024-09-19 00:00:00+00
90 MB | _timescaledb_internal._hyper_1_176_chunk | 2024-09-05 00:00:00+00 | 2024-09-12 00:00:00+00
376 MB | _timescaledb_internal._hyper_1_170_chunk | 2024-08-29 00:00:00+00 | 2024-09-05 00:00:00+00
48 kB | _timescaledb_internal._hyper_1_153_chunk | 2024-08-15 00:00:00+00 | 2024-08-22 00:00:00+00
80 kB | _timescaledb_internal._hyper_1_133_chunk | 2024-08-08 00:00:00+00 | 2024-08-15 00:00:00+00
188 MB | _timescaledb_internal._hyper_1_124_chunk | 2024-08-01 00:00:00+00 | 2024-08-08 00:00:00+00
201 MB | _timescaledb_internal._hyper_1_195_chunk | 2023-12-28 00:00:00+00 | 2024-01-04 00:00:00+00
(11 rows)

 hypertable_name     | hypertable_size_in_gb
-------------------------+-----------------------
table1 | 687
table2 | 187
(2 rows)

pg_size_pretty
1398 GB
(1 row)

  view_name       | view_size_in_mb
----------------------+-----------------
rz_hour_downsample | 448
rz_minute_downsample | 22971
(2 rows)

Filesystem Size Used Avail Use% Mounted on
/dev/nvme2n1 2.0T 1.9T 113G 95% /data

How can we reproduce the bug?

once you have a hypertable with few chunks with each chunk with considerable size (may be 10GB to see the difference), try dropping couple of chunks which may sum upto 50GB or more and check if we see the size of DB and the chunks available are of same size..

[antekresic]

Any chance you can try this out on one of the more recent versions? 2.8 is quite old at this point.

Thanks.

[Hrishikesh-tsdb]

@antekresic we have another environment with 2.11 version and I see the same, about only 80GB disappeared :-)

[antekresic]

Can you give us more information what you are seeing here? You are saying the issue arises when you have these huge chunks? (over 10GB)

How are you dropping the chunks? Using drop_chunk function or DROP TABLE? Can you see the relation files being left behind on the server after dropping them?

[Hrishikesh-tsdb]

Yes, we have each chunk of about 240GB uncompressed and when i drop the older chunks, it doesn't release space on disk..
i have dropped the chunks using :
SELECT drop_chunks('mytable', INTERVAL '1 month');

I dont see any chunks or _timescale* chunk tables after i drop them..

[antekresic]

Can you check if they exist in the PG catalog tables?

SELECT count(*) from pg_class where relname like '%hyper%chunk';

Should be 0 if they were all dropped.

[Hrishikesh-tsdb]

PG catalog have only the chunks needed, the chunks which we dropped doesn't show up in this..

[antekresic]

If that is the case, you have leftover orphaned data files that you have to remove by hand.

You can use something like vacuumlo utility to get rid of them.
https://www.postgresql.org/docs/current/vacuumlo.html

[Hrishikesh-tsdb]

Thank you, but it looks like it is checking only the postgres tables, its not working on hypertables/chunks unfortunately..
fyi, I did restart of the host but it didnt help either..

postgres@TimescaleDB-qa2-01:~$ vacuumlo -n mydb
Connected to database ""
Test run: no large objects will be removed!
Checking data in public.mytable
Would remove 0 large objects from database "".

[antekresic]

Hypertables and chunks are postgres tables so it should pick them up. Its hard to help you like this since I have no insight where the space is being kept.

Can you locate the relation files of these dropped chunks in the pgdata folder? There should be huge files which have no reference in the pg catalog tables.