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% 性能が向上したというベンチマーク結果があります。
削除が高速
- 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() とかだとしっくりくるのに。。