pg_stat_all_tablesを使って何を見ようか。

本記事は [PostgreSQL Advent Calendar 2023](https://qiita.com/advent-calendar/2023/postgresql) 20日目の記事です。

今年のアドベントカレンダー、何を書こうかずっと悩んでいましたが、今回は普段の運用や検証でお世話になっている稼働統計ビュー  pg_stat_all_tables について書こうと思います。

# 稼働統計ビュー とは

PostgreSQLでは、データベースの状態を監視し、トラブルの予兆を見つけたり、SQL処理を最適化できるよう等、様々な用途で利用可能な稼働統計ビューというものが記録されています。

PostgreSQL 16では [22種類の稼働統計情報ビュー](https://www.postgresql.org/docs/current/monitoring.html) が提供されています。

今回はそんな稼働統計ビューの中で、運用でも検証でも度々お世話になっている pg_stat_all_tables について、カラムやどういった利用ができるのか例を出しつつ整理してみようと思います。

## pg_stat_all_tables とは

pg_stat_all_tablesは、テーブルに対して実行されたスキャンの回数、INSERT、UPDATE、DELETE等のテーブルの操作が行われた回数、対象のテーブルの有効なレコード数、無効なレコード更新数、メンテナンス系の処理の実行日時といったDB管理者が必要な情報を提供してくれます。

以下にpg_stat_all_tablesのカラムの詳細を示します。
|カラム名|概要|14|15|16|devel|
|:--|:--|:--|:--|:--|:--|
|relid|テーブルのObject Identifier (OID)|◯|◯|◯|◯|
|schemaname|テーブルが存在するスキーマの名前|◯|◯|◯|◯|
|relname|テーブルの名前|◯|◯|◯|◯|
|seq_scan|シーケンシャルスキャンの実行回数（累積値）|◯|◯|◯|◯|
|last_seq_scan|最後にシーケンシャルスキャンが行われた時刻|||◯|◯|
|seq_tup_read |シーケンシャルスキャンでフェッチされたレコード数（累積値）|◯|◯|◯|◯|
|idx_scan |インデックススキャンの実行回数（累積値）|◯|◯|◯|◯|
|last_idx_scan |最後にインデックススキャンが行われた時刻|||◯|◯|
|idx_tup_fetch |インデックススキャンでフェッチされたレコード数（累積値）|◯|◯|◯|◯|
|n_tup_ins |挿入されたレコード数（累積値）|◯|◯|◯|◯|
|n_tup_upd |更新されたレコード数（累積値）|◯|◯|◯|◯|
|n_tup_del |削除されたレコード数（累積値）|◯|◯|◯|◯|
|n_tup_hot_upd |[Heap-Only Tuple（HOT）](https://www.postgresql.org/docs/current/storage-hot.html)更新されたレコード数（累積値）|◯|◯|◯|◯|
|n_tup_newpage_upd |非HOT更新されたレコード数（累積値）|||◯|◯|
|n_live_tup |有効なレコード数（推定値）|◯|◯|◯|◯|
|n_dead_tup |無効なレコード数（推定値）|◯|◯|◯|◯|
|n_mod_since_analyze |最後にANALYZEが行われてから変更されたレコード数（推定値）|◯|◯|◯|◯|
|n_ins_since_vacuum |最後にVACUUMが行われてから挿入されたレコード数（推定値）|◯|◯|◯|◯|
|last_vacuum |最後に手動VACUUMが実行された時刻|◯|◯|◯|◯|
|last_autovacuum |最後に自動VACUUMが実行された時刻|◯|◯|◯|◯|
|last_analyze |最後に手動のANALYZEが実行された時刻|◯|◯|◯|◯|
|last_autoanalyze |最後に自動ANALYZEが実行された時刻|◯|◯|◯|◯|
|vacuum_count |手動VACUUMが実行された回数（累積値）|◯|◯|◯|◯|
|autovacuum_count |自動VACUUMの対象となりVACUUMが実行された回数（累積値）|◯|◯|◯|◯|
|analyze_count |手動でANALYZEが実行された回数（累積値）|◯|◯|◯|◯|
|autoanalyze_count |自動ANALYZEがの対象となりANALYZEが実行された回数（累積値）|◯|◯|◯|◯|

バージョンを重ねるごとに徐々に情報量は増えているので、現在利用を想定しているバージョンで何の情報を取得することができるのかはぜひ押さえておいていただきたいです。

# How to

ここからは pg_stat_all_tables の稼働統計情報を用いて具体的に何を調べることができるのか例を示しつつ考えてみようと思います。

## ケース１：レコードとVACUUM処理の実行状況を確認する
テーブルに対するメンテナンス処理としてVACUUMが適切に行われていそうか、無効なレコード数及びその割合が多くなっているものがないかを確認します。

```sql
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    (n_dead_tup::real / (case when n_live_tup > 0 then n_live_tup
                             else 1 end))::numeric(3,2) as dead_rows_ratio,
    last_vacuum,
    last_autovacuum
FROM
    pg_stat_all_tables
WHERE
    schemaname not in ('pg_catalog','information_schema','pg_toast')
ORDER BY
    n_dead_tup DESC;
```

## ケース２：テーブルやインデックスの利用状況を確認し、無駄なDBオブジェクトの存在有無を確認する。

テーブルの各スキャンの実行回数と最後に実行された時刻を確認することで、稼働中のシステムで利用されていないテーブルやインデックスの有無を確認できます。不要なテーブルやインデックスを削除することでストレージの節約（クラウド環境なら費用削減）やクエリのパフォーマンスを向上等を期待することができます。

```sql
SELECT
    schemaname,
    relname,
    seq_scan,
    last_seq_scan,
    idx_scan,
    last_idx_scan
FROM
    pg_stat_all_tables
WHERE
    schemaname not in ('pg_catalog','information_schema','pg_toast')
ORDER BY
     last_idx_scan DESC;
```

テーブル内では複数のインデックスが定義されていると思うので、不要なインデックスが存在すると思われるテーブルを見つけたら、次のステップとしてテーブル内で定義された個々のインデックスの状態を確認する必要があります。

ユニークキー等、テーブル設計として必要なものは除いて、使用されていないインデックスが判明したら削除を検討してみてください。ものによりますが、インデックスのサイズはテーブルのサイズと同等になることもしばしばあり、未使用のインデックスが存在するだけで処理性能等に直接影響が出てしまうため、注意が必要です。

```sql
SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    last_idx_scan
FROM
    pg_stat_all_indexes
WHERE
    relname = ＜テーブル名＞
ORDER BY
    last_idx_scan DESC;
```

## ケース３：シーケンシャルスキャンの読み込みレコード数の確認

小さいテーブルへのシーケンシャルスキャンは問題ありませんが、巨大なテーブルへのシーケンシャルスキャンは長時間化する可能性があり、処理が重なるとIOに影響を与え、性能トラブルを引き起こすことがあります。
必要なインデックスが不足、実行計画の変動等、原因は様々ありますが、以下のSQLを実行することでシーケンシャルスキャンの実行状況を確認することができます。

```sql
SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    seq_tup_read / seq_scan as avg_seq_tup_read
FROM
    pg_stat_all_tables
WHERE
    seq_scan > 0
    AND schemaname not in ('pg_catalog','information_schema','pg_toast')
ORDER BY
    avg_seq_tup_read DESC;
```

# まとめ
今回、稼働統計情報ビューである pg_stat_all_tables について書きましたが、稼働統計情報は継続的に観測することでDBの利用状況を鮮明に知ることができます。もし、これらの情報を取得していなければ、定期的に確認することを検討してみてください。PostgreSQLが自身で収集している情報だけでもかなり多くのことを知ることができます。こういった情報を活用することでPostgreSQLを最適化に役立てることができます。

今年もあと僅かです。来年も良いPostgreSQLライフをお過ごしください。