LoginSignup
73
59

More than 5 years have passed since last update.

@anzai323(Nozomi Anzai)

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

Last updated at Posted at 2019-03-03

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() とかだとしっくりくるのに。。
73
59
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
73
59