Time Series

SoliDB provides native support for time-series data with specialized append-only collections, efficient time-based pruning, and powerful downsampling capabilities using SDBQL.

Core Concepts

Append-Only Storage

Time series collections are immutable. Once data is written, it cannot be updated, only deleted via range pruning. This optimization allows for high write throughput.

Efficient Pruning

Old data can be removed instantly using strict time ranges. This is handled at the storage engine level (RocksDB) for maximum performance, avoiding per-document overhead.

Creating Time Series

Create a time series collection by setting the type to timeseries.

HTTP API

curl -X POST http://localhost:8080/_api/database/_system/collection \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "name": "metrics",
    "type": "timeseries"
  }'

Constraint

You cannot perform UPDATE or UPSERT operations on time series collections. Only INSERT and PRUNE (bulk delete) are supported.

Querying & Bucketing

SoliDB introduces the TIME_BUCKET() function to easily group data into fixed intervals (e.g., 5 minutes, 1 hour) for aggregation.

Example: Average CPU Usage per hour

FOR m IN metrics
  -- Bucket timestamps into 1-hour intervals
  COLLECT time = TIME_BUCKET(m.timestamp, "1h")
  
  -- Calculate average for each bucket
  AGGREGATE avg_cpu = AVG(m.cpu)
  
  -- Sort by time
  SORT time ASC
  
  RETURN { time, avg_cpu }

Supported Intervals

s - Seconds (e.g., "30s")
m - Minutes (e.g., "5m", "15m")
h - Hours (e.g., "1h", "6h")
d - Days (e.g., "1d", "7d")

Timestamp Formats

TIME_BUCKET accepts:

Unix timestamp (milliseconds)
ISO 8601 Date Strings
Negative timestamps (pre-1970)

Data Pruning

Efficiently delete old data using the prune API. This operation is much faster than running a REMOVE query because it deletes typically contiguous ranges of data on disk.

POST /_api/database/:db/collection/:name/prune

Request Body

{ "older_than": "2023-01-01T00:00:00Z" }

Response

{ "deleted": 15420 }

Note

This operation marks the collection count as "dirty" (approximate) until a full compaction or recount is performed, as the exact number of deleted documents is not calculated for performance reasons.