TimescaleDBでよく使うSQL | 45395

瞬間風速的に押し寄せてくるリアルタイム処理が必要な時系列データの処理を実現するために色々と調べた結果、PostgreSQLで効率よく時系列データを扱えるようにしてくれる「TimescaleDB」という拡張モジュールの存在を知りました。今回はTimescaleDBでよく使うSQLのご紹介。

前提条件

Mac M1（AppleSilicon）
PostgreSQL 10.12
TimescaleDB 2.18.2

全体の流れ

ハイパーテーブル作成
連続集計ビュー作成
連続集計ビュー更新ポリシー
連続集計ビュー削除手順
ハイパーテーブルを圧縮する

1.ハイパーテーブル作成

基本的にPostgreSQLのテーブルと同じように扱えます。リアルタイム時系列データに対応するためハイパーテーブルを用意します。

一番最初にTimescaleDB拡張モジュールを有効化します（初回のみ）

1
CREATE EXTENSION IF NOT EXISTS timescaledb;

TIMESTAMPTZ型のカラムを一つ用意したテーブルを作成します。まずはPostgreSQLのCREATE文でPosgreSQL標準のテーブルを作成します

1
CREATE TABLE your_table (
2
  time        TIMESTAMPTZ       NOT NULL,
3
  location    TEXT              NOT NULL,
4
  temperature DOUBLE PRECISION  NULL,
5
  humidity    DOUBLE PRECISION  NULL
6
);

テーブルを時間で分割されたハイパーテーブルに変更します

1
SELECT create_hypertable('your_table', by_range('time'));

2.連続集計ビュー作成

時間単位の継続的な集計、大規模なデータセットでのリアルタイム分析が高速化される連続集計ビューを作成します。ハイパーテーブルで作成したtimeで集計したい時間単位を定義し、集計したいグループをGROUP BYで定義します。

1
CREATE MATERIALIZED VIEW your_table_summary_daily
2
WITH (timescaledb.continuous) AS
3
SELECT
4
  location,
5
  time_bucket(INTERVAL '1 day', time) AS bucket,
6
  AVG(temperature),
7
  MAX(temperature),
8
  MIN(temperature)
9
FROM
10
  your_table
11
GROUP BY
12
  location,
13
  bucket;

3.連続集計ビュー更新ポリシー

連続集計ビューを自動集計する期間やタイミングを定義します。 start_offsetは直近1ヶ月分のデータを更新 end_offsetは直近1日までのデータを対象にする schedule_intervalは1時間毎に更新する

1
SELECT
2
  add_continuous_aggregate_policy(
3
    'your_table_summary_daily',
4
    start_offset => INTERVAL '1 month',
5
    end_offset => INTERVAL '1 day',
6
    schedule_interval => INTERVAL '1 hour'
7
);

4.連続集計ビュー削除手順

連続集計ビューを削除する場合はまず更新ポリシーを削除する必要がある

更新ポリシーを確認する

1
SELECT * FROM timescaledb_information.jobs;

ポリシーを削除（ポリシーIDを指定する）

1
SELECT delete_job(1000);

マテリアルビュー削除

1
DROP MATERIALIZED VIEW your_table_summary_daily CASCADE;

ハイパーテーブルを圧縮する

高速化の時にハイパーテーブルを任意の日時以降のデータを圧縮する。メリットとして、数十〜数百倍のデータサイズの圧縮、読み取りクエリの高速化（MAXやAVG）、CPUやI/Oコストの削減、データ保存期間の延長がある。デメリットとして圧縮後は更新・削除が不可になる、バッチで挿入するのでリアルタイム挿入は不向き、JOINなど遅くなる場合がある。

1
-- 圧縮を有効にする
2
ALTER TABLE your_table SET (timescaledb.compress);
3

4
-- 圧縮時の「グルーピングキー」（必須）
5
ALTER TABLE your_table SET (timescaledb.compress_segmentby = 'your_grouping_key');
6

7
-- 圧縮ポリシーをスケジューリング（例: 7日経過したデータを圧縮）
8
SELECT add_compression_policy('your_table', INTERVAL '7 days');