Quick start

Basic concepts

• Time series data: A sequence of data points recorded in chronological order. Each data point contains not only a numeric value but also an associated timestamp, reflecting the trend or pattern of a variable over time. Time series data is widely used in various fields, such as financial transactions, meteorological observations, sensor monitoring, website traffic analysis, and epidemic studies. Key elements of time series data include:

  • Timestamp: Each data point has a clear time marker indicating when the data was collected.

  • Observed value: The data value measured or recorded at each time point.

  • Sequential order: Data points are arranged in chronological order, reflecting the natural temporal relationship between data points and giving time series data its time-series characteristics.

  • Trends and periodicity: Time series data analysis often focuses on long-term trends, seasonal fluctuations, cyclical changes, and random variations in the data.

• Metrics: Monitoring indicators for time series data, such as temperature, stock prices, and network traffic — any quantifiable measurement. Metrics help understand behavior patterns of time series data, predict future trends, and detect and respond to anomalies in a timely manner.

• Aggregation: The process of consolidating higher-frequency data into lower-frequency data in time series analysis. In simple terms, data is merged or summarized along the time dimension to reduce granularity while retaining key trends or features. This approach is useful for data reduction, trend analysis, anomaly detection, and predictive model building.

• Downsampling: A common operation in time series data processing that reduces the sampling frequency of time series data — that is, reduces the number of data points while attempting to retain the key features or trends of the original data. Common downsampling methods include:

  • Decimation: The most direct method, simply taking every Nth data point. For example, if the original sequence is sampled 10 times per second, you can keep only the first of every 5 points, achieving a sampling rate of 2 times per second. This method is simple but may lose high-frequency information.

  • Averaging: Before downsampling, consecutive data points are averaged. For example, the average of 5 consecutive points becomes a single new data point. This method helps smooth data and reduce noise, but may obscure rapid changes.

  • Median: Similar to averaging, but uses the median of consecutive data points instead of the mean. This is more robust when processing time series that contain outliers.

  • Max/Min: Selects the maximum or minimum value from consecutive data points as the new data point, suitable for scenarios where peak or valley values must be preserved.

  • Resampling: Adjusts data to new time points through interpolation or other methods, then performs uniform sampling on the new timeline. Common resampling methods include linear interpolation, nearest neighbor interpolation, and cubic interpolation. Resampling methods are more flexible and can adjust to any sampling frequency while preserving the data distribution as much as possible.

  • Model-based downsampling: Uses statistical or machine learning models to predict data points at lower sampling rates, such as using ARIMA or LSTM models to predict values at each new time point, and then uses these predicted values as the downsampled data. This method enables more intelligent downsampling but has higher computational costs.

Ganos TSDB vs. TimescaleDB

• TimescaleDB: An open-source time-series database built on PostgreSQL, optimized for handling time-series data such as sensor data, monitoring metrics, and financial data. Its design goal is to provide efficient storage, fast writes, and complex query capabilities for time-series data while retaining full PostgreSQL SQL functionality.

• Ganos TSDB: A time-series engine developed on top of the open-source TimescaleDB, running on PolarDB for PostgreSQL clusters as a plug-in. It fully inherits all PolarDB capabilities such as shared storage, one primary node with multiple read-only nodes, and backup and recovery.

Differences

The two differ primarily in the features they provide. TimescaleDB Apache 2 Edition is the version of TimescaleDB released under the Apache 2.0 license. The Apache 2.0 open-source license allows anyone to use the code and provide it as a service, but only basic features are included.

In contrast, Ganos TSDB is fully compatible with TimescaleDB and additionally provides advanced features such as time-series Continuous aggregates, data compression, and OSS tiered storage for hot and cold data.

Enable the time-series database

To enable the time-series database capability in a PolarDB for PostgreSQL cluster, add timescaledb to the shared_preload_libraries parameter. You can modify the shared_preload_libraries parameter in the console.

-- Upgrade TimescaleDB first
ALTER EXTENSION timescaledb UPDATE;

-- Then upgrade ganos_tsdb
ALTER EXTENSION ganos_tsdb UPDATE;

Hypertables

A hypertable is a special type of table provided by the time-series database that can easily handle time-series data. All operations that can be performed on regular tables can also be performed on hypertables.

Features

• Hypertables automatically partition data by time. You interact with hypertables the same way as regular tables, but hypertables also have additional features that make it easier to manage time-series data.

• In the time-series database, hypertables coexist with regular tables. Use hypertables to store time-series data to improve write and query performance, and to support sequence functions on hypertables.

• With hypertables, the time-series database can partition time-series data based on time parameters. In the background, the database automatically sets up and maintains hypertable partitions.

SELECT create_hypertable(
    'table_name',
    'time_column_name',
    'partitioning_column_name',
    number_partitions,
    'associated_schema_name',
    'associated_table_prefix',
    chunk_time_interval,
    create_default_indexes,
    if_not_exists,
    partitioning_func,
    migrate_data,
    chunk_target_size,
    chunk_sizing_func,
    time_partitioning_func
);

SELECT set_chunk_time_interval(
    'table_name',
    chunk_time_interval,
    'dimension_name'
);

