Skip to content

TSDB.md

Latest commit

 

History

History
672 lines (529 loc) · 15.7 KB

TSDB.md

File metadata and controls

672 lines (529 loc) · 15.7 KB
Raw

Time Series Database (TSDB) Documentation

This document provides comprehensive documentation for the TSDB implementation with DataCrate support.

Overview

The TSDB component is designed to efficiently store and query time series data with high throughput and low query latency. It supports structured schema definition, time-based indexing, and SQL-like queries with powerful aggregation capabilities.

Key features:

  • DataCrate Organization: Logical grouping of related time series data
  • Flexible Schema: Define fields, tags, and retention policies per table
  • High Performance: Optimized storage with compression and indexing
  • SQL-like Queries: Familiar query syntax with time series extensions
  • Aggregation Support: Built-in functions for time series analysis

Architecture

DataCrate Concept

DataCrates are logical containers similar to databases in traditional RDBMS:

DataCrate "weather_monitoring"
├── Table "sensors" (temperature, humidity, pressure data)
├── Table "alerts" (alert events and notifications)
└── Table "diagnostics" (system health metrics)

Data Model Hierarchy

  1. TsdbStorage: Main storage engine managing all datacrates
  2. DataCrate: Logical container with retention policy and metadata
  3. Table: Collection of time series records with defined schema
  4. TableStorage: Physical storage for table data with indexing
  5. TimeSeriesRecord: Individual timestamped data point

Getting Started

1. Initialize TSDB Storage

use my_database_app::tsdb::storage::TsdbStorage;
use my_database_app::compression::CompressionType;

// Create TSDB storage with Zstd compression
let tsdb = TsdbStorage::new("./data", CompressionType::Zstd)?;

2. Create a DataCrate

use my_database_app::tsdb::types::{CrateCommand, RetentionPolicy};
use chrono::Duration;

let cmd = CrateCommand::CreateCrate {
    name: "weather_data".to_string(),
    description: Some("Weather station monitoring".to_string()),
    retention_policy: RetentionPolicy::Duration(Duration::days(30)),
};

let result = tsdb.execute_crate_command(cmd)?;

3. Create Tables with Schema

use my_database_app::tsdb::types::{TableCommand, TableSchema, FieldType};
use std::collections::BTreeMap;

// Define schema
let mut fields = BTreeMap::new();
fields.insert("temperature".to_string(), FieldType::Float);
fields.insert("humidity".to_string(), FieldType::Float);
fields.insert("status".to_string(), FieldType::String);

let schema = TableSchema {
    fields,
    tags: vec!["location".to_string(), "sensor_id".to_string()],
    retention_policy: Some(RetentionPolicy::Duration(Duration::days(7))),
};

let cmd = TableCommand::CreateTable {
    crate_name: "weather_data".to_string(),
    table_name: "sensors".to_string(),
    schema,
};

tsdb.execute_table_command(cmd)?;

4. Insert Time Series Data

use my_database_app::tsdb::types::{DataCommand, TimeSeriesRecord, FieldValue};
use chrono::Utc;

// Prepare data
let mut fields = BTreeMap::new();
fields.insert("temperature".to_string(), FieldValue::Float(23.5));
fields.insert("humidity".to_string(), FieldValue::Float(45.0));
fields.insert("status".to_string(), FieldValue::String("normal".to_string()));

let mut tags = BTreeMap::new();
tags.insert("location".to_string(), "office_a".to_string());
tags.insert("sensor_id".to_string(), "temp_001".to_string());

let record = TimeSeriesRecord {
    timestamp: Utc::now(),
    fields,
    tags,
};

// Insert data
let cmd = DataCommand::Insert {
    crate_name: "weather_data".to_string(),
    table_name: "sensors".to_string(),
    record,
};

tsdb.execute_data_command(cmd)?;

5. Query Data

use my_database_app::tsdb::query::parse_query;

// Parse and execute query
let query = parse_query("SELECT temperature, humidity FROM sensors WHERE location = 'office_a' LIMIT 10")?;
let results = tsdb.execute_query(&query)?;

// Process results
for row in results {
    for (field, value) in row {
        println!("{}: {}", field, value);
    }
}

Client Command Reference

The TSDB supports both structured commands and legacy simplified commands.

DataCrate Commands

Create DataCrate

TSDB_CREATE_CRATE <name> <description> <retention_policy_json>

Example:

TSDB_CREATE_CRATE weather_monitoring "Weather station data" {"Duration":2592000}

List DataCrates

TSDB_LIST_CRATES

Get DataCrate Information

TSDB_GET_CRATE_INFO <name>

Delete DataCrate

TSDB_DELETE_CRATE <name>

Table Commands

Create Table

TSDB_CREATE_TABLE <crate_name> <table_name> <schema_json>

Schema format:

{
  "fields": {
    "temperature": "Float",
    "humidity": "Float", 
    "status": "String",
    "active": "Boolean",
    "count": "Integer"
  },
  "tags": ["location", "sensor_id", "building"],
  "retention_policy": {"Duration": 604800}  // Optional, 7 days in seconds
}

Example:

TSDB_CREATE_TABLE weather_monitoring sensors {"fields":{"temperature":"Float","humidity":"Float"},"tags":["location","sensor_id"],"retention_policy":{"Duration":604800}}

List Tables

TSDB_LIST_TABLES <crate_name>

Get Table Schema

TSDB_GET_TABLE_SCHEMA <crate_name> <table_name>

Delete Table

TSDB_DELETE_TABLE <crate_name> <table_name>

Data Commands

Insert Data

TSDB_INSERT <crate_name> <table_name> <record_json>

Record format:

{
  "timestamp": "2025-06-06T12:00:00Z",
  "fields": {
    "temperature": 72.5,
    "humidity": 45.0,
    "status": "normal"
  },
  "tags": {
    "location": "office_a",
    "sensor_id": "temp_001"
  }
}

Example:

TSDB_INSERT weather_monitoring sensors {"timestamp":"2025-06-06T12:00:00Z","fields":{"temperature":72.5,"humidity":45.0},"tags":{"location":"office_a","sensor_id":"temp_001"}}

Query Data

TSDB_QUERY "<sql_query>"

Delete Data

TSDB_DELETE <crate_name> <table_name> <time_range_json> <tags_json>

Legacy Time Series Commands

For backward compatibility and simplified usage:

Create Measurement

TS.CREATE "<schema_json>"

Schema format:

{
  "name": "measurement_name",
  "fields": {
    "field1": 0.0,        // Float field (initialize with 0.0)
    "field2": 0,          // Integer field (initialize with 0)
    "field3": "",         // String field (initialize with "")
    "field4": false       // Boolean field (initialize with false)
  },
  "retention_policy": {
    "Duration": 2592000   // Retention in seconds (30 days)
  }
}

Example:

TS.CREATE "{\"name\":\"weather\",\"fields\":{\"temperature\":0.0,\"humidity\":0.0,\"pressure\":0.0},\"retention_policy\":{\"Duration\":2592000}}"

Insert Record

TS.INSERT <measurement_name> "<record_json>"

Example:

TS.INSERT weather "{\"timestamp\":\"2025-06-06T12:00:00Z\",\"fields\":{\"temperature\":72.5,\"humidity\":45.0,\"pressure\":1013.2},\"tags\":{\"location\":\"NYC\",\"station\":\"central\"}}"

Query Records

TS.QUERY "<sql_query>"

List Measurements

TS.LIST

SQL Query Language

The TSDB supports a comprehensive SQL-like query language designed for time series data.

Basic Query Structure

SELECT <fields>
FROM <table_name>
[WHERE <conditions>]
[GROUP BY <grouping>]
[LIMIT <number>]

Field Selection

-- Select specific fields
SELECT temperature, humidity FROM sensors;

-- Select all fields
SELECT * FROM sensors;

-- Select with aggregation
SELECT AVG(temperature), MAX(humidity), COUNT(*) FROM sensors;

Time Filtering

-- Time range queries
SELECT * FROM sensors 
WHERE TIME >= '2025-06-01T00:00:00Z' 
  AND TIME <= '2025-06-02T00:00:00Z';

-- Relative time
SELECT * FROM sensors 
WHERE TIME >= '2025-06-06T00:00:00Z';

Tag Filtering

-- Single tag filter
SELECT temperature FROM sensors 
WHERE location = 'office_a';

-- Multiple tag filters
SELECT * FROM sensors 
WHERE location = 'office_a' 
  AND sensor_id = 'temp_001';

Field Value Filtering

-- Numeric comparisons
SELECT * FROM sensors 
WHERE temperature > 25.0 
  AND humidity < 50.0;

-- String comparisons
SELECT * FROM sensors 
WHERE status = 'normal';

Aggregation Functions

Supported Functions

  • COUNT() - Count of records
  • SUM(field) - Sum of field values
  • AVG(field) - Average of field values
  • MIN(field) - Minimum field value
  • MAX(field) - Maximum field value
  • FIRST(field) - First value chronologically
  • LAST(field) - Last value chronologically
  • PERCENTILE_XX(field) - Percentile functions (e.g., PERCENTILE_95)

Examples

-- Basic aggregation
SELECT COUNT(*), AVG(temperature), MAX(humidity) FROM sensors;

-- Aggregation with filtering
SELECT AVG(temperature) FROM sensors 
WHERE location = 'office_a' 
  AND TIME >= '2025-06-06T00:00:00Z';

-- Multiple aggregations
SELECT 
    location,
    COUNT(*) as reading_count,
    AVG(temperature) as avg_temp,
    MIN(temperature) as min_temp,
    MAX(temperature) as max_temp,
    PERCENTILE_95(temperature) as p95_temp
FROM sensors 
GROUP BY location;

Time-based Grouping

Group results by time intervals using GROUP BY TIME(interval):

Supported Intervals

  • Seconds: 1s, 30s, 45s
  • Minutes: 1m, 5m, 15m, 30m
  • Hours: 1h, 3h, 6h, 12h
  • Days: 1d, 7d
  • Weeks: 1w, 2w
  • Months: 1M, 3M, 6M
  • Years: 1y

Examples

-- Hourly averages
SELECT AVG(temperature), MAX(humidity) 
FROM sensors 
GROUP BY TIME(1h);

-- Daily summaries
SELECT 
    COUNT(*) as readings_per_day,
    AVG(temperature) as daily_avg_temp,
    MIN(temperature) as daily_min_temp,
    MAX(temperature) as daily_max_temp
FROM sensors 
GROUP BY TIME(1d);

-- 15-minute intervals with filtering
SELECT location, AVG(temperature) 
FROM sensors 
WHERE TIME >= '2025-06-06T00:00:00Z'
GROUP BY location, TIME(15m);

Result Limiting

-- Limit number of results
SELECT * FROM sensors LIMIT 100;

-- Limit with ordering (implicit time ordering)
SELECT temperature, humidity FROM sensors 
WHERE location = 'office_a' 
LIMIT 50;

Complex Query Examples

Multi-condition Filtering

SELECT sensor_id, temperature, humidity 
FROM sensors 
WHERE TIME >= '2025-06-06T00:00:00Z'
  AND TIME <= '2025-06-06T23:59:59Z'
  AND location = 'office_a'
  AND temperature BETWEEN 20.0 AND 30.0
  AND status = 'normal'
LIMIT 1000;

Time-based Analysis

-- Hourly temperature trends for specific location
SELECT 
    AVG(temperature) as avg_temp,
    MIN(temperature) as min_temp,
    MAX(temperature) as max_temp,
    COUNT(*) as reading_count
FROM sensors 
WHERE location = 'office_a'
  AND TIME >= '2025-06-01T00:00:00Z'
GROUP BY TIME(1h);

Multi-location Comparison

-- Compare average temperatures across locations
SELECT 
    location,
    AVG(temperature) as avg_temp,
    PERCENTILE_95(temperature) as p95_temp,
    COUNT(*) as reading_count
FROM sensors 
WHERE TIME >= '2025-06-01T00:00:00Z'
GROUP BY location;

Data Model

Field Types

Float

  • 64-bit floating point numbers
  • Used for decimal measurements (temperature, pressure, etc.)
  • Example: 23.5, 1013.25

Integer

  • 64-bit signed integers
  • Used for counts, IDs, whole number measurements
  • Example: 42, -10, 1000

String

  • UTF-8 encoded text
  • Used for categorical data, status values, descriptions
  • Example: "normal", "sensor_001", "critical"

Boolean

  • True/false values
  • Used for binary states, flags, conditions
  • Example: true, false

Tags vs Fields

Fields

  • Contain the actual measured values
  • Are indexed for range queries
  • Support aggregation functions
  • Examples: temperature, pressure, count, voltage

Tags

  • Contain metadata about the measurement
  • Are indexed for exact-match filtering
  • Used for grouping and filtering
  • Should have low cardinality (limited unique values)
  • Examples: location, sensor_id, device_type, status

Retention Policies

Duration-based

{"Duration": 2592000}  // 30 days in seconds

Common durations:

  • 1 hour: 3600
  • 1 day: 86400
  • 1 week: 604800
  • 30 days: 2592000
  • 1 year: 31536000

Forever

{"Forever": null}

Data is kept indefinitely (use carefully for storage management).

Schema Evolution

When you need to modify schemas:

  1. Adding fields: Create new table with additional fields
  2. Removing fields: Fields not in new records are simply omitted
  3. Changing types: Requires data migration to new table
  4. Adding tags: New tags can be added to new records
  5. Removing tags: Tags not in new records are omitted

Schema format:

{
  "name": "measurement_name",
  "fields": {
    "field1": 0.0,        // Float field (initialize with 0.0)
    "field2": 0,          // Integer field (initialize with 0)
    "field3": "",         // String field (initialize with "")
    "field4": false       // Boolean field (initialize with false)
  },
  "retention_policy": {
    "Duration": 2592000   // Retention in seconds (30 days)
  }
}

Inserting Records

TS.INSERT <measurement> "<record_json>"

Example:

TS.INSERT weather "{"timestamp":"2025-06-06T12:00:00Z","fields":{"temperature":72.5,"humidity":45.0,"pressure":1013.2},"tags":{"location":"NYC","station":"central"}}"

Record format:

{
  "timestamp": "2025-06-06T12:00:00Z",  // ISO 8601 timestamp
  "fields": {
    "field1": 72.5,      // Value must match field type in schema
    "field2": 1000
  },
  "tags": {              // Tags are optional metadata for filtering
    "location": "NYC",
    "host": "server1"
  }
}

Querying Data

TS.QUERY "<sql_query>"

The SQL query supports SELECT, FROM, WHERE, GROUP BY, and LIMIT clauses.

Examples:

TS.QUERY "SELECT temperature, humidity FROM weather WHERE TIME >= '2025-06-01T00:00:00Z' AND TIME <= '2025-06-07T00:00:00Z'"

TS.QUERY "SELECT AVG(temperature), MAX(humidity) FROM weather WHERE location = 'NYC' GROUP BY TIME(1h) LIMIT 24"

Query format:

SELECT <fields> FROM <measurement> [WHERE <conditions>] [GROUP BY <group>] [LIMIT <n>]
  • SELECT: comma-separated field names or aggregate functions
    • Supported aggregates: COUNT(), SUM(), AVG(), MIN(), MAX(), FIRST(), LAST(), PERCENTILE_XX()
  • WHERE: filter by time range and/or tags
    • Time format: TIME >= '2025-06-01T00:00:00Z' AND TIME <= '2025-06-07T00:00:00Z'
    • Tag format: tag_name = 'tag_value'
  • GROUP BY: group results, e.g., GROUP BY TIME(1h) for hourly grouping
    • Supported intervals: TIME(Xs), TIME(Xm), TIME(Xh), TIME(Xd), TIME(Xw), TIME(XM), TIME(Xy)
  • LIMIT: maximum number of results to return

Listing Measurements

TS.LIST

Lists all available measurements in the TSDB.

Data Model

The TSDB uses a structured data model with:

  1. Measurements: Similar to tables in a relational database
  2. Fields: The actual data values (numeric, string, boolean)
  3. Tags: Metadata for filtering and categorizing data
  4. Timestamp: Required for each record, in UTC

Performance Considerations

  • Group similar data in the same measurement
  • Use tags for frequently queried metadata
  • Limit the number of fields per measurement
  • Choose appropriate retention policies for your data
  • Use aggregation for long time ranges to reduce query time

Advanced Features

  • Retention Policies: Automatically expire old data
  • Aggregation Functions: Powerful data analysis capabilities
  • Time Bucketing: Group results by time intervals
  • High Write Throughput: Optimized for sensor and monitoring data
  • Efficient Compression: Reduces storage requirements

Example Use Cases

  • IoT Sensor Data: Store and analyze sensor readings
  • Application Metrics: Monitor application performance
  • Financial Data: Track market prices and trading volumes
  • Weather Data: Record and analyze weather conditions
  • User Analytics: Track user behavior over time