TimescaleDB

概要

OSS の時系列データベース

SQL を喋る (Postgres の拡張)

TimescaleDB と PostgreSQL DB は単一の PostgresSQL サーバーに同居できる

インタフェース言語として SQL を利用する

テーブル、インデックス、トリガー ような標準的なデータベースオブジェクトを保持している

主要な概念

概要

PostgreSQL の拡張なので、PostgreSQL インスタンス上で動作する

信頼性やセキュリティ、様々なサードパーティ製ツール等の PostgreSQL のメリットを享受できる

Hypertables

全期間内の全データが保持されているように見える抽象的なテーブル

カラム名とタイプ、少なくとも１つの時間を指定するカラム、そして追加のパーティショニングキー (option) で定義される

Chunks

単一の chunk は、特定の期間及びパーティションキーの範囲に対応する

クエリプランナーは、クエリを解決するのにアクセスする必要のある chunk 数を最小化するように動く

各 chunk の実態は、データベーステーブルになる

データモデル

サンプルデータとして、1000 の IoT デバイスが様々な環境からデータを取得しているパターンを考える。

table:sample

timestamp device_id cpu_1m_avg free_mem temperature location_id dev_type

2017-01-01 01:02:00 abc123 80 500MB 72 335 field

2017-01-01 01:02:23 def456 90 400MB 64 335 roof

2017-01-01 01:02:30 ghi789 120 0MB 56 77 roof

2017-01-01 01:03:12 abc123 80 500MB 72 335 field

2017-01-01 01:03:35 def456 95 350MB 64 335 roof

2017-01-01 01:03:42 ghi789 100 100MB 56 77 roof

narrow-table model

ほとんどの時系列データベースはデータを以下の方法で表現している。

各メトリクスを別々のエンティティとして表現する

code:json

1. {name: cpu_1m_avg, device_id: abc123, location_id: 335, dev_type: field}

2. {name: cpu_1m_avg, device_id: def456, location_id: 335, dev_type: roof}

3. {name: cpu_1m_avg, device_id: ghi789, location_id: 77, dev_type: roof}

4. {name: free_mem, device_id: abc123, location_id: 335, dev_type: field}

5. {name: free_mem, device_id: def456, location_id: 335, dev_type: roof}

6. {name: free_mem, device_id: ghi789, location_id: 77, dev_type: roof}

7. {name: temperature, device_id: abc123, location_id: 335, dev_type: field}

8. {name: temperature, device_id: def456, location_id: 335, dev_type: roof}

9. {name: temperature, device_id: ghi789, location_id: 77, dev_type: roof}

narrow モデルは、メトリクス毎にタイムスタンプを保持する必要があるため、同一タイムスタンプで保持するメトリクス数が多いとパフォーマンスが落ちるし、ストレージも圧迫する。また、関係する異なるメトリクスを取得しようとすると、メトリクス毎にJOINが必要になるため、クエリが複雑になる。複数のメトリクスを一緒に取得する場合は、wide table フォーマットでそれらを保持した方が良い。

wide-table model

異なるメトリクス同士を一度に取得するのに JOIN が必要ない方式。複数のメトリクスに対してタイムスタンプが１つしか保持されない。タイムスタンプを識別子とした、典型的な RDB のテーブルと一緒。

関連データとの JOIN

table:location

location_id name latitude longitude zip_code region

42 Grand Central Terminal 40.7527° N 73.9772° W 10017 NYC

77 Lobby 7 42.3593° N 71.0935° W 02139 Massachusetts

このような外部テーブルを非正規化して１つの巨大なテーブルを作ることもできるが、そうするとデータが肥大化するし、管理が難しくなる。

試してみる

立ち上げ

code:sh

docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg11

# コンテナ内のクライアントソフトを利用してアクセスできる

docker exec -it timescaledb psql -U postgres

code:sql

-- データベースの作成

CREATE database tutorial;

-- データベースにアクセス

\c tutorial

-- TimescaleDB で拡張する

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

hypertable の作成

code:sql

CREATE TABLE conditions (

time TIMESTAMPTZ NOT NULL,

location TEXT NOT NULL,

temperature DOUBLE PRECISION NULL

);

-- カラムを追加するとき

ALTER TABLE conditions

ADD COLUMN humidity DOUBLE PRECISION NULL;

-- hypertable を作成する

SELECT create_hypertable('conditions', 'time');

-- データの挿入

INSERT INTO conditions(time, location, temperature, humidity)

VALUES (NOW(), 'office', 70.0, 50.0);

INSERT INTO conditions

VALUES

(NOW(), 'office', 70.0, 50.0),

(NOW(), 'basement', 66.5, 60.0),

(NOW(), 'garage', 77.0, 65.2);

-- データの取得

tutorial=# SELECT * FROM conditions LIMIT 100;

time | location | temperature | humidity

-------------------------------+----------+-------------+----------

2019-05-11 05:36:09.472717+00 | office | 70 | 50

2019-05-11 05:36:17.084085+00 | office | 70 | 50

2019-05-11 05:36:17.084085+00 | basement | 66.5 | 60

2019-05-11 05:36:17.084085+00 | garage | 77 | 65.2

(4 rows)

-- 過去三時間において、各場所における15分毎の情報を、時間及び温度で並び替えして出力する

SELECT time_bucket('15 minutes', time) AS fifteen_min,

location, COUNT(*),

MAX(temperature) AS max_temp,

MAX(humidity) AS max_hum

FROM conditions

WHERE time > NOW() - interval '3 hours'

GROUP BY fifteen_min, location

ORDER BY fifteen_min DESC, max_temp DESC;

特殊なデータの取得方法がいくつかある。