Что такое TimescaleDB?

TimescaleDB — это специализированная база данных временных рядов (time-series), построенная как расширение PostgreSQL. Она объединяет масштабируемость NoSQL систем с привычностью и надёжностью SQL, оптимизированная для работы с временными данными.

Ключевые особенности TimescaleDB

Полная SQL совместимость — все возможности PostgreSQL
Автоматическое партицирование — по времени и пространству
Hypertables — абстракция над партицированными таблицами
Непрерывные агрегаты — предвычисленные представления
Сжатие данных — экономия места на диске
Политики хранения — автоматическое удаление старых данных

Установка TimescaleDB

Установка на Ubuntu/Debian:

# Добавление репозитория TimescaleDB
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -

# Установка
sudo apt-get update
sudo apt-get install timescaledb-2-postgresql-14

# Настройка PostgreSQL
sudo timescaledb-tune
sudo systemctl restart postgresql

# Создание расширения в базе данных
psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

Установка через pip для разработки:

# Установка клиентских библиотек
pip install psycopg2-binary

# Для работы с аналитикой
poetry add psycopg2-binary pandas matplotlib

Создание hypertable

-- Создание обычной таблицы
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    location TEXT
);

-- Преобразование в hypertable
SELECT create_hypertable('sensor_data', 'time');

-- Создание индексов для лучшей производительности
CREATE INDEX ON sensor_data (sensor_id, time DESC);
CREATE INDEX ON sensor_data (location, time DESC);

Работа с данными временных рядов

-- Вставка данных
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location)
VALUES 
    (NOW(), 1, 22.5, 65.2, 'office'),
    (NOW() - INTERVAL '1 minute', 1, 22.3, 65.5, 'office'),
    (NOW() - INTERVAL '2 minutes', 2, 21.8, 64.1, 'warehouse');

-- Запросы с временными функциями
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) as avg_temp,
    MAX(temperature) as max_temp,
    MIN(temperature) as min_temp
FROM sensor_data 
WHERE time >= NOW() - INTERVAL '1 day'
GROUP BY hour, sensor_id
ORDER BY hour DESC;

-- Поиск аномалий
SELECT time, sensor_id, temperature
FROM sensor_data
WHERE temperature > (
    SELECT AVG(temperature) + 2 * STDDEV(temperature)
    FROM sensor_data
    WHERE time >= NOW() - INTERVAL '1 hour'
);

Непрерывные агрегаты

-- Создание непрерывного агрегата для почасовой статистики
CREATE MATERIALIZED VIEW hourly_stats
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) as avg_temp,
    MAX(temperature) as max_temp,
    MIN(temperature) as min_temp,
    COUNT(*) as readings_count
FROM sensor_data
GROUP BY hour, sensor_id;

-- Настройка автоматического обновления
SELECT add_continuous_aggregate_policy('hourly_stats',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

Политики хранения и сжатия

-- Настройка автоматического удаления старых данных
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- Настройка сжатия данных старше 7 дней
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

Интеграция с Python

import psycopg2
import pandas as pd
from datetime import datetime, timedelta

# Подключение к базе данных
conn = psycopg2.connect(
    host="localhost",
    database="timeseries_db",
    user="postgres",
    password="password"
)

# Массовая вставка данных
def insert_sensor_data(sensor_id, readings):
    cursor = conn.cursor()
    
    insert_query = """
        INSERT INTO sensor_data (time, sensor_id, temperature, humidity)
        VALUES %s
    """
    
    data = [(reading['time'], sensor_id, reading['temp'], reading['humidity'])
            for reading in readings]
    
    psycopg2.extras.execute_values(cursor, insert_query, data)
    conn.commit()

# Запрос данных с pandas
def get_sensor_stats(sensor_id, hours_back=24):
    query = """
        SELECT 
            time_bucket('1 hour', time) as hour,
            AVG(temperature) as avg_temp,
            MAX(temperature) as max_temp,
            MIN(temperature) as min_temp
        FROM sensor_data
        WHERE sensor_id = %s AND time >= NOW() - INTERVAL '%s hours'
        GROUP BY hour
        ORDER BY hour
    """
    
    return pd.read_sql_query(query, conn, params=[sensor_id, hours_back])

# Поиск аномалий
def detect_anomalies(sensor_id, threshold=2.0):
    query = """
        WITH stats AS (
            SELECT AVG(temperature) as mean_temp, 
                   STDDEV(temperature) as stddev_temp
            FROM sensor_data 
            WHERE sensor_id = %s AND time >= NOW() - INTERVAL '1 day'
        )
        SELECT time, temperature, 
               ABS(temperature - stats.mean_temp) / stats.stddev_temp as z_score
        FROM sensor_data, stats
        WHERE sensor_id = %s 
          AND time >= NOW() - INTERVAL '1 hour'
          AND ABS(temperature - stats.mean_temp) / stats.stddev_temp > %s
        ORDER BY time DESC
    """
    
    return pd.read_sql_query(query, conn, params=[sensor_id, sensor_id, threshold])

Мониторинг и оптимизация

-- Просмотр информации о hypertables
SELECT * FROM timescaledb_information.hypertables;

-- Статистика по chunks (партициям)
SELECT * FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start DESC;

-- Статистика сжатия
SELECT 
    hypertable_name,
    total_chunks,
    number_compressed_chunks,
    before_compression_total_bytes,
    after_compression_total_bytes,
    compression_ratio
FROM timescaledb_information.compression_stats;

Случаи использования TimescaleDB

IoT и сенсорные данные — сбор метрик с устройств
Мониторинг инфраструктуры — DevOps метрики
Финансовые данные — цены акций, торговые данные
Логирование приложений — анализ производительности
Промышленная аналитика — данные с производственных линий

Преимущества TimescaleDB

Полная совместимость с PostgreSQL и его экосистемой
Автоматическая оптимизация для временных данных
Эффективное сжатие и управление хранением
Масштабируемость до миллионов записей в секунду
Богатые возможности аналитики с SQL

FAQ

В чём разница между TimescaleDB и InfluxDB?

TimescaleDB предоставляет полную SQL совместимость и интеграцию с PostgreSQL экосистемой, в то время как InfluxDB имеет собственный язык запросов и оптимизирован исключительно для временных рядов.

Можно ли использовать TimescaleDB для обычных реляционных данных?

Да, поскольку TimescaleDB — это расширение PostgreSQL, ты можешь использовать обычные таблицы наряду с hypertables в одной базе данных.

Что такое TimescaleDB?

TimescaleDB - специализированная база данных временных рядов, построенная на PostgreSQL