TimescaleDB - RelytONE docs

Relyt ONE integrates TimescaleDB, an open-source time-series database extension for PostgreSQL. We use TimescaleDB Apache 2 Edition v2.21.3 to provide powerful time-series capabilities. For a detailed comparison of features between Apache 2 Edition and Community Edition, see the TimescaleDB editions feature comparison.

What is TimescaleDB?

TimescaleDB is a powerful PostgreSQL extension that transforms your database into a high-performance time-series database. It’s designed for handling large volumes of time-stamped data while maintaining full SQL compatibility and leveraging PostgreSQL’s reliability. Key benefits:

Automatic partitioning via hypertables for efficient time-series data storage
Fast queries with optimized indexing and query planning
Full SQL support - no need to learn new query languages
Horizontal scaling capabilities for massive datasets
Built-in functions for time-series analysis

Prerequisites

Before getting started, ensure you have:

An active Relyt database connection
TimescaleDB extension enabled on your database
Basic familiarity with SQL and PostgreSQL

Quick Start

Step 1: Enable TimescaleDB Extension

First, connect to your Relyt database and enable the TimescaleDB extension:

-- Connect to your database and enable TimescaleDB
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Verify the extension is installed
\dx

You should see timescaledb listed among your installed extensions.

Step 2: Create Your First Hypertable

A hypertable is TimescaleDB’s core abstraction - it looks like a regular PostgreSQL table but is automatically partitioned by time.

-- Create a regular table first
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    location TEXT
);

-- Convert it to a hypertable
SELECT create_hypertable('sensor_data', by_range('time'));

The time column must be the first argument to create_hypertable() and should be of type TIMESTAMPTZ, TIMESTAMP, or DATE.

Always include a time column in your hypertables and ensure it’s properly indexed for optimal performance.

Step 3: Insert Time-Series Data

Insert data just like you would with any PostgreSQL table:

-- Insert sample sensor readings
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location) VALUES
    (NOW() - INTERVAL '1 hour', 1, 22.5, 45.0, 'living_room'),
    (NOW() - INTERVAL '45 minutes', 1, 22.8, 44.5, 'living_room'),
    (NOW() - INTERVAL '30 minutes', 2, 19.2, 52.0, 'bedroom'),
    (NOW() - INTERVAL '15 minutes', 2, 19.5, 51.5, 'bedroom'),
    (NOW(), 3, 15.8, 68.0, 'outside');

-- Insert bulk data efficiently
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location)
SELECT 
    generate_series(NOW() - INTERVAL '7 days', NOW(), INTERVAL '5 minutes') AS time,
    (random() * 3 + 1)::INTEGER AS sensor_id,
    (random() * 10 + 15)::DOUBLE PRECISION AS temperature,
    (random() * 30 + 40)::DOUBLE PRECISION AS humidity,
    CASE (random() * 2)::INTEGER 
        WHEN 0 THEN 'indoor'
        ELSE 'outdoor'
    END AS location;

Step 4: Query Time-Series Data

TimescaleDB provides powerful functions for time-series analysis:

-- Get latest readings per sensor
SELECT DISTINCT ON (sensor_id) 
    sensor_id, 
    time, 
    temperature, 
    humidity, 
    location
FROM sensor_data 
ORDER BY sensor_id, time DESC;

-- Calculate hourly averages using time_bucket
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_data 
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour, sensor_id
ORDER BY hour DESC, sensor_id;

-- Find temperature trends over the last week
SELECT 
    time_bucket('1 day', time) AS day,
    location,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp,
    AVG(temperature) AS avg_temp
FROM sensor_data 
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY day, location
ORDER BY day DESC;

Essential TimescaleDB Functions

time_bucket()

The most important function for time-series analysis - groups data into time intervals:

-- 15-minute intervals
SELECT time_bucket('15 minutes', time) AS bucket, AVG(temperature)
FROM sensor_data 
GROUP BY bucket 
ORDER BY bucket;

-- Daily intervals with offset
SELECT time_bucket('1 day', time, '6 hours'::INTERVAL) AS bucket, COUNT(*)
FROM sensor_data 
GROUP BY bucket;

first() and last()

Get the first or last value in a time range:

-- Get first and last temperature reading per day
SELECT 
    time_bucket('1 day', time) AS day,
    first(temperature, time) AS first_temp,
    last(temperature, time) AS last_temp
FROM sensor_data 
GROUP BY day 
ORDER BY day;

Continuous Aggregates

Pre-compute common queries for faster performance:

-- Create a continuous aggregate for hourly averages
CREATE MATERIALIZED VIEW hourly_sensor_avg AS
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_data 
GROUP BY hour, sensor_id;

-- Query the pre-computed view
SELECT * FROM hourly_sensor_avg 
WHERE hour > NOW() - INTERVAL '24 hours'
ORDER BY hour DESC;

Common Use Cases

IoT Sensor Data

Store and analyze readings from temperature, humidity, and other sensors with automatic time-based partitioning.

Application Metrics

Monitor application performance, response times, and system metrics with efficient time-series queries.

Financial Data

Track stock prices, trading volumes, and market data with millisecond precision.

Log Analytics

Store and analyze application logs, system events, and audit trails over time.

Learn More

This guide covers the basics of using TimescaleDB in Relyt ONE. For comprehensive documentation on advanced features, best practices, and detailed API references, visit the official TimescaleDB documentation:

TimescaleDB Official Documentation

Explore advanced features like data retention, compression, continuous aggregates, and performance optimization.

Getting Started: Begin with simple hypertables and basic queries. As your data grows, explore advanced features like continuous aggregates and compression in the official TimescaleDB docs.

​What is TimescaleDB?

​Prerequisites

​Quick Start

​Step 1: Enable TimescaleDB Extension

​Step 2: Create Your First Hypertable

​Step 3: Insert Time-Series Data

​Step 4: Query Time-Series Data

​Essential TimescaleDB Functions

​time_bucket()

​first() and last()

​Continuous Aggregates

​Common Use Cases

IoT Sensor Data

Application Metrics

Financial Data

Log Analytics

​Learn More

TimescaleDB Official Documentation

What is TimescaleDB?

Prerequisites

Quick Start

Step 1: Enable TimescaleDB Extension

Step 2: Create Your First Hypertable

Step 3: Insert Time-Series Data

Step 4: Query Time-Series Data

Essential TimescaleDB Functions

time_bucket()

first() and last()

Continuous Aggregates

Common Use Cases

Learn More