Skip to main content
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.
⌘I