Data retention

Background

With time-series data, older data usually matters less over time. When you no longer need old data, you can delete it from QuestDB to save disk space or to meet a data retention policy. You do this by removing or tiering table partitions, or by applying row-level retention to a materialized view.

QuestDB offers five approaches for data retention:

• TTL (automatic, open source): Use Time To Live (TTL) to automatically drop partitions when data ages beyond a specified threshold. This is the simplest approach and is available in both open source and Enterprise editions.
• EXPIRE ROWS (automatic, materialized views only): Use EXPIRE ROWS to keep only some rows in a materialized view, such as the latest row per key, the top-N rows per group, or rows matching a condition. The base table is not touched. Some policies hide expired rows without freeing their disk space, so check the policy's enforcement mode if you care about reclaiming storage.
• Storage policy (automatic, Enterprise only): Use a storage policy to automate the partition lifecycle — convert to Parquet locally and drop old data on a schedule. This is the recommended approach for QuestDB Enterprise users who need graduated data tiering beyond simple deletion.
• Cold storage (automatic, Enterprise only): Add the TO REMOTE stage to a storage policy to move historical partitions to object storage as Parquet instead of deleting them. They stay queryable with normal SQL while leaving local disk. Use this when retention is driven by cost or compliance rather than by the data no longer being useful. See cold storage.
• Manual: Use DROP PARTITION commands as described on this page for explicit control over which partitions to remove and when.

This page lists the retention options, then shows partitioning examples that drop data by date. For more on partitioning, see the partitioning page.

Manual partition management

This section covers the manual approach to removing stale data by dropping partitions. A table must have a designated timestamp assigned and a partitioning strategy specified during a CREATE TABLE operation to achieve this.

note

Users cannot alter the partitioning strategy after a table is created.

Tables can be partitioned by one of the following:

• YEAR
• MONTH
• WEEK
• DAY
• HOUR

Creating a table and partitioning by DAY

CREATE TABLE my_table(ts TIMESTAMP, symb SYMBOL, price DOUBLE) timestamp(ts)
PARTITION BY DAY;

Dropping partitions

caution

Use DROP PARTITION with care, as QuestDB cannot recover data from dropped partitions.

To drop partitions, users can use the ALTER TABLE DROP PARTITION syntax. Partitions may be dropped by:

• DROP PARTITION LIST - specifying a comma-separated list of partitions to drop

  --Delete a partition
  ALTER TABLE my_table DROP PARTITION LIST '2021-01-01';
  
  --Delete a list of two partitions
  ALTER TABLE my_table DROP PARTITION LIST '2021-01-01', '2021-01-02';

• WHERE timestamp = - exact date matching by timestamp

  ALTER TABLE my_table DROP PARTITION
  WHERE timestamp = to_timestamp('2021-01-01', 'yyyy-MM-dd');

• WHERE timestamp < - using comparison operators (< / >) to delete by time range relative to a timestamp. Note that the now() function may be used to automate dropping of partitions relative to the current time, i.e.:

  --Drop partitions older than 30 days
  ALTER TABLE my_table DROP PARTITION
  WHERE timestamp < dateadd('d', -30, now());

Usage notes:

• The most chronologically recent partition cannot be deleted
• Arbitrary partitions may be dropped, which means they may not be the oldest chronologically. Depending on the types of queries users are performing on a dataset, it may not be desirable to have gaps caused by dropped partitions.
• Unlike TTL, DROP PARTITION commands must be triggered manually or via external scheduling (e.g., cron jobs). For automated partition retention, consider TTL or a storage policy instead.

Example

The following example demonstrates how to create a table with partitioning and to drop partitions based on time. This example produces 5 days' worth of data with one incrementing LONG value inserted per hour.

Create a partitioned table and generate data

CREATE TABLE my_table (timestamp TIMESTAMP, x LONG) timestamp(timestamp)
PARTITION BY DAY;

INSERT INTO my_table
SELECT timestamp_sequence(
    to_timestamp('2021-01-01T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),100000L * 36000), x
FROM long_sequence(120);

For reference, the following functions are used to generate the example data:

• timestamp_sequence with 1 hour stepping
• long_sequence which creates a x:long column

The result of partitioning is visible when listing as directories on disk:

path/to/<QuestDB-root>/db

my_table
├── 2021-01-01
├── 2021-01-02
├── 2021-01-03
├── 2021-01-04
└── 2021-01-05

Partitions can be dropped using the following query:

--Delete days before 2021-01-03
ALTER TABLE my_table DROP PARTITION
WHERE timestamp < to_timestamp('2021-01-03', 'yyyy-MM-dd');