Distributed TimescaleDB - RndTechConf 2021

distributed-timescaledb-rndtechconf2021.md

Distributed TimescaleDB

Time-series данные в распределенном кластере TimescaleDB поверх ОРСУБД PostgreSQL

Ссылка на актуальные слайды: Google Презентация

Ссылка на PDF версию

Мои предыдущие выступления на тему TimescaleDB

• PGConf.Russia 2019. PostgreSQL + PostGIS + TimescaleDB. Хранилище для систем мониторинга транспорта

• HighLoad 2019. Time series данные в реляционной СУБД. Расширения TimescaleDB и PipelineDB для PostgreSQL

• Выпуски RU Postgres

Сниппет с гипертаблицей

CREATE TABLE telemetries (
    imei        TEXT                NOT NULL,
    time        TIMESTAMPTZ         NOT NULL,
    latitude    DOUBLE PRECISION    NOT NULL,
    longitude   DOUBLE PRECISION    NOT NULL,
    speed       SMALLINT            NOT NULL,
    course      SMALLINT            NOT NULL,
    CONSTRAINT telemetries_pkey PRIMARY KEY (imei, time)
);

SELECT * FROM create_hypertable (
    'telemetries', 'time',
    chunk_time_interval => INTERVAL '7 days'
);

INSERT INTO telemetries VALUES ...;

SELECT
    time_bucket('30 days', time) AS bucket,
    imei,
    avg(speed) AS avg,
    max(speed) AS max
FROM telemetries
WHERE speed > 0
GROUP BY imei, bucket
ORDER BY imei, bucket;

Сниппет с распределенной гипертаблицей

CREATE TABLE telemetries (
    imei        TEXT                NOT NULL,
    time        TIMESTAMPTZ         NOT NULL,
    latitude    DOUBLE PRECISION    NOT NULL,
    longitude   DOUBLE PRECISION    NOT NULL,
    speed       SMALLINT            NOT NULL,
    course      SMALLINT            NOT NULL,
    CONSTRAINT telemetries_pkey PRIMARY KEY (imei, time)
);

SELECT * FROM add_data_node('data_node_1', host => 'pg_data_node_1');
SELECT * FROM add_data_node('data_node_2', host => 'pg_data_node_2');
SELECT * FROM add_data_node('data_node_3', host => 'pg_data_node_3');

SELECT * FROM create_distributed_hypertable(
    'telemetries', 'time', 'imei',
    chunk_time_interval => INTERVAL '7 days'
);

INSERT INTO telemetries VALUES ...;

SELECT
    time_bucket('30 days', time) AS bucket,
    imei,
    avg(speed) AS avg, max(speed) AS max
FROM telemetries
WHERE speed > 0
GROUP BY imei, bucket
ORDER BY imei, bucket;

Полезняшки

• https://krd.dev

• https://t.me/krddevdays

• https://www.timescale.com

• https://blog.timescale.com

• https://hub.docker.com/r/binakot/postgresql-postgis-timescaledb