Posted inDatabase

TimescaleDB：IoTデータの肥大化に悩むPostgreSQLのための「救世主」的ソリューション

Database tutorial - IT technology blog
Database tutorial - IT technology blog

背景：時系列データの前にPostgreSQLが「限界」を迎えるとき

モニタリングシステムやIoTデバイス管理システムを構築していると、すぐに困難な課題に直面します。それは、洪水のように押し寄せるデータです。システムは毎秒、数千件もの温度、湿度、あるいはCPU使用率のレコードを受信することがあります。当初、PostgreSQLは非常にうまく処理しますが、データテーブルが肥大化するにつれて、その負荷が顕著に現れ始めます。

私が以前携わった太陽光発電管理プロジェクトでは、純粋なPostgreSQL上のストレージシステムは初期段階では非常に安定して動作していました。しかし、データベースが5,000万行に達したあたりから、パフォーマンスが急激に低下し始めました。グラフを描画するためのSELECTクエリに15〜20秒もかかるようになったのです。インデックスが肥大化してRAMを使い果たし、サーバーは常にI/O待ち（I/O Wait）の状態に陥りました。

InfluxDBに移行して新しいクエリ言語を学び直す代わりに、私はTimescaleDBを選択しました。これはPostgreSQLの拡張機能（extension）であり、慣れ親しんだSQL構文と強力なJOIN機能をそのまま維持できます。TimescaleDBは、巨大なテーブルを「Hypertables」に変換し、時間に基づいてデータを自動的にチャンク（chunks）に分割することで、スケーラビリティの問題を解決します。

DockerによるTimescaleDBのデプロイ

システムの競合を避け、最も素早く試すには、Dockerが最適な選択肢です。TimescaleDBの公式イメージには、PostgreSQLと必要なツールがあらかじめ統合されています。

# PostgreSQL 15でコンテナを実行
docker run -d --name timescale-db \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=your_secure_password \
  timescale/timescaledb:latest-pg15

コンテナが起動したら、psqlまたはDBeaver経由で接続します。専用機能の使用を開始するには、拡張機能の有効化が必須です。

-- データベース内で拡張機能を有効化
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Hypertableの設定：パフォーマンスの鍵

HypertableはTimescaleDBの核心です。通常通りテーブルを定義した後、関数を使用して自動パーティショニングメカニズムを有効にします。

1. 生データテーブルの初期化

サーバー室のセンサーからのデータを保存する必要があると仮定します：

CREATE TABLE sensor_data (
    time        TIMESTAMPTZ       NOT NULL,
    sensor_id   INTEGER           NOT NULL,
    temperature DOUBLE PRECISION  NULL,
    cpu_usage   DOUBLE PRECISION  NULL
);

2. Hypertableへの変換

以下のコマンドは、TimescaleDBに対してtime列に基づいてテーブルを分割するよう指示します。ここでは、各チャンクが7日間のデータを管理するように設定します。

SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => INTERVAL '7 days');

実務上の注意： 1つのチャンクのインデックスがRAMに収まるようにchunk_time_intervalを選択してください。RAMが16GBの場合、アクセスの最大速度を実現するために、各チャンクには約2〜3GBのデータが含まれるようにするのが理想的です。

3. データ圧縮ポリシー（Compression）

IoTデータは通常、高い反復性を持ちます。TimescaleDBは列指向圧縮（columnar compression）を使用しており、ストレージ容量を90GBからわずか10GB程度まで削減できます。

-- デバイスごとのクエリを最適化するためにsensor_idで圧縮を設定
ALTER TABLE sensor_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'sensor_id'
);

-- 30日以上前のデータを自動圧縮
SELECT add_compression_policy('sensor_data', INTERVAL '30 days');

4. クリーンアップポリシー（Retention）

ディスクの溢れを防ぐために、例えば2年以上前の古いデータを自動的に削除するように設定する必要があります：

SELECT add_retention_policy('sensor_data', INTERVAL '2 years');

Continuous Aggregatesによるダッシュボードの最適化

ユーザーがダッシュボードを読み込むたびに、数十億行のデータに対して平均値（AVG）を計算するのは、パフォーマンス上の災厄です。TimescaleDBはこれをContinuous Aggregatesで解決します。これはマテリアライズド・ビュー（Materialized View）に似ていますが、よりスマートに自動更新されます。

CREATE MATERIALIZED VIEW sensor_stats_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       avg(temperature) AS avg_temp
FROM sensor_data
GROUP BY bucket, sensor_id;

実際の結果は非常に印象的でした。私のプロジェクトでは、温度グラフの読み込み時間が8秒から200ms未満に短縮されました。ユーザーはインターフェースを操作する際、明らかにスムーズになったと感じることができます。

まとめ

大規模なデータ課題に対してPostgreSQLの安定性を活用したい場合、TimescaleDBは最良の選択肢です。技術スタックを変更する必要も、新しい言語を学ぶ必要もありませんが、専用のNoSQLデータベースのようなパフォーマンスを手に入れることができます。これは、SQLの柔軟性と現代的なスケーラビリティの完璧な組み合わせです。

Share:
Tags: