DOCSEARCHbyAlgolia
Submit Docs

TimescaleDB Toolkit Documentation

repository·main·Indexed 19 days ago

https://github.com/timescale/timescaledb-toolkit

A PostgreSQL extension written in Rust providing high-performance analytical hyperfunctions for time-series data, including approximate aggregates, time-weighted averages, and downsampling utilities. The toolkit includes specialized tools like the aggregate_builder for custom Postgres aggregates, flat_serialize! for data layout serialization, and analytical features such as ASAP Smoothing, Hyperloglog, LTTB, T-Digest, and UddSketch.

Tokens
63.8K
Snippets
235
Records
289
Agent score
66%

What's inside TimescaleDB Toolkit

  1. Overview of Scripting Utilities

    main

    The scripting-utilities directory contains a collection of small, focused micro-crates designed to assist in writing 'scripty' code for tools. These crates provide deduplicated logic for common but slightly complex or irritating tasks that would otherwise require repetitive copy-pasting.

    To maintain fast compile times and prevent the utilities from becoming overly complex, the project uses a micro-crate architecture rather than a single large utility crate. This approach keeps each crate focused, easy to understand, and allows the compiler to take advantage of parallelism.

  2. Overview of Approximate Percentiles

    main

    The TimescaleDB Toolkit provides functionality for calculating approximate percentiles (quantiles) on large datasets. This is useful when exact percentile calculations are computationally expensive or too slow for real-time analysis.

    Note on terminology: While 'quantile' is the technically precise term for dividing a group into an arbitrary number of buckets, this toolkit uses the term 'percentile' to describe these operations.

  3. Available features in TimescaleDB Toolkit

    main

    The toolkit provides several utilities for time-series data analysis, categorized by their stability level:

    Experimental Features

    • ASAP Smoothing: A data smoothing algorithm designed to generate human-readable graphs that maintain erratic data behavior while smoothing away cyclic noise.
    • Hyperloglog: An approximate COUNT DISTINCT implementation based on hashing that provides reasonable accuracy in constant space.
    • LTTB (Largest Triangle Three Buckets): A downsampling method designed to preserve visual similarity in time-series data.

    Stable Features

    • Percentile Approximation: A simplified interface for percentile approximation that wraps lower-level algorithms.
      • T-Digest: A quantile estimate sketch optimized for accuracy near the tails (e.g., 0.001 or 0.995).
      • UddSketch: A quantile estimate sketch providing a guaranteed maximum relative error.

  4. What is a Timevector?

    main
    A timevector is a space-efficient intermediate representation of values over time used by the TimescaleDB Toolkit extension. It is designed to store the results of analytic functions (like asap_smooth or lttb) and can be used to pass aggregated data efficiently between functions. You can convert standard time-series data into a timevector, perform operations on it, and then use the unnest API to retrieve the original (time, value) pairs.

  5. What is the Update Tester and how does it work?

    main

    The Update Tester is a tool used to verify that upgrading the timescaledb_toolkit extension from previous versions to the current version works correctly.

    It automates the following lifecycle:

    1. Version Iteration: For every version listed in the upgradeable_from field of timescaledb_toolkit.control:
      • Checks out the corresponding git tag.
      • Builds and installs that specific version.
      • Resets the git state.
    2. Current Build: Builds and installs the extension from the current git state.
    3. Upgrade Validation: For each old version identified:
      • Creates a new database.
      • Installs the old version of the extension.
      • Populates the database with timescaledb_toolkit objects.
      • Performs the extension update.
      • Validates that the extension remains in the expected state.

    Warning: Running this tool modifies the git HEAD. It is highly recommended to run this only on a clean git tree to avoid conflicts and state issues.

  6. What is UddSketch?

    main

    UddSketch is a specialized data structure for estimating percentiles with a guaranteed maximum relative error. It is a variation of DDSketch designed to handle cases where the number of required buckets exceeds a predefined maximum.

    While DDSketch maintains its error bound by only providing estimates for a subset of the range, UddSketch combines buckets to loosen the error bound, allowing it to estimate all percentile values across the entire range. This makes it highly suitable for continuous aggregation in PostgreSQL.

  7. What is T-Digest and how does it work?

    main

    T-Digest is a space-efficient data structure implemented as a PostgreSQL aggregate function for quantile approximations. It provides increased resolution at the edges of a distribution, making it highly accurate for estimating extreme quantiles (e.g., the 99th or 99.9th percentile) compared to traditional methods.

    Key Characteristics:

    • Input Type: Currently restricted to DOUBLE PRECISION values.
    • Partializable: It is suitable for use with TimescaleDB Continuous Aggregates.
    • Order Sensitivity: While highly accurate, results may vary subtly if the input order changes (e.g., due to parallelization) or if digests are combined using rollup instead of being built from raw data points.

  8. What is ASAP Smoothing?

    main

    The ASAP (Autocorrelation-based Smoothing Algorithm) smoothing algorithm is designed to create human-readable graphs that preserve the rough shape and larger trends of input data while minimizing local variance.

    In TimescaleDB Toolkit, this is implemented as a PostgreSQL aggregate that takes (timestamp, value) pairs, normalizes them to a target resolution, and returns smoothed values. It works by bucketing points into even-sized intervals and identifying candidate intervals for smoothing using the Wiener-Khinchin theorem to find periods of high autocorrelation. This ensures the resulting graph is smooth while maintaining the same degree of outlier values.

  9. How Timevector Pipelines work

    main

    A Timevector Pipeline is a way to perform efficient operations on a timevector object. A pipeline is created by connecting a timevector to a pipeline element using the pipeline operator ->.

    Because most pipeline elements output a new timevector, you can chain multiple elements together. The output of one element becomes the input for the next.

    Operator Associativity

    Due to PostgreSQL parser limitations, the -> operator is left-associative.

    • timevector -> elementA -> elementB is equivalent to (timevector -> elementA) -> elementB.
    • To create a pipeline object from multiple elements first, use parentheses: timevector -> (elementA -> elementB). This second form is preferred as it allows for future internal optimizations.
    -- Left-associative (standard)
