Timescale DB causes issues with Auto vacuum and Locks up Postgres database causing it to crash: AutoVacuum issue

[mmpatil]

What type of bug is this?

Crash

What subsystems and features are affected?

Adaptive chunking

What happened?

2024-03-03 01:02:32.713 PST [10620] WARNING: autovacuum worker took too long to start; canceled
2024-03-03 01:02:32.871 PST [15088] WARNING: autovacuum worker started without a worker entry
2024-03-03 01:03:38.103 PST [10620] WARNING: autovacuum worker took too long to start; canceled
2024-03-03 01:03:44.283 PST [11932] WARNING: autovacuum worker started without a worker entry
2024-03-03 01:04:03.072 PST [10620] WARNING: autovacuum worker took too long to start; canceled
2024-03-03 01:04:06.977 PST [11360] WARNING: autovacuum worker started without a worker entry
2024-03-03 01:04:21.561 PST [10620] WARNING: autovacuum worker took too long to start; canceled
2024-03-03 01:04:31.328 PST [4428] WARNING: autovacuum worker started without a worker entry
2024-03-03 01:04:40.225 PST [10516] LOG: checkpoint starting: time
2024-03-03 01:04:42.199 PST [10516] LOG: checkpoint complete: wrote 17 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.787 s, sync=0.151 s, total=1.974 s; sync files=7, longest=0.079 s, average=0.022 s; distance=105 kB, estimate=5772 kB
2024-03-03 01:05:04.987 PST [10620] WARNING: autovacuum worker took too long to start; canceled
2024-03-03 01:05:19.873 PST [13680] WARNING: autovacuum worker started without a worker entry
2024-03-03 01:05:38.606 PST [10620] WARNING: autovacuum worker took too long to start; canceled

TimescaleDB version affected

2.5.0

PostgreSQL version used

15

What operating system did you use?

Windows Server

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

2024-03-03 01:02:32.713 PST [10620] WARNING:  autovacuum worker took too long to start; canceled
2024-03-03 01:02:32.871 PST [15088] WARNING:  autovacuum worker started without a worker entry
2024-03-03 01:03:38.103 PST [10620] WARNING:  autovacuum worker took too long to start; canceled
2024-03-03 01:03:44.283 PST [11932] WARNING:  autovacuum worker started without a worker entry
2024-03-03 01:04:03.072 PST [10620] WARNING:  autovacuum worker took too long to start; canceled
2024-03-03 01:04:06.977 PST [11360] WARNING:  autovacuum worker started without a worker entry
2024-03-03 01:04:21.561 PST [10620] WARNING:  autovacuum worker took too long to start; canceled
2024-03-03 01:04:31.328 PST [4428] WARNING:  autovacuum worker started without a worker entry
2024-03-03 01:04:40.225 PST [10516] LOG:  checkpoint starting: time
2024-03-03 01:04:42.199 PST [10516] LOG:  checkpoint complete: wrote 17 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.787 s, sync=0.151 s, total=1.974 s; sync files=7, longest=0.079 s, average=0.022 s; distance=105 kB, estimate=5772 kB
2024-03-03 01:05:04.987 PST [10620] WARNING:  autovacuum worker took too long to start; canceled
2024-03-03 01:05:19.873 PST [13680] WARNING:  autovacuum worker started without a worker entry
2024-03-03 01:05:38.606 PST [10620] WARNING:  autovacuum worker took too long to start; canceled

How can we reproduce the bug?

Have a table with 1000000 rows

[jnidzwetzki]

Hello @mmpatil,

Thanks for reaching out. I was unable to reproduce this issue in my local environment. I created a hypertable with more than 1000000 rows. However, the mentioned problem did not appear on my system. Could you share the steps that are needed to reproduce the issue (e.g., the layout of the table, hypertable settings, and sample data)?

In addition, you mention a database crash. The shared logfile only contains messages about the checkpointer and the autovacuum process. Could you elaborate on which kind of crash you faced and share more details (e.g., the stack trace of the crash)?

[nikkhils]

@mmpatil your timescaledb version 2.5.0 is kinda old. Please update it to 2.14.x when you get the chance.