知っておきたい時系列データベース～TimescaleDB入門セミナー～
2021/6/24
SRA OSS, Inc. 日本支社
佐藤 友章
sato@sraoss.co.jp© 2021 SRA OSS, Inc. Japan

1

自己紹介
• 佐藤 友章 (さとう ともあき)• SRA OSSで技術部門の責任者を務める• PostgreSQLのサポートやトレーニング、コンサルティングなど、今もなおエンジニアとして活動中• PostgreSQLとの出会いは大学院時代、当時のバージョンは7.2• 趣味はお酒と海外旅行© 2021 SRA OSS, Inc. Japan2

時系列データベースとは© 2021 SRA OSS, Inc. Japan3

時系列データベースとは• 時系列データに最適化されたデータベース• 時系列データとは• 時間情報 (タイムスタンプ) をもった一連の値• 例えば、IoTセンサーやDevOps監視、株価・為替のデータ、など• 時系列データベースに求められるのは高速な同時書き込み

対話的な集計問い合わせ© 2021 SRA OSS, Inc. Japan

大量データの保存

4おもな時系列データベース製品• 1番人気はInfluxDB、TimescaleDBは5番手 (全35製品中)

DB-Engines Ranking - Trend of Time Series DBMS Popularity
https://db-engines.com/en/ranking_trend/time+series+dbms

1. InfluxDB: 独自のストレージエン
ジンをもつ時系列データベース
2. Kdb+: インメモリ機能をもつ列ベースの時系列データベース
3. Prometheus: HTTPプル型の時系
列データベースを含む監視ツール
4. Graphite: シンプルな時系列デー
タベースを含むグラフ化ツール
5. TimescaleDB: PostgreSQLベースの時系列データベースなど© 2021 SRA OSS, Inc. Japan

5

TimescaleDBとは© 2021 SRA OSS, Inc. Japan

6

TimescaleDBとは• 種別: 時系列データベース• ウェブサイト: https://www.timescale.com• 開発元: 米国Timescale社• 初版リリース: 2017年• ライセンス: Apache-2 (一部TSL)• 実装言語: C言語• コード量: 約8万行
© 2021 SRA OSS, Inc. Japan

7

TimescaleDBのライセンス
コミュニティ機能
自由に使えるオープンソース機能(ApacheLicense 2.0)

コミュニティ機能(TimescaleLicense)※ライセンスは各自で確認すること
https://www.timescale.com/legal/licenses• チャンクの並び替え・移動• 分散ハイパーテーブル• 圧縮• 継続的集計• データ保持• ユーザ定義アクション• 欠損値の補間

DBasS提供除き
自由に使える© 2021 SRA OSS, Inc. Japan

8

TimescaleDBの特長

使いやすさ

スケーラビリティ• SQLに完全対応• PostgreSQL用アプリ
ケーションがそのまま動作可能• 時系列データ向け分
析・管理機能あり• データを適切なサイズに分割して格納• データ量が増えても安定して高速に書き込み可能• 分散処理に対応© 2021 SRA OSS, Inc. Japan

信頼性• 長い歴史をもつ
PostgreSQL上で動作• バックアップやレプリケーションなど、PostgreSQLの機能・ツールが利用可能

9

TimescaleDBのアーキテクチャ
ハイパー
テーブル

チャンク

チャンク

チャンク

空間1

空間2

空間3

時間1

継承によるパーティ
ショニング…

時間2© 2021 SRA OSS, Inc. Japan

時間N
10

TimescaleDBのインストール© 2021 SRA OSS, Inc. Japan

11

TimescaleDBの利用方法• Timescale Cloudを使う• Timescale社提供のマネージドサービス• AWSとAzure、GCPに対応• 24米ドル/月～• 自前で構築して使う

TimescaleDB/PostgreSQL
バージョン対応
TimescaleDB
バージョン• UbuntuやDebian、RHEL/CentOS、Windows、macOS向けパッケージ、 2.4以降
Dockerイメージ、AMIが利用可能
2.1～2.3• ソースコードからもビルド可能
2.0
1.7© 2021 SRA OSS, Inc. Japan

