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
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