TimescaleDB とは
TimescaleDB とは、PostgreSQL の拡張 (EXTENSION) として実装されている、時系列データの扱いやすくする OSS です。
日時によって変動する CPU 使用率や温度などの監視データや金額のようなデータについて、複雑な処理を高速に行なうことができます。
2017 年 5 月から beta が公開され始め、ライセンスは Apache Licens V2.0 です。
メリット
Architecture & Concepts: https://docs.timescale.com/v1.2/introduction/architecture
Why Use TimescaleDB over Relational DBs?: https://docs.timescale.com/v1.2/introduction/timescaledb-vs-postgres
- パーティショニングの恩恵
- 内部的にはパーティショニングが使われています。TimescaleDB の機能を適用したテーブルを「HyperTable」と呼び、これが親テーブルとなって、専用スキーマに「Chunk」と呼ぶ日時間隔別の子テーブルが作成されます。
- 一般的なパーティショニングの恩恵を受けることができます。例えば、ある月のデータを削除したい場合に DELETE -> autovacuum という重たい処理ではなく、月別子テーブルの DROP TABLE で済む、などです。
- INSERT が高速
- INSERT のたびに INDEX の更新が必要になりますが、メモリに乗り切らないサイズのテーブルだとそれなりに重たい処理です。TimescaleDB ではこれを改善し、大きなサイズのテーブルにおいては素の PostgreSQL と比べて劇的に高速なようです。
- 集計の高機能化
- 収集値をグラフにする際などに、実際には 1 分おきに収集していたとしても 1 時間の平均値で描画する、というような抽出をすることはよくあります。この GROUP BY のために日時を丸めるのに、素の PostgreSQL では
date_trunc()
を使うなどしますが、それを強力にしたtime_bucket()
という関数が用意されています。 -
last()
、first()
といった分析用の関数も追加されています。 - 処理も最適化されています。10 億行のテーブルにおいて、素の PostgreSQL と比べデータロード所要時間が 1/15 で済み、スループットで 200% 性能が向上したという ベンチマーク結果 があります。
- 収集値をグラフにする際などに、実際には 1 分おきに収集していたとしても 1 時間の平均値で描画する、というような抽出をすることはよくあります。この GROUP BY のために日時を丸めるのに、素の PostgreSQL では
- 削除が高速
-
drop_chunks()
関数によって「7 日前より古いデータを削除」といったデータの削除をすることができます。内部的には該当する子テーブルの DROP TABLE ですが、子テーブルの名前を知らなくても簡単に済みます。
-
PostgreSQL への導入
Docker でも用意されていますが、ここでは、既存の PostgreSQL への EXTENSION 追加、として導入します。
なお、以下の環境で行なっています。
ソフトウェア | バージョン |
---|---|
CentOS | 7.2 |
PostgreSQL | 10.5 (yum でインストール済) |
TimescaleDB | 1.2.1 |
1. TimescaleDB を yum でインストール
Installation: https://docs.timescale.com/v1.2/getting-started/installation/rhel-centos/installation-yum
yum リポジトリの設定をします。
$ sudo su -
# cat > /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$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
EOL
インストールします。
※ PostgreSQL 11 がインストールされている場合は「timescaledb-postgresql-11」となります。
# sudo yum install -y timescaledb-postgresql-10
# rpm -qa | grep timescaledb
timescaledb-tools-0.4.1-0.el7.x86_64
timescaledb-postgresql-10-1.2.1-0.el7.x86_64
2. PostgreSQL の設定ファイルを編集
Configuring TimescaleDB: https://docs.timescale.com/v1.2/getting-started/configuring
timescaledb-tune
コマンドで postgresql.conf ファイルを自動編集します。
これによって、shared_preload_libraries
で TimescaleDB をロードするほか、マシンスペックを考慮したパラメータチューニングが行なわれるようです。
なお、PostgreSQL のコマンドへパスが通っていない、別のバージョンを参照してしまう場合には、-pg-config
オプションで明示的に pg_config コマンドのパスを指定します。その他、データベースクラスタのディレクトリを指定するオプションなどもあり、詳細は timescaledb-tune -h
で確認することができます。
何度か質問されますが、「y」をタイプしていくと最後に「Saving changes to ...」というメッセージが出力され、postgresql.conf が書き変わります。
# timescaledb-tune -pg-config /usr/pgsql-10/bin/pg_config
Using postgresql.conf at this path:
/var/lib/pgsql/10/data/postgresql.conf
Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup201903021512
:
(省略)
:
Saving changes to: /var/lib/pgsql/10/data/postgresql.conf
設定ファイルの変更を反映するため、PostgreSQL を再起動します。
# systemctl restart postgresql-10
3. DB、テーブルを作成
Setup: https://docs.timescale.com/v1.2/getting-started/setup
Creating Hypertables: https://docs.timescale.com/v1.2/getting-started/creating-hypertables
DB を作成します。
$ createdb timescaledb
いま作成した DB で、TimesaceleDB 拡張を有効化(CREATE EXTENSION)します。
$ psql timescaledb
timescaledb=# CREATE EXTENSION timescaledb;
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.2.1
For more information on TimescaleDB, please visit the following links:
1. Getting started: https://docs.timescale.com/getting-started
2. API reference documentation: https://docs.timescale.com/api
3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
CREATE EXTENSION
4. サンプルデータの作成
テーブルを作成します。場所別に気温と湿度を記録するテーブルです。
timescaledb=# CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL,
humidity DOUBLE PRECISION NULL
);
CREATE TABLE
create_hypertable()
関数を実行して conditions テーブルに TimescaleDB を適用させ、time 列を元にパーティショニングさせることにします。
timescaledb=# SELECT create_hypertable('conditions', 'time');
create_hypertable
-------------------------
(1,public,conditions,t)
(1 行)
これによって、パーティション用の BEFORE INSERT トリガが作成されます。1 行登録することになった度に TimescaleDB で定義された C 関数が実行され、子テーブルを判別しているようです。
データは未登録なので子テーブルはまだありません。
timescaledb=# \d+ conditions
テーブル "public.conditions"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト | ストレージ | 統計の対象 | 説明
-------------+--------------------------+----------+---------------+------------+------------+------------+------
time | timestamp with time zone | | not null | | plain | |
location | text | | not null | | extended | |
temperature | double precision | | | | plain | |
humidity | double precision | | | | plain | |
インデックス:
"conditions_time_idx" btree ("time" DESC)
トリガー:
ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
以下にある「weather_xxx.tar.gz」のサンプルデータをダウンロード、解凍し、テーブルに投入します。
Sample Datasets: https://docs.timescale.com/v1.2/tutorials/other-sample-datasets
$ wget https://timescaledata.blob.core.windows.net/datasets/weather_big.tar.gz
$ tar xfz weather_big.tar.gz
$ ls weather*
weather.sql weather_big.tar.gz weather_big_conditions.csv weather_big_locations.csv
$ psql timescaledb
timescaledb=# COPY conditions FROM '/home/anzai/Downloads/weather_big_conditions.csv' CSV;
COPY 40000000
トリガによって子テーブルが作成されます。子テーブル一覧は show_chunks()
で取得することができます。
timescaledb=# SELECT show_chunks('conditions');
show_chunks
----------------------------------------
_timescaledb_internal._hyper_1_1_chunk
_timescaledb_internal._hyper_1_2_chunk
_timescaledb_internal._hyper_1_3_chunk
_timescaledb_internal._hyper_1_4_chunk
_timescaledb_internal._hyper_1_5_chunk
(5 行)
クエリの比較
素の PostgreSQL と TimescaleDB で実行の仕方、速度を比較してみました。(実行速度は \timing
で計測)
比較 1: データ投入
サンプルデータの 4000 万行の CSV を COPY
でロードします。
- 素の PostgreSQL
/* TimescaleDB を使わないそっくりなテーブルを作成 */
CREATE TABLE conditions_raw (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL,
humidity DOUBLE PRECISION NULL
);
CREATE INDEX conditions_raw_time_idx ON conditions_raw (time DESC);
COPY conditions_raw FROM '/home/anzai/Downloads/weather_big_conditions.csv' CSV;
=> 174209.647 ミリ秒(02:54.210)
- TimescaleDB
COPY conditions FROM '/home/anzai/Downloads/weather_big_conditions.csv' CSV;
=> 222913.379 ミリ秒(03:42.913)
TimescaleDB の方が遅い・・・
比較 2: 15分おきの平均
15分おきの平均気温、湿度を求めます。TimescaleDB を適用した同じテーブルで比較します。
- 素の PostgreSQL(15分おきに丸めるのが複雑)
SELECT time
- (extract(minute from time)::INT % 15) * '1 minute'::INTERVAL
- extract(second from time) * '1 second'::INTERVAL
AS fifteen_min,
avg(temperature), avg(humidity)
FROM conditions
GROUP BY 1 ORDER BY 1;
=> 31168.772 ミリ秒(00:31.169)
- TimescaleDB(専用関数がある。速い)
SELECT time_bucket('15 minutes', time) AS fifteen_min,
avg(temperature), avg(humidity)
FROM conditions
GROUP BY 1 ORDER BY 1;
=> 13754.011 ミリ秒(00:13.754)
比較 3: DELETE の比較
ある日付以前のデータを削除します。サンプルデータでは全行が削除対象となります。
TimescaleDB を適用した同じテーブルで比較します。
- 素の PostgreSQL(遅い。DROP TABLE するにも、該当する子テーブルを調べる必要がある)
DELETE FROM conditions WHERE time <= '2017-01-01';
=> 59708.500 ミリ秒(00:59.708)
- TimescaleDB(指定が楽。速い)
SELECT drop_chunks('2017-01-01'::TIMESTAMPTZ, 'conditions');
=> 21.245 ミリ秒
結論
たしかに時系列データが扱いやすく、いろいろ高速化することが確認できました。また、ドキュメントがかなりしっかりしているので、触ってみるのは簡単でした。
ただ、個人的には、用語がまぎらわしいというかちょっととっつきづらいかな、と思いました。
- 別ソフトウェアかのような名前だけど実は EXTENSION。本当は簡単なのに難しそうに見えて気おくれしてしまう。
- Hypertable という名前から機能が想像できない。Chunk もあいまいな感じ。
- 既存のテーブルを Hypertable 化する create_hypertable() が、名前的には CREATE TABLE されそうに見えるので違和感。hypertablize() とかだとしっくりくるのに。。