実際の課題：IoTテレメトリのボトルネック
6ヶ月前、私たちのエンジニアリングチームは、IoTモニタリングプラットフォームの深刻なパフォーマンス低下という壁に直面しました。4つの工場フロアに配置された約5,000台の産業用ゲートウェイから、温度、湿度、電圧、振動などのテレメトリデータを追跡していました。各ユニットが5秒ごとにメトリクスを送信するため、インジェスト層からは毎秒1,000件の書き込みが定常的にPostgreSQLの標準テーブルへと直接送られていました。
初期の段階では、データベースは何の問題もなく処理できていました。8コア、32 GB RAMのインスタンスは、最初の1,000万行を容易に処理しました。しかし運用開始から2ヶ月目には、テレメトリテーブルが1億5,000万行を超えて膨れ上がりました。その頃から、スタック全体でレイテンシの悪化が発生し始めました。
- 書き込みレイテンシの急増： インジェストの応答時間が8msから450ms超へと跳ね上がりました。工場の朝のシフト開始ピーク時には、バッチインサートが頻繁にタイムアウトするようになりました。
- ダッシュボードのタイムアウト： 5分ごとの全端末平均を計算するGrafanaダッシュボードの応答速度が、1秒未満から25秒以上へと大幅に悪化し、HTTP 504 Gateway Timeoutが頻発しました。
- 深刻なI/Oスラッシング： 定期的な
VACUUMおよびautovacuumサイクルにより、NVMeディスクのスループットが450 MB/sで飽和し、実行中のトランザクションのI/Oが枯渇しました。
原因分析：標準のPostgreSQLが時系列データで失速する理由
パフォーマンス低下の原因を特定するため、PostgreSQLの実行計画、バッファヒット率、カーネルI/O統計を詳細に調査しました。その結果、アーキテクチャ上の3つのボトルネックがすぐに判明しました。
1. B-TreeインデックスがRAMから退避
標準テーブルが数億行規模に増大するにつれて、B-Treeインデックスも同時に肥大化します。(device_id, recorded_at)の複合インデックスは32 GBを超え、8 GBのshared_buffersプールを大きく上回りました。新規の書き込み時にインデックスのリーフページをメモリ上で更新できなくなり、インサートごとにディスクからのランダムリードが発生したことで、シーケンシャルな書き込みがI/Oボトルネックへと変わってしまいました。
2. 分析用集計処理に伴う高コストなスキャン
1時間ごとや日ごとのロールアップを算出するダッシュボードのクエリは、更新のたびに数百万行の生データをスキャンしていました。PostgreSQLは単純な算術平均を算出するためだけに、数千ものヒープページを取得する必要がありました。これにより、何週間も変更されていない過去データに対して無駄なCPUとメモリ帯域が消費されていました。
3. デッドタプルの蓄積とテーブルの肥大化
90日以上前のデータを削除するには、スケジュール設定されたDELETEバッチを実行する必要がありました。PostgreSQLは削除された行を即座に領域回収するのではなくデッドタプルとしてマークするため、テーブルが急速に肥大化しました。その結果発生するautovacuumの実行がテーブルページをロックし、トラフィックのピーク時にキャッシュ効率を低下させました。
選択肢の比較検討
時系列ワークロードを処理するために、3つの異なるアーキテクチャパスを検討しました。
| アプローチ | メリット | デメリット | 評価 |
|---|---|---|---|
| PostgreSQL標準のパーティショニング | PostgreSQL本体に組み込まれており、サードパーティ製拡張機能が不要。 | チャンク作成用のカスタムスクリプトが必要で、自動ロールアップがなく、ネイティブのカラムナー圧縮も提供されていない。 | 長期的な保守・運用のオーバーヘッドが大きすぎる。 |
| 専用の時系列DB（例：InfluxDB） | メトリクス専用に設計されており、標準で優れた圧縮性能を備える。 | 運用対象のデータベースが増える。また、デバイスのメタデータや顧客アカウントとのリレーショナルな結合（JOIN）が困難になる。 | 移行コストが高く、二重書き込みの複雑さが発生する。 |
| TimescaleDB拡張機能 | 自動パーティショニング（Hypertable）、継続的集計、カラムナー圧縮を備えたネイティブ拡張機能。 | 外部のPostgreSQL拡張機能のインストールとチューニングが必要。 | 採用： スキーマの全面書き換えが不要で、完全なSQL互換性を維持。 |
TimescaleDBを用いたステップ別の実装手順
TimescaleDBは、標準テーブルをHypertableに変換します。内部的には受信データをチャンクと呼ばれる時間ベースのパーティションに分割しつつ、アプリケーション層に対しては単一の統合テーブルとして公開します。
ステップ1：拡張機能のインストールとメモリのチューニング
PostgreSQL 15を実行しているUbuntu 22.04データベースサーバーで、公式のTimescaleリポジトリを追加してパッケージをインストールしました。
# TimescaleDBリポジトリを追加してパッケージをインストール
curl -s https://packagecloud.io/install/repositories/timescale/timescaledb/script.deb.sh | sudo bash
sudo apt-get install timescaledb-2-postgresql-15
# メモリおよびワーカーの自動チューナーを実行
sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresql続いて、データベース内で拡張機能を有効化しました。
CREATE EXTENSION IF NOT EXISTS timescaledb;ステップ2：スキーマの設計とHypertableの作成
標準的なSQLを使用してテレメトリテーブルを定義し、タイムスタンプ列に明示的なNOT NULL制約を設定しました。
CREATE TABLE sensor_readings (
recorded_at TIMESTAMPTZ NOT NULL,
device_id INTEGER NOT NULL,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION,
voltage DOUBLE PRECISION
);
-- 1日ごとのチャンクインターバルを持つHypertableに変換
SELECT create_hypertable(
'sensor_readings',
'recorded_at',
chunk_time_interval => INTERVAL '1 day'
);適切なチャンクインターバルの選択は非常に重要です。アクティブなチャンクとそのインデックスが、利用可能なshared_buffersの25%以内に収まるように設計するのが目安です。今回の1日あたり8,640万行という書き込み量では、1日ごとのインターバルによって約1.8 GBのチャンクが生成され、RAM内に余裕を持って収まりました。
ステップ3：継続的集計（Continuous Aggregates）によるダッシュボードの高速化
継続的集計（Continuous Aggregates）は、要約統計量をバックグラウンドで段階的に事前計算します。クエリが届くと、TimescaleDBは事前集計された過去のバケットと最新の生データポイントのみをマージして結果を返します。
-- 1時間ごとの集計ビューを作成
CREATE MATERIALIZED VIEW device_hourly_stats
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', recorded_at) AS bucket,
device_id,
AVG(temperature) AS avg_temp,
MAX(temperature) AS max_temp,
MIN(temperature) AS min_temp,
AVG(humidity) AS avg_humidity,
COUNT(*) AS reading_count
FROM sensor_readings
GROUP BY bucket, device_id
WITH NO DATA;
-- 集計データを30分ごとに自動更新
SELECT add_continuous_aggregate_policy('device_hourly_stats',
start_offset => INTERVAL '3 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '30 minutes'
);ステップ4：カラムナー圧縮と自動データ保持（Retention）の有効化
一度書き込まれたテレメトリメトリクスが変更されることはほとんどありません。TimescaleDBのカラムナー圧縮は過去の行データをカラム配列に並べ替え、大幅なストレージ削減を実現します。
-- デバイスごとのカラムナー圧縮を設定
ALTER TABLE sensor_readings SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'device_id',
timescaledb.compress_orderby = 'recorded_at DESC'
);
-- 7日以上経過したチャンクを自動圧縮
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');
-- 180日以上経過した生のパーティションチャンクを破棄
SELECT add_retention_policy('sensor_readings', INTERVAL '180 days');本番運用6ヶ月の実績：数値で見る成果
12億件の生テレメトリレコードを処理した6ヶ月間で、主要メトリクスは全面的に改善・安定化しました。
- インサートレイテンシ： P95の書き込みレイテンシが450msから6msへと激減し、クライアント側のタイムアウトが完全に解消されました。
- ダッシュボードの表示速度：
device_hourly_statsから読み取ることで、Grafanaでの全端末30日間クエリの応答時間が25秒から42ミリ秒へと大幅に短縮されました。 - ディスク使用量： カラムナー圧縮により、生のテレメトリストレージが480 GBから62 GBへと削減され、87%の省スペース化を達成しました。
- VACUUMのオーバーヘッド解消： データ保持ポリシーにより、基盤となるチャンクファイルがファイルシステム操作で直接破棄されるため、行ごとの削除処理を回避し、autovacuumによるテーブル肥大化が完全に解消されました。
高頻度の書き込みや増大するメトリクステーブルによってPostgreSQLの負荷が高まっている場合、TimescaleDBは非常に有力な選択肢となります。既存のリレーショナルデータモデル、バックアップパイプライン、ORMツールを捨てることなく、時系列データベース並みのパフォーマンスを手に入れることができます。