PostgreSQL
バージョン
12、1312、1311、12
9.6、10、11、1212

TimescaleDBのインストール RHEL/CentOSの場合 (1)• TimescaleDBのYumリポジトリを登録$ sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOF[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/¥$releasever/¥$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOF© 2021 SRA OSS, Inc. Japan

13

TimescaleDBのインストール RHEL/CentOSの場合 (2)• TimescaleDBのRPMパッケージをインストール• コミュニティ機能を含まないパッケージtimescaledb-ossもあり$ sudo yum -y install timescaledb-2-postgresql-13(省略)
Installed:
timescaledb-2-loader-postgresql-13-2.3.0-0.el8.x86_64
timescaledb-2-postgresql-13-2.3.0-0.el8.x86_64
timescaledb-tools-0.11.0-0.el8.x86_64

ライブラリローダ
本体
クライアントツール

Complete!

© 2021 SRA OSS, Inc. Japan

14

TimescaleDBのインストール RHEL/CentOSの場合 (3)• TimescaleDB向けにPostgreSQLの設定を行う$ sudo timescaledb-tune --pg-config=/usr/pgsql-13/bin/pg_config
Using postgresql.conf at this path:/var/lib/pgsql/13/data/postgresql.conf
Is this correct? [(y)es/(n)o]: yWriting backup to:/tmp/timescaledb_tune.backup202106150658
shared_preload_libraries needs to be updated
Current:#shared_preload_libraries = ''Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated• timescaledb-tuneはCPU数やメモリ量をもとに対話的に設定を行うツール• 最低限必要な設定はカンマ区切り
でshared_preload_librariesにtimescaledbを追加するだけ

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y(省略)Saving changes to: /var/lib/pgsql/13/data/postgresql.conf

TimescaleDBのインストール RHEL/CentOSの場合 (4)• PostgreSQLのサービスを再起動し、TimescaleDBの拡張モ
ジュールをインストール$ sudo systemctl restart postgresql-13$ sudo su - postgres$ createdb test
$ psql -c "CREATE EXTENSION timescaledb" test
WARNING:WELCOME TO_____ ______________|__(_)| || _ ∖ ___ ∖| | _ _ __ ______ ___ ___ __ _| | ___| | | | |_/ /| | | | _ ` _ ∖ / _ ∖/ __|/ __/ _` | |/ _ ∖ | | | ___ ∖| | | | | | | | | __/∖__ ∖ (_| (_| | | __/ |/ /| |_/ /|_| |_|_| |_| |_|∖___||___/∖___∖__,_|_|∖___|___/ ∖____/
Running version 2.3.0(省略)
CREATE EXTENSION© 2021 SRA OSS, Inc. Japan

16

TimescaleDBの基本的な使い方© 2021 SRA OSS, Inc. Japan17

ハイパーテーブルの作成• 通常のテーブルを作成し、create_hypertable関数を実行• チャンクは7日間ごとに分割• 分割の間隔はchunk_time_interval引数で変更可能=# CREATE TABLE conditions (time timestamp NOT NULL,temperature double precision);
CREATE TABLE=# SELECT create_hypertable('conditions', 'time');create_hypertable------------------------(1,public,conditions,t)(1 行)© 2021 SRA OSS, Inc. Japan18

ハイパーテーブルの制限事項• ハイパーテーブルを参照する外部キー制約は作成できない• 分割条件の時間列にNULLは格納できない• 一意性インデックスに分割条件の全列を含める必要あり• データをチャンク間で移動できない© 2021 SRA OSS, Inc. Japan19

ハイパーテーブルのデータ操作• 通常のテーブルと基本的に変わらない• データ登録時に格納先のチャンクがなければ作成される=# INSERT INTO conditions VALUES (current_timestamp, 21.7);INSERT 0 1=# SELECT * FROM conditions;time| temperature---------------------+------------2021-06-14 10:00:00 |21.7(1 行)=# UPDATE conditions SET temperature = temperature + 0.1
WHERE time = '2021-06-14 10:00:00';
UPDATE 1© 2021 SRA OSS, Inc. Japan

20

チャンクの削除• チャンクごと削除すれば、不要領域が発生せずに済む=# SELECT drop_chunks('conditions', '2021-06-01');
drop_chunks---------------------------------------_timescaledb_internal._hyper_1_2_chunk_timescaledb_internal._hyper_1_3_chunk_timescaledb_internal._hyper_1_4_chunk_timescaledb_internal._hyper_1_5_chunk_timescaledb_internal._hyper_1_6_chunk(5 行)

VACUUM
必要不要領域

不要領域…

DELETEVACUUM不要…

drop_chunks© 2021 SRA OSS, Inc. Japan…

21

TimescaleDBによるデータ分析© 2021 SRA OSS, Inc. Japan22

time_bucket関数• 時間を任意の間隔で丸める=# SELECT time, temperature
FROM condition
ORDER BY time;
time|temperature---------------------+-------------------2021-06-14 00:00:00 | 21.80233243974021
2021-06-14 00:01:00 | 22.36631915597042:|:
2021-06-14 00:29:00 | 22.152689798625406(省略)

30分ごとに区切って平均を求める

26

24

平均
22
温度

=# SELECT time_bucket('30min', time) AS time_30min,avg(temperature)
FROM conditions
GROUP BY time_30min
ORDER BY time_30min;
time_30min|avg---------------------+-------------------2021-06-14 00:00:00 | 22.438319603197666
(省略)

20

18

16
0:00

© 2021 SRA OSS, Inc. Japan

6:00

12:00

18:00

0:00
23

first/last関数• 別の列をキーに並び替え、最初・最後の値を取得=# SELECT time, temperature
FROM condition
ORDER BY time;
time|temperature---------------------+-------------------2021-06-14 00:00:00 | 21.80233243974021
2021-06-14 00:01:00 | 22.36631915597042:|:
2021-06-14 00:29:00 | 22.152689798625406(省略)

30分ごとに区切って最初の値を取得

26

24

最初22
温度

=# SELECT time_bucket('30min', time) AS time_30min,first(temperature, time)
FROM conditions
GROUP BY time_30min
ORDER BY time_30min;
time_30min|first---------------------+-------------------2021-06-14 00:00:00 | 21.80233243974021
(省略)

20

18

160:00© 2021 SRA OSS, Inc. Japan

6:00

12:00

18:00

0:00
24

コミュニティ機能

time_bucket_gapfill関数• 欠損値を補って時間を任意の間隔で丸める=# DELETE FROM conditions
WHERE time >= '2021-06-14 12:00:00'
AND time < '2021-06-14 18:00:00';
DELETE 360=# SELECT time_bucket_gapfill('30min', time)
AS time_30min,avg(temperature)
FROM conditions
WHERE time BETWEEN '2021-06-14' AND '2021-06-15'
GROUP BY time_30min
ORDER BY time_30min;
time_30min|avg---------------------+-------------------2021-06-14 00:00:00 | 22.438319603197666:|:
2021-06-14 11:30:00 | 22.760572405493722
2021-06-14 12:00:00 |
NULL
:|
:
補完
2021-06-14 17:30:00 |2021-06-14 18:00:00 | 21.307451267267307
(省略)

26

値のない部分をNULLで補う

24

22
温度
20

18

160:00© 2021 SRA OSS, Inc. Japan

6:00

12:00

18:00

0:00
25

コミュニティ機能locf関数• 直前の値で欠損値を補う (last observation carried forward)=# SELECT time_bucket_gapfill('30min', time)
AS time_30min,locf(avg(temperature))
FROM conditions
WHERE time BETWEEN '2021-06-14' AND '2021-06-15'
GROUP BY time_30min
ORDER BY time_30min;
time_30min|avg---------------------+-------------------2021-06-14 00:00:00 | 22.438319603197666
直前
温:|
:度
2021-06-14 11:30:00 | 22.760572405493722
2021-06-14 12:00:00 | 22.760572405493722
2021-06-14 12:30:00 | 22.760572405493722
LOCF
2021-06-14 13:00:00 | 22.760572405493722
補完:|:
2021-06-14 17:30:00 | 22.760572405493722
2021-06-14 18:00:00 | 21.307451267267307
(省略)

26

値のない部分を直前の値で補う

24

22

直前の値
20

18

160:00© 2021 SRA OSS, Inc. Japan

6:00

12:00

18:00

0:00
26

コミュニティ機能

interpolate関数• 直前・直後の値の線形補完で欠損値を補う=# SELECT time_bucket_gapfill('30min', time)
AS time_30min,
interpolate(avg(temperature))
FROM conditions
WHERE time BETWEEN '2021-06-14' AND '2021-06-15'
GROUP BY time_30min
ORDER BY time_30min;
time_30min|avg---------------------+-------------------2021-06-14 00:00:00 | 22.438319603197666:|:
2021-06-14 11:30:00 | 22.760572405493722
直前
2021-06-14 12:00:00 | 22.648793856399383
2021-06-14 12:30:00 | 22.537015307305044
線形
2021-06-14 13:00:00 | 22.425236758210705
補完:|:
2021-06-14 17:30:00 | 21.419229816361646
2021-06-14 18:00:00 | 21.307451267267307
直後
(省略)

26

24

値のない部分を線形補完で補う

22直前の値

温度
20直後の値

18160:00© 2021 SRA OSS, Inc. Japan

6:00

12:00

18:00

0:00
27

そのほかのデータ分析関数
histogram関数

approximate_row_count関数• ヒストグラムを取得• テーブルのおおよその行数を取得=# SELECT time_bucket('30min', time) AS time_30min,
histogram(temperature, 18, 23, 5)
FROM conditions
WHERE time BETWEEN '2021-06-14'
AND '2021-06-15'
GROUP BY time_30min
ORDER BY time_30min;
time_30min|
histogram---------------------+------------------2021-06-14 00:00:00 | {0,0,0,0,2,28,0}2021-06-14 00:30:00 | {0,0,1,10,10,9,0}2021-06-14 01:00:00 | {0,0,26,4,0,0,0}
2021-06-14 01:30:00 | {0,2,28,0,0,0,0}
2021-06-14 02:00:00 | {0,0,10,20,0,0,0}
2021-06-14 02:30:00 | {0,0,10,20,0,0,0}(省略)=# ¥timing
タイミングは on です。
=# SELECT count(*) FROM conditions;count--------1455480(1 行)
時間: 246.751 ミリ秒=# SELECT approximate_row_count('conditions');
approximate_row_count----------------------1455840(1 行)
時間: 4.144 ミリ秒© 2021 SRA OSS, Inc. Japan28

そのほかの機能

コミュニティ機能• チャンクの並び替え・移動• 分散ハイパーテーブル• 圧縮• 継続的集計 (continuous aggregates)• データ保持 (data retention)• ユーザ定義アクション© 2021 SRA OSS, Inc. Japan

今後、SRA OSSTech Blogで紹介予定！
29まとめ• 時系列データベースは時間情報をもった一連のデータを扱うのに最適化されたデータベース• TimescaleDBはPostgreSQLベースの時系列データベース• DBasS提供をしなければ、すべての機能が自由に使える• リレーショナルデータベースとNoSQLの特長をあわせもつ• データは時間で分割されたチャンクに格納され、ハイパーテーブルを通じてアクセスされる© 2021 SRA OSS, Inc. Japan30

参考URLsraoss blog

• SRA OSS Tech Blog (日本語)• TimescaleDBの紹介• https://www.sraoss.co.jp/tech-blog/pgsql/timescaledb-intro/• TimescaleDBによるデータ分析 (近日公開)• Timescale Blog (英語)• TimescaleDB vs. PostgreSQL for time-series• https://blog.timescale.com/blog/timescaledb-vs-6a696248104e/• TimescaleDB vs. InfluxDB• https://blog.timescale.com/blog/timescaledb-vs-influxdb-for-time-series-datatimescale-influx-sql-nosql-36489299877/© 2021 SRA OSS, Inc. Japan

31

オープンソースとともに© 2021 SRA OSS, Inc. Japan

32