TimescaleDB Overview

TimescaleDB is an open-source time-series database that provides full SQL support. It is built as an extension of PostgreSQL, combining the power of traditional relational databases with time-series optimizations.

Primary Use Cases

TimescaleDB excels in scenarios involving:

1. Large Data Volumes: Handles massive amounts of time-stamped data efficiently
2. Time-Indexed Inserts: Optimized for append-heavy workloads where data is primarily written with timestamps
3. Minimal Updates: Ideal for data that remains relatively static after ingestion

Key Advantages

• Time-Series Optimization: Purpose-built architectural decisions for temporal data
• Automatic Partitioning: Automatic data chunking based on time intervals and spatial dimensions
• Full SQL Compatibility: Leverages standard SQL syntax and PostgreSQL features
• Scalability: Supports both vertical and horizontal scaling
• Multi-Dimensional Partitioning: Automatic partitioning by time and space (dimensions like sensor_id or user_id)
• Parallel Query Execution: Distributes queries across multiple servers and chunks
• Automatic Chunk Sizing: Dynamically adjusts chunk dimensions for optimal performance
• Write Optimizations: Batch commits, in-memory indexes, transaction support, and data backfilling capabilities
• Query Optimizations: Intelligent chunk selection, efficient value retrieval, LIMIT clause pushdown, and parallel aggregations
• PostgreSQL Ecosystem: Inherits GIS support, JOIN capabilities, streaming replication, and point-in-time recovery
• Automated Data Retention: Built-in policies for automatically purging outdated data

Working with Hypertables

Hypertable are the core abstraction in TimescaleDB, representing regular PostgreSQL tables that are automatically partitioned into smaller chunks.

Converting a Standard Table

-- Transform a regular table into a hypertable using a timestamp column
SELECT create_hypertable('sensor_data', 'recorded_at');

Hypertables behave like regular tables while providing transparent partitioning through the TimescaleDB extension.

Configuring Chunk Time Intervals

The chunk time interval determines how data is partitioned by time. The default interval is 7 days. When selecting an interval, consider that chunks belonging to recent time periods (including their indexes) should fit comfortably in memory, ideally not exceeding 25% of available RAM.

-- Create a hypertable with a one-day interval using microseconds
SELECT create_hypertable('environmental_readings', 'timestamp', chunk_time_interval => 86400000000);

-- Alternatively, use PostgreSQL's INTERVAL syntax
SELECT create_hypertable('environmental_readings', 'timestamp', chunk_time_interval => INTERVAL '1 day');

Modifying Existing Hypertables

-- Adjust the chunk interval for an existing hypertable
SELECT set_chunk_time_interval('sensor_data', INTERVAL '24 hours');
SELECT set_chunk_time_interval('sensor_data', 86400000000);

Adding Spatial Partitioning

-- Create a hypertable with 4 spatial partitions based on device location
SELECT create_hypertable('device_metrics', 'timestamp', 'location_id', 4);

Checking Hypertable Size

-- Retrieve the total storage size of a hypertable
SELECT hypertable_size('iot_devices');

Data Retention Policies

Retention policies automate the removal of historical data, helping manage storage costs and query performance.

Adding a Retention Policy

-- Automatically remove data older than 6 months
SELECT add_retention_policy('sensor_archive', INTERVAL '6 months');

Removing a Retention Policy

-- Disable the retention policy for a specific table
SELECT remove_retention_policy('sensor_archive');

Manual Chunk Deletion

-- Remove all chunks containing data older than 3 weeks
SELECT drop_chunks('market_data_stream', INTERVAL '3 weeks');

-- Remove chunks with data between 2 and 3 weeks old
SELECT drop_chunks(
  'market_data_stream',
  older_than => INTERVAL '2 weeks',
  newer_than => INTERVAL '3 weeks'
);

Continuous Aggregates

Continuous aggregations pre-compute and store query results, dramatically improving performance for frequently executed aggregation queries.

Standard Aggregation Query

-- Calculate daily OHLC (Open-High-Low-Close) data for stock prices
SELECT
  time_bucket('1 day', "timestamp") AS trading_day,
  stock_symbol,
  MAX(price) AS high_price,
  FIRST(price, timestamp) AS opening_price,
  LAST(price, timestamp) AS closing_price,
  MIN(price) AS low_price
FROM market_prices mp
GROUP BY trading_day, stock_symbol
ORDER BY trading_day DESC, stock_symbol;

Creating a Continuous Aggregate

Convert the standard aggregation into a continuous aggregate using a materialized view with the TimescaleDB continuous option:

CREATE MATERIALIZED VIEW daily_stock_summary
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', "timestamp") AS trading_day,
  stock_symbol,
  MAX(price) AS high_price,
  FIRST(price, timestamp) AS opening_price,
  LAST(price, timestamp) AS closing_price,
  MIN(price) AS low_price
FROM market_prices mp
GROUP BY trading_day, stock_symbol;

Querying the continuous aggregate provides instant access to pre-computed results:

SELECT * FROM daily_stock_summary
  ORDER BY trading_day DESC, stock_symbol;

By default, continuous aggregates operate in real-time mode, appending recent data that hasn't yet been materialized to the query results.

Configuring Automated Refresh Policies

-- Define a policy that refreshes materialized data daily
-- Materializes data from 3 days ago up to 1 hour ago
SELECT add_continuous_aggregate_policy('daily_stock_summary',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 day');

Manual Refresh Operations

Manual refresh is valuable for edge IoT scenarios where devices experience extended connectivity gaps:

-- Refresh one week of data into the continuous aggregate
CALL refresh_continuous_aggregate(
  'daily_stock_summary',
  now() - INTERVAL '1 week',
  now()
);

Data Compression

Compression significantly reduces storage requirements for historical data while maintaining query capability.

Enabling Compression

Three parameters control compression behavior:

-- Enable compression with custom ordering and segmentation
ALTER TABLE market_prices SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'timestamp DESC',
  timescaledb.compress_segmentby = 'stock_symbol'
);

• compress: Required flag to enable compression on the hypertable
• compress_orderby: Column used for sorting compressed data
• compress_segmentby: Column used for grouping compressed data

If not specifide, compression uses the hypertable's timestamp column for ordering.

Automated Compression Policies

-- Automatically compress chunks older than 60 days
SELECT add_compression_policy('server_cpu_stats', INTERVAL '60d');

-- Compress chunks containing data older than 2 weeks
SELECT add_compression_policy('market_prices', INTERVAL '2 weeks');

When executed, these policies immediately compress existing qualifying chunks and establish recurring compression schedules.

Important: Avoid compressing data that requires frequent updates. While compressed chunks accept new insertions, existing compressed rows cannot be modified or deleted. Compress data only after it has aged sufficiently.

Manual Compression

-- Compress chunks containing data older than 2 weeks
-- Skip already compressed chunks to prevent errors
SELECT compress_chunk(chunk_info, if_not_compressed => true)
  FROM show_chunks('market_prices', older_than => INTERVAL '2 weeks') AS chunk_info;

Verifying Compression Effectiveness

-- Compare compressed and uncompressed storage sizes
SELECT pg_size_pretty(pre_compression_bytes) AS "Before Compression",
  pg_size_pretty(post_compression_bytes) AS "After Compression"
  FROM hypertable_compression_stats('market_prices');

This comparison helps evaluate compression efficiency and inform policy adjustments.