データ集計など時間がかかわるデータの検索に時系列DBは必須。
使い方や関数は早めにキャッチアップが必要だと実感。
時系列DBとは
時間情報を持ったデータを格納し、最適化されたデータベース。
TimescaleDBはその中の1つであり、今回TimescaleDBをお試ししてみる。
詳細は参考の資料を参照。
https://www.sraoss.co.jp/wp-content/uploads/files/event_seminar/material/2021/timescaledb-intro-20210624.pdf
コンテナの起動
本体をインストールしたくないので、コンテナで起動する。
パスワードを設定したい場合、POSTGRES_PASSWORD
環境変数に設定する。ここはPostgreSQLと一緒。
docker pull timescale/timescaledb-ha:pg16
docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=example timescale/timescaledb-ha:pg16
docker exec -it timescaledb psql -U postgres
docs.timescale.com
ハイパーテーブルの作成
TimescaleDBの紹介資料を参照して、ハイパーテーブルを作成する。
通常のテーブルを作成して、create_hypertable関数を実行。
サーバのCPU使用率、メモリ使用率の情報をもつserverspecテーブルを作成。
CREATE TABLE serverspec (
time timestamp NOT NULL,
servername text NOT NULL,
cpu_userd double precision,
memory_used double precision
);
create_hypertable関数には、ハイパーテーブルに変換するテーブル名、分割の条件にする時間の列名を指定する。
戻り値は、ハイパーテーブルのID、スキーマ名、テーブル名。
SELECT create_hypertable('serverspec', 'time');
WARNING: column type "timestamp without time zone" used for "time" does not follow best practices
HINT: Use datatype TIMESTAMPTZ instead.
create_hypertable
(1,public,serverspec,t)
データの操作
実際にデータの挿入、検索、更新、削除をする。
INSERT INTO serverspec VALUES ('2024-02-18 21:00:00', 'server1', 19.4, 10.4);
INSERT 0 1
SELECT * FROM serverspec;
time | servername | cpu_userd | memory_used
2024-02-18 21:00:00 | server1 | 19.4 | 10.4
(1 row)
UPDATE serverspec SET cpu_userd = cpu_userd + 0.1 WHERE time = '2024-02-18 21:00:00' and servername = 'server1
';
UPDATE 1
select * from serverspec;
time | servername | cpu_userd | memory_used
2024-02-18 21:00:00 | server1 | 19.5 | 10.4
(1 row)
DELETE FROM serverspec WHERE time = '2024-02-18 21:00:00' and servername = 'server1';
チャンクの確認準備
チャンクの確認のために、事前にデータを投入する。
1分間隔のデータを1年間分投入する。
INSERT INTO serverspec
SELECT time,
servername,
round(random()::numeric * (100 - 0), 1) AS cpu_used,
round(random()::numeric * (100 - 0), 1) AS memory_used
FROM generate_series('2024-01-01 00:00:00'::timestamp, '2024-12-31 23:59:00', '1 minute') AS time,
unnest(ARRAY['server1', 'server2', 'server3']) AS servername;
ANALYZE;
投入データのSQLを少し解説する。
random関数を使用して、0から100の範囲で数値を生成し、round関数で小数第一位を四捨五入する。
round(random()::numeric * (100 - 0), 1)
generate_series関数の第一パラメータに開始値、第二パラメータに終了値、第三パラメータに刻み値を設定する。
下記の場合は、1分刻みのデータを生成する。
generate_series('2024-01-01 00:00:00'::timestamp, '2024-12-31 23:59:00', '1 minute')
www.postgresql.jp
そもそも、チャンクとは
TimescaleDBでのチャンクとは、データを内部的に時間と空間で分割したテーブルのこと。
ユーザは、ハイパーテーブルを介して、チャンクのテーブルを意識することなく、データにアクセスができる。
空間は時間以外にチャンクを分割する条件。
チャンクのデフォルトは、7日ごとに分割される。チャンクを分割する間隔は、create_hypertable
を実行する
際に指定可能。
チャンクの確認
登録したデータによって、チャンクがどのように生成されている状態なのかを確認する。
チャンクを確認する場合、show_chunks
関数を使用する。
select show_chunks('serverspec');
show_chunks
_timescaledb_internal._hyper_1_1_chunk
_timescaledb_internal._hyper_1_2_chunk
_timescaledb_internal._hyper_1_3_chunk
_timescaledb_internal._hyper_1_4_chunk
_timescaledb_internal._hyper_1_5_chunk
_timescaledb_internal._hyper_1_6_chunk
_timescaledb_internal._hyper_1_7_chunk
_timescaledb_internal._hyper_1_8_chunk
<省略>
(53 rows)
オプションをつけることで、取得する範囲を指定できる。
2024-12-01よりも古い日付のものを取得。
SELECT show_chunks('serverspec', older_than => DATE '2024-02-01');
show_chunks
_timescaledb_internal._hyper_1_2_chunk
_timescaledb_internal._hyper_1_3_chunk
_timescaledb_internal._hyper_1_4_chunk
_timescaledb_internal._hyper_1_5_chunk
_timescaledb_internal._hyper_1_6_chunk
(5 rows)
docs.timescale.com
チャンクの削除
チャンクの削除は、drop_chunk
関数を使用する。
データの削除は、DELETEコマンドでできるが、その場合、不要領域が発生し、再利用するためにVACUUMが必要になる。
チャンクごと削除すれば、不要領域が発生せず、VACUUMを実行する必要がない。
SELECT drop_chunks('serverspec', older_than => DATE '2024-02-01');
drop_chunks
_timescaledb_internal._hyper_1_2_chunk
_timescaledb_internal._hyper_1_3_chunk
_timescaledb_internal._hyper_1_4_chunk
_timescaledb_internal._hyper_1_5_chunk
_timescaledb_internal._hyper_1_6_chunk
(5 rows)
docs.timescale.com
データ取得
実際に、データの取得をいろいろとやってみる。
TimescaleDBのGettingStartedを参考にする。
docs.timescale.com
firstとlast
first
は、別の列をキーに並べ替え、最初の値を返す。
last
は、fist
の反対で、最後の値を返す。
SELECT servername, first(cpu_userd, time), last(cpu_userd, time) FROM serverspec WHERE time > now() - INTERVAL '4 days' GROUP BY servername;
servername | first | last
server2 | 14.4 | 19.4
server3 | 26.4 | 2.2
server1 | 1.4 | 47.2
(3 rows)
time_bucket関数は時間を任意の間隔で丸める関数。
time_bucket関数を使用した場合、任意の時間で丸めることが可能。
SELECT time_bucket('1 hour', time) as bucket, first(cpu_userd, time), last(cpu_userd,time) FROM serverspec GROUP BY bucket LIMIT 10;
bucket | first | last
2024-02-01 00:00:00 | 45.8 | 82.3
2024-02-01 01:00:00 | 45 | 75.3
2024-02-01 02:00:00 | 2.1 | 67.4
2024-02-01 03:00:00 | 9 | 66.8
2024-02-01 04:00:00 | 12.4 | 5.5
2024-02-01 05:00:00 | 53.8 | 52.9
2024-02-01 06:00:00 | 12.2 | 69.9
2024-02-01 07:00:00 | 99.6 | 12.1
2024-02-01 08:00:00 | 39.2 | 25.8
2024-02-01 09:00:00 | 3.7 | 29.1
30分間隔にする場合、30 minutes
を指定する。
SELECT time_bucket('30 minutes', time) as bucket, first(cpu_userd, time), last(cpu_userd,time) FROM serverspec GROUP BY bucket LIMIT 10;
bucket | first | last
2024-02-01 00:00:00 | 45.8 | 36.5
2024-02-01 00:30:00 | 71.3 | 82.3
2024-02-01 01:00:00 | 45 | 76.4
2024-02-01 01:30:00 | 78 | 75.3
2024-02-01 02:00:00 | 2.1 | 56.5
2024-02-01 02:30:00 | 29.8 | 67.4
2024-02-01 03:00:00 | 9 | 0.2
2024-02-01 03:30:00 | 47.8 | 66.8
2024-02-01 04:00:00 | 12.4 | 41.1
2024-02-01 04:30:00 | 37.6 | 5.5
Aggregation
時間ごとのCPUのMAX値などを集計してみる。
SELECT time_bucket('1 day', time) as day, servername, max(cpu_userd) as cpu_max, first(cpu_userd, time) as open, last(cpu_userd, time) as close, min(cpu_u
serd) as cpu_min FROM serverspec GROUP BY day, servername ORDER BY day DESC, servername;
day | servername | cpu_max | open | close | cpu_min
2024-12-31 00:00:00 | server1 | 99.8 | 60.1 | 47.2 | 0.1
2024-12-31 00:00:00 | server2 | 100 | 14.1 | 19.4 | 0
2024-12-31 00:00:00 | server3 | 99.9 | 66.8 | 2.2 | 0.1
2024-12-30 00:00:00 | server1 | 100 | 33.5 | 78.3 | 0
2024-12-30 00:00:00 | server2 | 99.9 | 44.8 | 34.5 | 0.1
2024-12-30 00:00:00 | server3 | 100 | 32.5 | 50.7 | 0.1
2024-12-29 00:00:00 | server1 | 99.8 | 42.7 | 31.4 | 0.1
docs.timescale.com
最後に
TimescaleDBのコンテナの起動からデータの集計を試してみて、PostgreSQL単体よりも検索スピードが高速であることを体感できた。(計測まではしていない)
さまざまな機器からの情報を集計し、グラフ化するといった集計処理には最適なデータベースと理解。
Zabbixとも連携ができるように、監視装置からのアラームの情報を集計等でも活躍すると思うので、これからも時系列DBについて勉強しておく。
参考
透過的という言葉とは
qiita.com
パーティショニング
www.postgresql.jp