時系列データベース設計：IoTとモニタリングデータの効率的な管理

時系列データが特殊な理由

センサーデータ、サーバーメトリクス、株価、ユーザーイベントログなど、「タイムスタンプ付きのデータが大量に蓄積される」という時系列データは、通常のリレーショナルデータとは異なる特性を持ちます。書き込みが非常に多く、過去データへの更新はほとんどなく、最新データと集計クエリへのアクセスが中心です。この特性に最適化された設計が必要です。

基本設計：時系列テーブルの基礎

-- シンプルな時系列テーブル（普通のPostgreSQL）
CREATE TABLE sensor_readings (
  sensor_id    UUID NOT NULL,
  recorded_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  temperature  DOUBLE PRECISION,
  humidity     DOUBLE PRECISION,
  pressure     DOUBLE PRECISION
);

-- タイムスタンプへのインデックスは必須
CREATE INDEX idx_sensor_recorded ON sensor_readings(recorded_at DESC);

-- センサーIDと時刻の複合インデックス（よくある検索パターン）
CREATE INDEX idx_sensor_id_recorded ON sensor_readings(sensor_id, recorded_at DESC);

-- 最新1時間のデータ取得
SELECT sensor_id, recorded_at, temperature
FROM sensor_readings
WHERE recorded_at >= NOW() - INTERVAL '1 hour'
ORDER BY recorded_at DESC;

パーティショニングで大規模データを管理

数十億行規模の時系列データでは、テーブルパーティショニングが不可欠です。古いデータをパーティションごと削除することで、DELETEによるオーバーヘッドなしに高速なデータクリーンアップが実現します。

-- 月次パーティショニングの設定
CREATE TABLE sensor_readings (
  sensor_id   UUID NOT NULL,
  recorded_at TIMESTAMP WITH TIME ZONE NOT NULL,
  value       JSONB NOT NULL
) PARTITION BY RANGE (recorded_at);

-- 月次パーティションの作成
CREATE TABLE sensor_readings_2026_04
  PARTITION OF sensor_readings
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE sensor_readings_2026_05
  PARTITION OF sensor_readings
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

-- 各パーティションにインデックスを作成
CREATE INDEX ON sensor_readings_2026_04 (recorded_at DESC);
CREATE INDEX ON sensor_readings_2026_05 (recorded_at DESC);

-- 古いパーティションの削除（一瞬で完了）
DROP TABLE sensor_readings_2024_01;  -- 2024年1月のデータを削除

-- 新しいパーティションの自動作成（pg_cron等で定期実行）
-- DO $$
-- DECLARE next_month TEXT;
-- BEGIN
--   next_month := TO_CHAR(NOW() + INTERVAL '1 month', 'YYYY_MM');
--   EXECUTE format('CREATE TABLE IF NOT EXISTS sensor_readings_%s PARTITION OF sensor_readings ...');
-- END $$;

TimescaleDBによる時系列最適化

TimescaleDBはPostGQLの拡張機能で、時系列データ専用の最適化を提供します。自動的なチャンキング（ハイパーテーブル）、連続集計、データ保持ポリシーがすぐに使えます。

-- TimescaleDBのインストール後
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 通常のテーブルをハイパーテーブルに変換
SELECT create_hypertable('sensor_readings', 'recorded_at',
  chunk_time_interval => INTERVAL '7 days');  -- 7日ごとにチャンク作成

-- 連続集計（Continuous Aggregate）
-- リアルタイムで集計を維持し、重いクエリを回避
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
  sensor_id,
  time_bucket('1 hour', recorded_at) AS hour,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp,
  COUNT(*) AS reading_count
FROM sensor_readings
GROUP BY sensor_id, time_bucket('1 hour', recorded_at);

-- 自動更新ポリシーを設定
SELECT add_continuous_aggregate_policy('sensor_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '30 minutes');

-- データ保持ポリシー（90日以上古いデータを自動削除）
SELECT add_retention_policy('sensor_readings', INTERVAL '90 days');

-- 時間バケット集計（time_bucket関数）
SELECT
  time_bucket('15 minutes', recorded_at) AS bucket,
  sensor_id,
  AVG(temperature) AS avg_temp
FROM sensor_readings
WHERE recorded_at >= NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

集計テーブルパターン：分→時→日の階層集計

リアルタイムモニタリングでは生データと集計データを階層的に管理します。

-- 生データ（1分以内に削除）
CREATE TABLE metrics_raw (
  metric_name VARCHAR(100) NOT NULL,
  measured_at TIMESTAMP WITH TIME ZONE NOT NULL,
  value       DOUBLE PRECISION NOT NULL,
  tags        JSONB
) PARTITION BY RANGE (measured_at);

-- 1時間集計（30日保持）
CREATE TABLE metrics_hourly (
  metric_name VARCHAR(100) NOT NULL,
  hour        TIMESTAMP WITH TIME ZONE NOT NULL,
  avg_value   DOUBLE PRECISION,
  max_value   DOUBLE PRECISION,
  min_value   DOUBLE PRECISION,
  sum_value   DOUBLE PRECISION,
  count       INT,
  PRIMARY KEY (metric_name, hour)
);

-- 日次集計（2年保持）
CREATE TABLE metrics_daily (
  metric_name  VARCHAR(100) NOT NULL,
  date         DATE NOT NULL,
  avg_value    DOUBLE PRECISION,
  max_value    DOUBLE PRECISION,
  min_value    DOUBLE PRECISION,
  total        DOUBLE PRECISION,
  PRIMARY KEY (metric_name, date)
);

-- 階層集計のクエリ最適化
-- 過去1時間 → metrics_raw
-- 過去7日間 → metrics_hourly
-- 過去1年間 → metrics_daily
-- それ以上  → metrics_daily（古いものは集計のみ保持）

ErdmatesでIoT/モニタリングのデータモデルを設計

IoTシステムやモニタリング基盤のデータモデルはテーブル間の関係が複雑です。センサーマスタ、デバイス設定、アラートルール、集計テーブルなどの関係をErdmatesで視覚化することで、設計の全体像を把握しやすくなります。時系列テーブルのパーティショニング設計もERDコメントとして記録しておくと、引き継ぎ時に役立ちます。

まとめ

時系列データの設計は「大量の書き込み」「時間軸での集計」「古いデータのアーカイブ」という3つの要件を同時に満たす必要があります。パーティショニングでデータ管理を効率化し、集計テーブルで読み取りパフォーマンスを最適化し、データ保持ポリシーでストレージコストを管理する。この3点セットを設計段階から組み込むことで、データ量が増加しても安定したシステムを維持できます。