Hi, is there some simple command to delete the oldest partition? I'm running QuestDB with limited storage space and I'd like to delete the oldest partition when storage reaches 90% of used space.

Hi Vaclav, I'm afraid that you need to execute two statements to drop the oldest partition. First, select the oldest timestamp:

select min(ts) min_ts from x

Next, drop the partition:

alter table x drop partition where ts = :min_ts

Would the first query be equivalent to

SELECT ts FROM x LIMIT 1

? I would say in the absence of aggregations or

order by

, the

LIMIT 1

should return the earliest designated timestamp, and in that case the query should be faster than using

min()

Yes,

select ts from x limit 1

would work just fine and indeed it would be faster

Also note that in the

alter table

query you will need the timestamp to be at the same resolution level than your partitioning. For example, I have a table where I have daily partitioning but microsecond resolution in my timestamps, so my earlier timestamp is

2022-10-28T19:15:48.741819Z.

If I try to drop a partition

WHERE ts = '2022-10-28T19:15:48.741819Z'

I am getting an error saying

no partitions matched WHERE clause

. I can do something like this instead

alter table 'ilp_test'  drop partition where _timestamp_ = *timestamp_floor('d', to_timestamp('2022-10-28T19:15:48.741819Z', 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ'))*;

Now the timestamp is at day resolution, and the partition is dropped successfully

That's something we could improve in future

Just noticed in my latest query I don’t need to convert to timestamp before applying

timestamp_floor,

so it can be simplified as

alter table 'ilp_test'  drop partition where _timestamp_ = *timestamp_floor('d', '2022-10-28T19:15:48.741819Z')*;

Created a couple of GH issues for potential enchancements: https://github.com/questdb/questdb/issues/2879 https://github.com/questdb/questdb/issues/2878