PostgreSQLの時系列データ向け拡張「TimescaleDB」を触ってみた

# TimescaleDB とは

[TimescaleDB](https://www.timescale.com/) とは、PostgreSQL の拡張 (EXTENSION) として実装されている、時系列データの扱いやすくする OSS です。
日時によって変動する CPU 使用率や温度などの監視データや金額のようなデータについて、複雑な処理を高速に行なうことができます。
2017 年 5 月から beta が公開され始め、ライセンスは [Apache Licens V2.0](https://github.com/timescale/timescaledb/blob/master/LICENSE) です。

## メリット

> 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% 性能が向上したという [ベンチマーク結果](https://docs.timescale.com/v1.2/introduction/timescaledb-vs-postgres) があります。
* 削除が高速
  * `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 リポジトリの設定をします。

```console
$ 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」となります。

```console
# 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 が書き変わります。

```console
# 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 を再起動します。

```console
# 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 を作成します。

```console
$ createdb timescaledb
```

いま作成した DB で、TimesaceleDB 拡張を有効化（CREATE EXTENSION）します。

```console
$ 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. サンプルデータの作成

テーブルを作成します。場所別に気温と湿度を記録するテーブルです。

```console
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 列を元にパーティショニングさせることにします。

```console
timescaledb=# SELECT create_hypertable('conditions', 'time');
    create_hypertable
-------------------------
 (1,public,conditions,t)
(1 行)
```

これによって、パーティション用の BEFORE INSERT トリガが作成されます。1 行登録することになった度に TimescaleDB で定義された C 関数が実行され、子テーブルを判別しているようです。
データは未登録なので子テーブルはまだありません。

```console
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

```console
$ 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()` で取得することができます。

```console
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

```sql
/* 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

```sql
COPY conditions FROM '/home/anzai/Downloads/weather_big_conditions.csv' CSV;

=> 222913.379 ミリ秒(03:42.913)
```

TimescaleDB の方が遅い・・・

## 比較 2： 15分おきの平均

15分おきの平均気温、湿度を求めます。TimescaleDB を適用した同じテーブルで比較します。

* 素の PostgreSQL（15分おきに丸めるのが複雑）

```sql
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（専用関数がある。速い）

```sql
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 するにも、該当する子テーブルを調べる必要がある）

```sql
DELETE FROM conditions WHERE time <= '2017-01-01';
=> 59708.500 ミリ秒(00:59.708)
```

* TimescaleDB（指定が楽。速い）

```sql
SELECT drop_chunks('2017-01-01'::TIMESTAMPTZ, 'conditions');
=> 21.245 ミリ秒
```

# 結論

たしかに時系列データが扱いやすく、いろいろ高速化することが確認できました。また、ドキュメントがかなりしっかりしているので、触ってみるのは簡単でした。

ただ、個人的には、用語がまぎらわしいというかちょっととっつきづらいかな、と思いました。

* 別ソフトウェアかのような名前だけど実は EXTENSION。本当は簡単なのに難しそうに見えて気おくれしてしまう。
* Hypertable という名前から機能が想像できない。Chunk もあいまいな感じ。
* 既存のテーブルを Hypertable 化する create_hypertable() が、名前的には CREATE TABLE されそうに見えるので違和感。hypertablize() とかだとしっくりくるのに。。