Continuous aggregates

Continuous aggregates are an automated precomputation mechanism that periodically or in real time precompute predefined aggregation results such as hourly averages, converting complex queries into fast reads of precomputed results. They are similar to materialized views but optimized for time-series data, supporting incremental updates and avoiding repeated computation of historical data to accelerate queries on very large datasets.

Benefits

• Query acceleration: Query precomputed results directly to avoid full table scans of raw data.

• Resource savings: Reduce CPU and memory consumption for real-time computation.

• Automated maintenance: Automatically handle aggregation updates for new and old data without manual triggering.

CREATE MATERIALIZED VIEW transaction_min_cagg
    WITH (timescaledb.continuous) -- Declare as a continuous aggregate
    AS
    SELECT id,
        time_bucket(INTERVAL '1 min', tm) AS bucket, -- Aggregate by 1-minute intervals
        AVG(price),
        MAX(price),
        MIN(price)
    FROM transaction_data
    GROUP BY id, bucket;

integer add_job(
                proc REGPROC,
                schedule_interval INTERVAL,
                config JSONB DEFAULT NULL,
                initial_start TIMESTAMPTZ DEFAULT NULL,
                scheduled BOOL DEFAULT true,
                check_config REGPROC DEFAULT NULL,
                fixed_schedule BOOL DEFAULT TRUE,
                timezone TEXT DEFAULT NULL
                );

void alter_job(    job_id INTEGER,
        schedule_interval INTERVAL = NULL,
        max_runtime INTERVAL = NULL,
        max_retries INTEGER = NULL,
        retry_period INTERVAL = NULL,
        scheduled BOOL = NULL,
        config JSONB = NULL,
        next_start TIMESTAMPTZ = NULL,
        if_exists BOOL = FALSE,
        check_config REGPROC = NULL,
        fixed_schedule BOOL = NULL,
        initial_start TIMESTAMPTZ = NULL,
        timezone TEXT DEFAULT NULL
        );

SELECT alter_job(1002, schedule_interval => INTERVAL '2 hours');

void run_job(job_id int);

CALL run_job(1002);

void delete_job(job_id int);

SELECT delete_job(1002);

refresh_continuous_aggregate(
    cagg     REGCLASS,
    window_start             "any",
    window_end               "any"
    );

CALL refresh_continuous_aggregate('transaction_min_cagg', '2024-03-11 00:00:00+08'::timestamptz, NULL);

integer add_continuous_aggregate_policy(
                cagg REGCLASS,
                start_offset "any",
                end_offset "any",
                schedule_interval INTERVAL,
                if_not_exists BOOL = false,
                initial_start TIMESTAMPTZ = NULL,
                timezone TEXT = NULL
                );

SELECT add_continuous_aggregate_policy('transaction_min_cagg',
    start_offset => '1 hour', -- Process data from 1 hour ago (to ensure data completeness)
    end_offset => INTERVAL '0', -- Process up to the current time
    schedule_interval => INTERVAL '1 sec' -- Refresh every second
    );

Real-time queries

When querying continuous aggregates, if real-time query is not enabled, only data with completed aggregations is accessible. You can enable real-time query to access newly ingested data that has not yet been aggregated.

• Enable real-time query

  ALTER MATERIALIZED VIEW transaction_min_cagg SET (timescaledb.materialized_only = false);

• Disable real-time query

  ALTER MATERIALIZED VIEW transaction_min_cagg SET (timescaledb.materialized_only = true);

Nested aggregates

Nested aggregates refer to creating continuous aggregates on top of materialized views generated by other continuous aggregates. For example, you can create hourly aggregates on top of per-minute aggregates, and monthly aggregates on top of daily aggregates. This reduces data redundancy and improves aggregation performance.

1. Create the first-level aggregate. In this example, a per-minute aggregate is created.

   CREATE MATERIALIZED VIEW transaction_min_cagg
       WITH (timescaledb.continuous) AS
       SELECT id,
           time_bucket(INTERVAL '1 min', tm) AS bucket,
           AVG(price) as avg,
           MAX(price) as max,
           MIN(price) as min
       FROM transaction_data
       GROUP BY id, bucket;

2. Create a nested aggregate. Create an hourly aggregate on top of the per-minute aggregate.

   CREATE MATERIALIZED VIEW transaction_one_hour_mview
       WITH (timescaledb.continuous) AS
       SELECT id,
           time_bucket(INTERVAL '1 hour', bucket) AS bucket,
           AVG(avg) as avg,
           MAX(max) as max,
           MIN(min) as min
       FROM transaction_min_cagg
       GROUP BY id, time_bucket(INTERVAL '1 hour', bucket);

Time-series compression

• When a time-series partition is identified as historical data, you can enable the data compression feature provided by the time-series database. Ganos TSDB supports compressing entire tables or specific partitions. Compression can reduce data storage space by more than 70%, significantly lowering storage costs.

• Compressed data is read-only.

Uninstall the plug-in

DROP EXTENSION ganos_tsdb CASCADE;
DROP EXTENSION timescaledb CASCADE;