📈

PostgreSQL で時系列データの扱いを考える

2023/07/15に公開

PostgreSQL

jsonb

tech

はじめに

まだ Heroku に無料枠があった頃、DIY でセンサーからのデータを収集してグラフ化していたのですが、単純に1計測を1レコードに保存してしまうと簡単に上限である1万レコードに到達してしまうんですよね。

1センサーで10分に1回の計測だとしても3カ月持たない感じです。

6回/時 x 24時間 x 90日 = 12,960 レコード

センサー数を増やしたり、計測間隔を短くすると、簡単に数百万、数千万レコードに到達してしまいますね。

そこで、JSONB型のカラムにデータを突っ込んだら、1万レコードの制限を回避できるのでないか？と考え、JSONB型について調べたメモになります。まぁ、Heroku の無料枠はなくなってしまいましたが、この先、何かの役に立てばということで。

時系列データの特徴

センサーからのデータを収集して可視化や分析を行うような、いわゆる IoT システムでは、システムの規模や複雑度に対して、比較的データ量は多くなりがちです。

ケースバイケースなので一概には言えないですが、経験上、製造業にしろ、ヘルスケアにしろ、農業にしろ、IoT のデータは以下のような特徴を持っている印象があります。

分単位、秒単位の計測を行うので比較的データ量が多い
追記がメインで更新や削除（古いデータの削除を除く）はあまり行わない
参照は日単位とか月単位とか区切りのよい期間指定が多い
古いデータは一定期間で順次削除される

こういった時系列データは、InfluxDB や MongoDB の様な NoSQL が使われることも多いかと思います。一方で、ユーザー管理やデバイス管理などの、一般的な Web システムの部分では、やはり RDBMS が欲しくなります。それなりの規模であれば、それなりのコストをかけて構築・運用をするのですが、多くの場合、Too much になりがちです。

なので、こじんまりとしたシステムを、低コストで、こじんまりと運用したいというのが出発点です。

JSONB 型のカラムにデータを出し入れする

ここでは、JSONB 型の説明は割愛しますが、カラムに JSON を格納し、SQL から操作することができます。うまく使えばスキーマレスのデータを扱うことができます。

参考: PostgreSQL 15.0文書 - 第8章データ型 - 8.14. JSONデータ型

センサーデータですが、単純にタイムスタンプと値を持つものとして、以下のような形式で JSONB 型のカラムに格納することにしました。タイムスタンプをキーにして、同じキーで追記された場合には上書されるものとします。キーと値の両方にタイムスタンプがあるのが冗長ですが、タイムスタンプをキーにしたかったのでこのようにしました。

{
  "1689345888": {"timestamp": 1689345888, "value": 18.5, "tag": "baz"},
  "1689345890": {"timestamp": 1689345890, "value": 20.3, "tag": "baz"},
  ... (snip) ...
}

センサーデータを格納するテーブルは、とりあえず、以下ような形で定義します

id(Integer)	sensor_id(Integer)	stream(JSONB)
1	32	{}
2	33	{}

テーブルを作成します。

CREATE TABLE sensor_data(
    id SERIAL,
    sensor_id INTEGER NOT NULL,
    stream JSONB,
    PRIMARY KEY(id)
);

テーブルにセンサーを登録します。

INSERT INTO sensor_data (sensor_id, stream) VALUES (32, '{}'::JSONB);
INSERT INTO sensor_data (sensor_id, stream) VALUES (33, '{}'::JSONB);

センサーデータを追記していきます。jsonb_insert() は JSON 処理関数の1つで、第1引数で指定された JSONB の、第2引数である path で指定された位置に、第3引数で指定された新しい値を追加し、追加後の JSONB を返します。この場合、sensor_id=32 となるレコードの stream に '{"timestamp": 1689345888, "value": 18.5, "tag": "bar" }' を追加します。

UPDATE sensor_data
SET stream = jsonb_insert(stream, '{"1689345888"}', '{"timestamp": 1689345888, "value": 18.5, "tag": "bar" }')
WHERE sensor_id=32;

データが挿入されたことを確認してみます。

# SELECT * FROM sensor_data;
 id | sensor_id |                                 stream
----+-----------+------------------------------------------------------------------------
  4 |        33 | {}
  3 |        32 | {"1689345888": {"tag": "bar", "value": 18.5, "timestamp": 1689345888}}
(2 rows)

さらに新しいデータを追加してみます。

UPDATE sensor_data
SET stream = jsonb_insert(stream, '{"1689345890"}', '{"timestamp": 1689345890, "value": 20.3, "tag": "baz" }')
WHERE sensor_id=32;

データが追加されたことが確認できました。

 SELECT * FROM sensor_data;
 
 id | sensor_id |                                                                    stream
----+-----------+----------------------------------------------------------------------------------------------------------------------------------------------
  4 |        33 | {}
  3 |        32 | {"1689345888": {"tag": "bar", "value": 18.5, "timestamp": 1689345888}, "1689345890": {"tag": "baz", "value": 20.3, "timestamp": 1689345890}}

