2.7.8 TimescaleDB

TimescaleDB is a database designed for handling time-series data (data that changes over time), such as metrics, sensor readings, or logs. It is built as an extension of PostgreSQL.

How TimescaleDB Stores Data:

TimescaleDB stores data in a structure called a hypertable.

• A hypertable looks like a normal table but is automatically split into smaller pieces called chunks.
• These chunks are organized based on time intervals.
• Each record typically contains:
  • A timestamp
  • One or more values (e.g., measurements)

This design allows TimescaleDB to efficiently manage large amounts of time-based data.

Chunks

• Chunks are smaller partitions of a hypertable.
• Each chunk represents a specific time interval (e.g., one day or one week).
• When querying data, only the relevant chunks are scanned, improving performance.

Retention Policy

A retention policy controls how long data is kept in the database. Default is 3 months period set.

• Older chunks can be automatically deleted after a defined period.
• This helps manage storage and keeps the database size under control.
• Only data within the retention period remains available for queries.

Example concept:

• Keep data for 30 days -> chunks older than 30 days are removed.

Compression Policy

A compression policy reduces storage usage for older data. Default is compress chunks after 14 days.

• Older chunks can be compressed automatically.
• Compression reduces disk space and improves query efficiency for historical data.
• Compressed data is still queryable but may be slightly slower to access than recent (uncompressed) data.

Typical usage:

• Recent data -> uncompressed (fast writes and reads)
• Older data -> compressed (optimized storage)

How TimescaleDB Works

When data is inserted:

• It is automatically placed into the correct time-based chunk.
• The system organizes data behind the scenes without requiring manual partitioning.

When data is read:

• Only the relevant chunks (based on time range) are accessed.
• This makes queries faster, especially for large datasets.

What is time_bucket?

time_bucket is a concept used to group data into fixed time intervals (also called resolution).

For example:

• Grouping data into 1-minute, 5-minute, or 1-hour intervals.

Instead of looking at every individual data point, data is grouped into these time buckets.

How time_bucket Affects Native Data

• The original (raw) data is not modified or lost.
• time_bucket only affects how data is grouped during reading/visualization.
• It creates a summarized view of the data over time intervals.

So:

• Raw data remains intact in the database.
• Bucketing simply provides a processed view of that data.

Within each time bucket:

• Min (Minimum): The smallest value in that time interval.
• Max (Maximum): The largest value in that time interval.
• Avg (Average): The sum of all values divided by the number of values in that interval.

Example concept:

• If a 1-minute bucket contains 60 data points:
  • Min = lowest of those 60 values
  • Max = highest of those 60 values
  • Avg = average of those 60 values

Resolution

Resolution (bucket size) determines how detailed the data appears:

• Smaller buckets (high resolution)

  • More detail
  • More data points
  • Higher processing cost

• Larger buckets (low resolution)

  • Less detail
  • Fewer data points
  • Faster performance

Summary

• TimescaleDB stores time-series data efficiently using hypertables and chunks.
• Raw data is always preserved.
• time_bucket groups data into time intervals for analysis.
• Min, Max, and Avg are calculated per interval.
• Resolution controls the balance between detail and performance.

Note: This section gives a concise overview of TimescaleDB. For more detailed information, see the TimescaleDB documentation.