Partition not detached

Community
,
1

Hello everyone! I have a question regarding a partition not being detached. I have a QuestDB table containing market data partitioned by DAY. Every partition contains roughly ~50 million records (tick by tick data).

When I try to detach the partitions for december 2024, all the partitions are detached, except the one named ‘2024-12-31’. I used the following command:

alter table <my table name> detach partition where update_time between '2024-12-01' and '2024-12-31'

Is there any particular reason for a partition not being detached? No new records are coming in, so the partition should be available for detaching. Am I missing something?

2

Hi @JosephP91 , welcome!

Let’s see what’s happening. Here are a few questions:

  • Do you see any error messages in the server logs when you run that command?
  • Do you have any data after ‘2024-12-31’, or is that the last (latest) partition?
  • Have you tried running with the upper bound being 2025-01-01? BETWEEN is inclusive, but it is worth ruling out any possible issue with the filter.
  • How are you checking if they are detached, by using table_partitions(), and also checking for renames?
3

Hello @nwoolmer, thanks for the response!

The only message I see is this one:

2025-01-04T10:08:55.727746Z I i.q.c.w.ApplyWal2TableJob error applying SQL to wal table [table=mts_proposals, sql=alter table mts_proposals detach partition where UPDATE_TIME between '2024-12-31' and '2024-01-01', error=could not detach partition [table=mts_proposals, detachStatus=DETACH_ERR_ACTIVE, partitionTimestamp=2024-12-31T00:00:00.000Z, partitionBy=DAY], errno=-104]

Since I’ve already issued the command to detach the partition (I also tried to use ‘2024-01-01’ as upper bound condition), I guess that that error is saying that the partition is already detached, but its name misteriously does not contain the ‘.detached’ suffix. Strange thing is that this partition is named ‘2024-12-31.205610’ on disk. Is it possible that that suffix is causing problems when QuestDB tries to rename it using .detached suffix?

I am checking the table partitions using this command: show partitions from mts_proposals;

This is the only partition that is giving me this problem. All the other partitions have been detached successfully.

4

The error message means that you’re trying to detach last partition and that’s not allowed. Please reduce the time range to exclude the last day you have the data in the table.

5

Hi @ideoma, thank you. Yes, I tried to do so and it worked. I am curious about why: Why can’t I detach all the partitions including the last active one?

6

The last partition not being detachable is documented here: ALTER TABLE DETACH PARTITION | QuestDB

Re: why, that is a philosophical question! The intent is usually for people to move cold partitions, and the latest partition is considered ‘hot’. Perhaps it should be more lax if no ingestion is happening.

Simplest work around is to create a new ‘latest’ partition by inserting a new row in the next day, then you can detach all of your ‘real’ partitions.

When we release Parquet partitions, this will be simpler, as the partitions will already be in a transferrable format.

1 Like