Zenn
Open13

TimescaleDBを操作する上で必要になったSQLや知識について

PostgreSQL
TimescaleDB
ピン留めされたアイテム
NMNM

ハイパーテーブル動作確認まとめ

そもそもハイパーテーブル？

SELECT * FROM timescaledb_information.hypertables;

リストにテーブル名があればハイパーテーブル

チャンク分割の期間

SELECT h.table_name, c.interval_length 
FROM _timescaledb_catalog.dimension c 
JOIN _timescaledb_catalog.hypertable h 
ON h.id = c.hypertable_id;

マイクロ秒

削除ポリシーある？

SELECT job_id, hypertable_name, proc_name, scheduled, config, next_start 
FROM timescaledb_information.jobs 
WHERE hypertable_name <> '' 
AND proc_name = 'policy_retention';

圧縮ポリシーある？

SELECT job_id, hypertable_name, proc_name, scheduled, config, next_start 
FROM timescaledb_information.jobs 
WHERE hypertable_name <> '' 
AND proc_name = 'policy_compression';

ポリシー動いてる？

SELECT hypertable_name, job_id, last_run_started_at, last_successful_finish, last_run_status, job_status, last_run_duration, next_start, total_runs, total_successes, total_failures 
FROM timescaledb_information.job_stats 
WHERE hypertable_name <> '';

チャンクのサイズは？

SELECT * FROM chunks_detailed_size('metrics');

圧縮されているチャンクがある？

SELECT * FROM hypertable_compression_stats('metrics');

number_compressed_chunks が0やnullなら圧縮されていない。

テーブルごとに確認するならこっち

SELECT * FROM chunk_compression_stats('metrics') ORDER BY chunk_name;

compression_status を見る

NMNM

Githubのissueやstackoverflowにしか回答が無いものもあったので備忘録として

NMNM

実行環境

SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit

SELECT extversion FROM pg_extension where extname = 'timescaledb';
 extversion
------------
 2.9.3

現在の最新は2.10.2
https://docs.timescale.com/about/latest/release-notes/

NMNM

time_bucket_gapfill
時間指定したカラムをフォーマットすると、エラーERROR: no top level time_bucket_gapfill in group by clauseが出力され、結果が得られない。
例えば id, time, valueを持つテーブルで、valueをidごとの5分平均で求めた場合

SELECT id, time_bucket_gapfill('5 minutes', time) AS date, avg(value) 
FROM metrics 
WHERE time BETWEEN '2023-04-01' AND '2023-04-02'
GROUP BY date, id
ORDER BY date, id

上記のSQLは動作するが、以下の様にdateをepochで取得しようとするとエラーとなる

SELECT id, EXTRACT (EPOCH FROM time_bucket_gapfill('5 minutes', time)) AS date, avg(value) 
FROM metrics 
WHERE time BETWEEN '2023-04-01' AND '2023-04-02'
GROUP BY date, id
ORDER BY date, id

WITHを使って先に計算すれば出力できる。

WITH gapfilled_data AS (
    SELECT 
        id, time_bucket_gapfill('5 minutes', time) AS date, avg(value) AS value
    FROM metrics
    WHERE time BETWEEN '2023-04-01' AND '2023-04-02'
    GROUP BY date, id 
)
SELECT
    id, EXTRACT (EPOCH FROM date), value
FROM gapfilled_data
ORDER BY date, id

なお通常のtime_bucket関数では起こらない

参考：
https://github.com/timescale/timescaledb/issues/1435

NMNM

圧縮ポリシーなどのワーカーが動作していない場合に確認する項目

  1. 圧縮ポリシーが設定されているかの確認
SELECT schedule_interval, config->>'compress_after', hypertable_name 
FROM timescaledb_information.jobs 
WHERE timescaledb_information.jobs.proc_name = 'policy_compression';
  1. ジョブの予約確認(上記のschedule_intervalと同じ設定が存在するか)
SELECT * FROM timescaledb_information.jobs WHERE hypertable_name = 'metrics';
  1. 直近のジョブの実行結果を確認(上記のjob_idと一致するものを探す)
    ワーカーが動いていない場合、結果が無い
SELECT * FROM timescaledb_information.job_stats;
  1. ワーカーを再起動し、postgresのログを確認
    大体何かしらエラーが出る
SELECT _timescaledb_internal.start_background_workers();
NMNM

実環境で以下のエラーが再起動時に出力された。
FATAL: terminating background worker "Compression Policy [1304]" due to administrator command
結果的に権限が無いという話では無く、postgresのconfファイルでmax_worker_processes設定が無かった。（もしくは実行しているワーカー数より少ない）

トラブルシュートになった公式のフォーラム
https://www.timescale.com/forum/t/failed-to-start-a-background-worker-error-timescaledb/56

NMNM

削除ポリシーの確認

SELECT schedule_interval, config->>'drop_after', hypertable_name FROM timescaledb_information.jobs 
WHERE timescaledb_information.jobs.proc_name = 'policy_retention';

チャンク間隔の確認・変更

確認 (returnはマイクロ秒)

SELECT h.table_name, c.interval_length
FROM _timescaledb_catalog.dimension c
JOIN _timescaledb_catalog.hypertable h
ON h.id = c.hypertable_id;

変更

  • 第一引数はハイパーテーブル名
  • 第二引数にInterval関数かマイクロ秒で期間を指定する。
SELECT set_chunk_time_interval('metrics', INTERVAL '60 d');
NMNM

ハイパーテーブルのサイズについて

チャンクごとのサイズ

SELECT * FROM chunks_detailed_size('metrics');

テーブルの合計サイズ
単一テーブル

SELECT hypertable_size('metrics');

または

SELECT sum(total_bytes) FROM chunks_detailed_size('metrics');

DB内全てのハイパーテーブル

SELECT 
  hypertable_name, 
  hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass) 
FROM timescaledb_information.hypertables;

圧縮率
単一テーブル

SELECT 1 - (ROUND(after_compression_total_bytes / before_compression_total_bytes::NUMERIC,2)) FROM hypertable_compression_stats('metrics');

chunks_detailed_size()の結果から圧縮されていないチャンクを除外したsum(total_bytes)と、after_compression_total_bytesが一致しない...
DB内全てのテーブルに対する関数は公式に無さそう。
また、圧縮率は計算で出せるから関数は作らない旨の記載がissueにある