Automate partition removal on QuestDB

Question

I have several time-series tables with daily partitioning on a QuestDB database. I would like to remove every day all the partitions older than 21 days from my tables. What would be the best way to do it?

Thanks

Answer 1

You can use ALTER TABLE...DROP PARTITION to remove old partitions from a particular table. You can execute this command either via pgwire API or via REST API. When working with a cronjob probably API is the most convenient way and you could execute something like

curl -G --data-urlencode "query=ALTER TABLE measurements DROP PARTITION WHERE timestamp < to_timestamp('2018-01-01:00:00:00', 'yyyy-MM-dd:HH:mm:ss');" http://localhost:9000/exec

To make it more interesting, you could go over all the tables on your database with daily partitioning, and calculate automatically what is the date range to delete. I am pasting a simple bash script to do so. The script depends on curl and jq and has been tested on both Ubuntu and OSx.

#!/bin/bash

# This script needs both curl and jq installed.
# It will go over all the tables with daily partitioning and will remove all partitions older than 21 days 
# It uses jq to parse the JSON output from the REST API, extracting the "dataset" element and flatten all the rows.
# Then it reads line by line and calls the QuestDB API with each ALTER TABLE statement.


# We get all the tables with daily partitioning and compose the ALTER TABLE statements

TABLES=`curl -G  --data-urlencode "query=with daily_tables AS (
select name, designatedTimestamp, timestamp_floor('d',dateadd('d', -21, systimestamp())) as deadline from tables where partitionBy = 'DAY'
)
select CONCAT('ALTER TABLE ', name, ' DROP PARTITION WHERE ', designatedTimestamp, ' <= ', deadline) FROM daily_tables;" "http://localhost:9000/exec?nm=true"|jq ".dataset | flatten[]"`


# Splitting the output line by line and issuing the ALTER TABLE
printf '%s\n' "$TABLES" |
while IFS= read -r sql; do
        # echo $sql #uncomment if you want to output each statement we are sending
        #we need to remove starting and trailing double quote from the line, so using :1:-1 syntax
        curl -G --data-urlencode "query=${sql:1:-1}" http://localhost:9000/exec
done