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

本記事は PostgreSQL Advent Calendar 2023 20日目の記事です。

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

稼働統計ビュー とは

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

PostgreSQL 16では 22種類の稼働統計情報ビュー が提供されています。

今回はそんな稼働統計ビューの中で、運用でも検証でも度々お世話になっている 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）更新されたレコード数（累積値）	◯	◯	◯	◯
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が適切に行われていそうか、無効なレコード数及びその割合が多くなっているものがないかを確認します。

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オブジェクトの存在有無を確認する。

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

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;

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

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

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

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

小さいテーブルへのシーケンシャルスキャンは問題ありませんが、巨大なテーブルへのシーケンシャルスキャンは長時間化する可能性があり、処理が重なるとIOに影響を与え、性能トラブルを引き起こすことがあります。
必要なインデックスが不足、実行計画の変動等、原因は様々ありますが、以下の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ライフをお過ごしください。