Time-Series

ParticleDB provides built-in time-series data lifecycle management with automatic retention policies, continuous aggregates for incremental downsampling, and tiered data management that transitions raw data into pre-aggregated summaries.

Retention Policies

Automatically delete data older than a specified duration. The engine periodically checks which policies are due and generates DELETE statements to purge expired rows.

Create a Retention Policy

CREATE RETENTION POLICY cleanup ON events COLUMN created_at RETENTION '30 days';

Optionally specify how often the policy is checked:

CREATE RETENTION POLICY log_cleanup ON logs COLUMN timestamp RETENTION '7 days' CHECK EVERY '1 hour';

Parameter	Description
ON <table>	The table to apply the policy to
COLUMN <col>	The timestamp column used to determine row age
RETENTION '<duration>'	How long to keep data (e.g., 30 days, 7d, 1h)
CHECK EVERY '<interval>'	How often to run the deletion check (default: policy-dependent)

Drop a Retention Policy

DROP RETENTION POLICY cleanup;

Show Retention Policies

SHOW RETENTION POLICIES;

Continuous Aggregates

A continuous aggregate is a materialized query over time-bucketed data that is incrementally refreshed. A watermark tracks the last processed bucket so only new data is aggregated on each refresh cycle.

Create a Continuous Aggregate

CREATE CONTINUOUS AGGREGATE hourly_stats AS
  SELECT TIME_BUCKET('1 hour', ts) AS bucket, COUNT(*) AS cnt, AVG(value) AS avg_val
  FROM metrics
  GROUP BY bucket
  REFRESH EVERY 1 HOUR;

The REFRESH EVERY clause schedules automatic incremental refreshes. You can also trigger a manual refresh:

REFRESH CONTINUOUS AGGREGATE hourly_stats;

Drop a Continuous Aggregate

DROP CONTINUOUS AGGREGATE hourly_stats;

Show Continuous Aggregates

SHOW CONTINUOUS AGGREGATES;

Downsampling Policies

Downsampling policies provide tiered data management: raw data is aggregated into coarser continuous aggregates at configurable age thresholds, and the raw rows can optionally be deleted after aggregation.

CREATE DOWNSAMPLING POLICY ds_metrics ON metrics COLUMN ts
  TIER 7d AGGREGATE TO hourly_metrics
  TIER 90d AGGREGATE TO daily_metrics DELETE RAW;

This policy:

1. After 7 days, aggregates raw metrics rows into hourly_metrics.
2. After 90 days, further aggregates into daily_metrics and deletes the original raw rows.

Drop a Downsampling Policy

DROP DOWNSAMPLING POLICY ds_metrics;

Duration Format

Duration strings accept both compact and human-readable formats:

Format	Examples
Compact	30d, 7d, 1h, 5m, 120s
Long	30 days, 1 hour, 5 minutes, 120 seconds

How It Works

The TimeSeriesManager maintains all retention policies, continuous aggregates, and downsampling policies. The engine periodically checks for:

• Policies due for run: retention policies whose check interval has elapsed. For each due policy, a DELETE FROM <table> WHERE <column> < <cutoff> statement is generated and executed.
• Aggregates due for refresh: continuous aggregates whose refresh interval has elapsed. Only rows newer than the watermark are processed, making refreshes incremental.

Each execution records the timestamp and rows affected, enabling monitoring and debugging of the data lifecycle pipeline.