同じキーで上書しようとするとエラーになります。

UPDATE sensor_data
SET stream = jsonb_insert(stream, '{"1689345890"}', '{"timestamp": 1689345890, "value": 14.8, "tag": "baz" }')
WHERE sensor_id=32;

ERROR:  cannot replace existing key
HINT:  Try using the function jsonb_set to replace key value.

上書を行う場合は jsonb_set() を用います。 1689345890 -> value の値が更新されたことが確認できます。

UPDATE sensor_data
SET stream = jsonb_set(stream, '{"1689345890"}', '{"timestamp": 1689345890, "value": 14.8, "tag": "baz" }')
WHERE sensor_id=32;

SELECT * FROM sensor_data;
 id | sensor_id |                                                                    stream
----+-----------+----------------------------------------------------------------------------------------------------------------------------------------------
  4 |        33 | {}
  3 |        32 | {"1689345888": {"tag": "bar", "value": 18.5, "timestamp": 1689345888}, "1689345890": {"tag": "baz", "value": 14.8, "timestamp": 1689345890}}
(2 rows)

今度は期間を指定してセンサーデータを検索します。 jsonb_path_query() を用いて条件に合致するデータを検索します。

SELECT jsonb_path_query(stream::jsonb, '$.* ? (@.timestamp > $start && @.timestamp < $end)', '{"start":1689345883, "end":1689345889}') AS data
FROM sensor_data
WHERE sensor_id = 32;

                          data
--------------------------------------------------------
 {"tag": "bar", "value": 18.5, "timestamp": 1689345888}
(1 row)

データを削除する場合には - オペレータを使用します。

UPDATE sensor_data
SET stream = stream - '1689345890'
WHERE sensor_id=32;

さて、1つのレコードに何万件、何十万件のデータを投入した場合、データ自体は PostgreSQL としての上限まで書き込めるのでしょうが、パフォーマンスが気になります。

日次とか月次でパーティショニングした方が、古いデータの削除も楽なのでメリットがありそうです。

データを作成します。

CREATE TABLE sensor_data2(
    id SERIAL,
    sensor_id INTEGER NOT NULL,
    partition DATE NOT NULL,
    stream JSONB NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO sensor_data2 (sensor_id, partition, stream) VALUES (32, '2023-07-15', '{}'::JSONB);
INSERT INTO sensor_data2 (sensor_id, partition, stream) VALUES (32, '2023-07-16', '{}'::JSONB);

UPDATE sensor_data2
SET stream = jsonb_set(stream, '{"1689419787"}', '{"timestamp": 1689419787, "value": 14.8, "tag": "baz" }')
WHERE sensor_id = 32 and partition = '2023-07-15';

UPDATE sensor_data2
SET stream = jsonb_set(stream, '{"1689420207"}', '{"timestamp": 1689420207, "value": 15.3, "tag": "baz" }')
WHERE sensor_id = 32 and partition = '2023-07-15';

UPDATE sensor_data2
SET stream = jsonb_set(stream, '{"1689423272"}', '{"timestamp": 1689423272, "value": 18.9, "tag": "baz" }')
WHERE sensor_id = 32 and partition = '2023-07-15';

UPDATE sensor_data2
SET stream = jsonb_set(stream, '{"1689499934"}', '{"timestamp": 1689444914, "value": 16.3, "tag": "baz" }')
WHERE sensor_id = 32 and partition = '2023-07-16';

UPDATE sensor_data2
SET stream = jsonb_set(stream, '{"1689513314"}', '{"timestamp": 1689513314, "value": 17.4, "tag": "baz" }')
WHERE sensor_id = 32 and partition = '2023-07-16';

全期間から value が 17 以上のデータを検索

SELECT jsonb_path_query(stream::jsonb, '$.* ? (@.value > 17)') AS data
FROM sensor_data2
WHERE sensor_id = 32;

                          data
--------------------------------------------------------
 {"tag": "baz", "value": 18.9, "timestamp": 1689423272}
 {"tag": "baz", "value": 17.4, "timestamp": 1689513314}
(2 rows)

2023年07月16日 03:15:14 ～ 2023年7月16日 22:16:12 のデータを検索

SELECT jsonb_path_query(stream::jsonb, '$.* ? (@.timestamp > $start && @.timestamp < $end)', '{"start":1689444914, "end":1689513372}') AS data
FROM sensor_data2
WHERE sensor_id = 32 and partition >= '2023-07-16' and partition <= '2023-07-16';

                          data
--------------------------------------------------------
 {"tag": "baz", "value": 17.4, "timestamp": 1689513314}
(1 row)

まとめ

PostgreSQL の JSONB 型に時系列データを投入し、追加・更新・検索・削除が行えることが確認できました。

はじめに

時系列データの特徴

JSONB 型のカラムにデータを出し入れする

まとめ

Discussion