SELECT timevector -> elementA -> elementB;

-- Explicit grouping (preferred for multi-element pipelines)
SELECT timevector -> (elementA -> elementB);

  10. Choose between T-Digest and UddSketch for percentile approximation

    main

    The percentile_agg interface provides access to different approximation algorithms. Choosing the right one depends on whether you prioritize accuracy at the tails, stability, or error characterization.

    T-Digest

    Use T-Digest if you need high accuracy at the extremes (tails) of your data distribution (e.g., 0.001 or 0.995 quantiles) and can tolerate slightly less accuracy near the median.

    • Best for: Estimating 99th percentiles.
    • Behavior: Approximates the continuous percentile (similar to Postgres percentile_cont).
    • Trade-offs:
      • Provides more stable absolute error across the entire data range.
      • Estimates may vary slightly depending on data order or batching/parallelization.
      • Harder to calculate precise error bars.

    UddSketch

    Use UddSketch if you need guaranteed relative error, stable estimates, or a smaller memory footprint. This is the default algorithm used by percentile_agg.

    • Best for: Median estimates and scenarios requiring stable, reproducible results.
    • Behavior: Provides an estimate of the discrete value (similar to Postgres percentile_disc).
    • Trade-offs:
      • Stability: Uses a stable bucketing function, so it returns the same estimate for the same data regardless of order or reaggregation.
      • Error: Guarantees a known relative error range, but absolute error can vary wildly if the data covers a large range (e.g., error at the high end might be 100x larger than at the low end).
      • Efficiency: Generally has a smaller memory and disk footprint than T-Digest.

    Comparison Summary

    FeatureT-DigestUddSketch (Default)
    Primary AccuracyTails (0.001, 0.995)Median / General
    Percentile TypeContinuous (percentile_cont)Discrete (percentile_disc)
    StabilitySensitive to order/batchingStable (same result for same data)
    Error TypeStable Absolute ErrorGuaranteed Relative Error
    FootprintLargerSmaller
    Error BarsDifficult to characterizeEasier to characterize

  11. Distinguish between aggregation and accessor parameters

    main

    The two-step pattern explicitly separates parameters that define how data is collected (aggregation step) from parameters that define how data is extracted (accessor step). This prevents confusion and allows you to tune the accuracy of the aggregate independently of the value you are retrieving.

    Example with uddsketch: In the example below, the parameters 1000 and 0.001 belong to the uddsketch aggregate (controlling buckets and error target), while 0.5 belongs to the approx_percentile accessor.

    SELECT
    approx_percentile(0.5, uddsketch(1000, 0.001, val)) as median,
    approx_percentile(0.9, uddsketch(1000, 0.001, val)) as p90
FROM foo;

    Note that the optimizer will combine the two calls above because the uddsketch parameters are identical. If you change the uddsketch parameters (e.g., to 100, 0.01), the optimizer will treat it as a new calculation.

    SELECT
    approx_percentile(0.5, uddsketch(1000, 0.001, val)) as median,
    approx_percentile(0.9, uddsketch(1000, 0.001, val)) as p90
FROM foo;

  12. What are Gauge Aggregates

    main

    A gauge is a metric used to measure values that vary up and down over time (e.g., resource utilization, precipitation, or temperature), as opposed to a counter which only tracks ever-increasing counts.

    Note: gauge_agg is currently an experimental feature. It shares implementation with counter_agg but lacks resetting logic, which means it enforces data ordering even if the specific aggregate type does not strictly require it. An unordered version may be available in the future.