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

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

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

```sql
SELECT * FROM pg_stat_activity;
```

参考

https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

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

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

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

```sql
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';
```

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

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

参考

https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW

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

```sql
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;
```

参考

https://www.postgresql.jp/document/13/html/progress-reporting.html

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

```sql
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` の権限が見れないことがよくあるため注意してください。

参考

https://www.postgresql.jp/document/13/html/progress-reporting.html

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

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

参考

https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

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

```sql
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;
```

参考

https://www.postgresql.jp/document/13/html/monitoring-stats.html

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

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

参考

https://www.postgresql.jp/document/13/html/monitoring-stats.html

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

```sql
SELECT pg_terminate_backend(pid番号);
```

参考

https://www.postgresql.org/docs/13/functions-admin.html

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

```sql
ANALYZE VERBOSE テーブル名
```

参考

https://www.postgresql.org/docs/13/sql-analyze.html

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

```sql
VACUUM VERBOSE ANALYZE テーブル名
```

参考

https://www.postgresql.org/docs/13/sql-vacuum.html

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

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

参考

https://www.postgresql.jp/document/13/html/sql-reindex.html

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