PostgreSQLの運用でよく使うクエリカンペ

業務や個人でPostgreSQLを使うことがあって、何か調査するときに使うクエリを毎回生成しているためここにまとめます。今回はPostgreSQL13が対象バージョンになります。

参照系

実行中のプロセス一覧

以下のコマンドを利用することで、実行中のプロセス一覧を確認することができます。

SELECT * FROM pg_stat_activity;

参考

タプル/VACUUM/ANALYZEの状況確認

以下のコマンドを利用することで、存在するテーブルのタプル状況や、VACUUM・ANALYZEの履歴を確認できます。

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables;

これだと、PostgreSQLの管理テーブルも表示されてしまうため以下のようにスキーマで絞ると見やすいです。

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables where schemaname = 'public';

または、以下のテーブルを利用することでユーザ定義のテーブルだけを参照できます。

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

参考

VACUUM進捗確認

以下のコマンドを利用することで、実行中のVACUUMの進捗が確認できます。

select
  v.pid,
  v.datname,
  c.relname,
  v.phase,
  v.heap_blks_total,
  v.heap_blks_scanned,
  v.heap_blks_vacuumed,
  v.index_vacuum_count,
  v.max_dead_tuples,
  v.num_dead_tuples
from
  pg_stat_progress_vacuum as v
join
  pg_class as c
on v.relid = c.relfilenode;

参考

CREATE INDEX / REINDEX進捗確認

以下のコマンドを利用することで、実行中のCREATE INDEX / REINDEXの進捗が確認できます。

select
    v.pid,
    v.datname,
    c.relname,
    v.phase,
    v.blocks_total,
    v.blocks_done,
    v.tuples_total,
    v.tuples_done,
    v.lockers_total,
    v.lockers_done,
    v.current_locker_pid
from
    pg_stat_progress_create_index as v
        join
    pg_class as c
    on v.index_relid = c.relfilenode;

権限によっては、 AUTOVACUUM の権限が見れないことがよくあるため注意してください。

参考

トランザクションIDの確認

以下のコマンドでどのpidがどのトランザクションIDで実行しているかを確認することができます。

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL;

参考

テーブルごとにseq_scan/index_scanがどれくらい行われているか

以下のコマンドでテーブルごとにseq_scan/index_scanがどれくらい行われているかが確認できます。

SELECT
    schemaname,
    relname,
    n_live_tup,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_tup_hot_upd
FROM
    pg_stat_user_tables;

参考

index利用状況の確認

以下のクエリでindexの利用状況の確認ができます。

SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM
    pg_stat_user_indexes;

参考

操作系

特定のプロセスをkillする

以下のコマンドで特定のプロセスをkillすることができます。上で紹介した、実行中のプロセス一覧確認クエリがpidを確認のうえ実行すると良いでしょう。

SELECT pg_terminate_backend(pid番号);

参考

ANALYZE

以下のコマンドでテーブルを ANALYZE します。基本的には AUTOVACUUM に任せますが、たまに緊急で手動実行することがあります。テーブル名を省略すると全テーブルがANALYZEされます。

ANALYZE VERBOSE テーブル名

参考

VACUUM

以下のコマンドでテーブルを VACUUM します。こちらも基本的には AUTOVACUUM に任せますが、たまに緊急で手動実行することがあります。また基本的には ANALYZE とセットで実行します。

VACUUM VERBOSE ANALYZE テーブル名

参考

REINDEX

以下のコマンドでテーブルを REINDEX します。こちらは基本的には実施しませんが、必要な場合たまに手動実行しています。CONCURRENTLYをつけることで他のクエリのブロッキングを防ぎます。

REINDEX (VERBOSE) TABLE CONCURRENTLY テーブル名
または
REINDEX (VERBOSE) TABLE CONCURRENTLY インデックス名

参考

まとめ

本記事では、私がPostgreSQLの運用でよく使うクエリを紹介しました。他にこんなのよく調べてるよとかあれば教えて下さい。