pg_catalog をのぞいてみよう
旅行プラットフォーム部エンジニアの吉田です。
本記事では、弊社でメイン DB として使用している PostgreSQL のシステムカタログに関して紹介しようと思います。
※記事に記載のリンクは 2022/10/31 現在のものとなります。
システムカタログとは
システムカタログとは、リレーショナルデータベース管理システムがテーブルや列の情報などのスキーマメタデータと内部的な情報を格納する場所です。
https://www.PostgreSQL.jp/document/14/html/catalogs.html
一般的には information_schema に格納されているテーブル群を指すことが多いと思います。PostgreSQL も information_schema を提供しますが、こちらには SQL 標準で定義された情報のみが格納されています。
後述の pg_class にある物理的なデータの格納場所に関する情報や pg_statistics にある統計情報などは PostgreSQL 固有のものとなります。
こういった情報は information_schema には含まれず、 pg_catalog など PostgreSQL 固有のスキーマに格納されます。本記事では特に、information_schema では提供されない PostgreSQL 独自の情報のいくつかを紹介します。
システムカタログを扱う際の注意点ですが、これらは実テーブル（または view）であり、ユーザーが直接変更できてしまいます。
意図しない操作によってデータベースが破損してしまう可能性があるため、システムカタログを手作業で直接変更することは避け、SQL コマンド経由でテーブルの情報を更新する必要があります。
pg_class を見ればだいたいのことがわかる
pg_class カタログはテーブルと、その他に列を持つもの、あるいはテーブルに似た全てのものを目録にしています。
https://www.PostgreSQL.jp/document/14/html/catalog-pg-class.html
PostgreSQL では様々なオブジェクトの一意キーとして oid という値を使用しますが、pg_class ではテーブルやインデックスといった DB の根幹に関わるオブジェクトの oid を確認できます。そのため、データベースにおける「データ」に関する全てが集約されているといっても過言ではないカタログです。
例えばテーブルにどういったカラムや制約があるのか、どういったインデックスが貼られているのか、といった情報は直接 pg_class から取得することはできませんが、それぞれ reltype や relchecks, relhasindex といったカラムで確認でき、それをもとに詳細な情報を他のシステムカタログから参照できます。
また、テーブルとインデックスに関しては pg_class の情報からデータが物理的に格納される場所（デフォルトでは /path/to/pgdata/base/[database の oid]/[relation の oid] が実体のファイルになります）やページサイズ、テーブルの場合はさらに生きている（削除フラグが立っていない）行数の推定値も確認できます。
このように、 pg_class はテーブルやインデックスのメタ情報を得るうえで避けては通れない要衝となっています。是非ともうまい活用法を見出したいところですね。
例
-- databaseを作成
create database testdb;
-- databaseのoidを取得
-- pg_database はグローバルな pg_catalog テーブル
select oid from pg_database where datname = 'testdb';
oid
--------
693302
(1 row)
-- testdbに移動
\c testdb
-- schemaを作成
create schema testns;
-- schemaのoidを取得
-- pg_namespace や pg_class など、殆どの pg_catalog テーブルはデータベースにつき1つ作成される
select oid from pg_namespace where nspname = 'testns';
oid
--------
693303
(1 row)
-- 作成したschemaに簡単なテーブルを作成し、pkeyを追加（pkeyには自動でbtreeインデックスが貼られる）
create table testns.testtbl as select 1 as id;
alter table testns.testtbl add primary key (id);
analyze testns.testtbl;
-- pg_classで作成したテーブルの情報を取得（一例として、記事で触れたカラムを中心に抽出）
select oid, relname, reltype, relam relpages, reltuples, relchecks, relhasindex from pg_class
-- 先程取得したschemaのoidで絞る
where relnamespace = 693303;
oid | relname | reltype | relpages | reltuples | relchecks | relhasindex
--------+--------------+---------+----------+-----------+-----------+-------------
693304 | testtbl | 693306 | 2 | 6 | 0 | t
693307 | testtbl_pkey | 0 | 403 | 6 | 0 | f
(2 rows)
-- テーブルのoidがわかったので、カラム名の一覧や制約の一覧、インデックスの情報を取得してみる
-- カラム一覧は pg_attribute
select attname from pg_attribute where attrelid = 693304 and attnum > 0;
attname
---------
id
(1 row)
-- 制約一覧は pg_constratint
select conname from pg_constraint where conrelid = 693304;
conname
--------------
testtbl_pkey
(1 row)
-- index一覧は pg_index
-- indexrelid: そのインデックスの pg_class 上のoidで、 testtbl_pkey と一致していることがわかる
-- indrelid: そのインデックスが貼られているテーブルの pg_class 上の oid （とてもややこしい）
-- またindisprimaryはそのインデックスが一意キーかどうかを表す値で、これが true なので pkey であることがわかる
select indexrelid, indisprimary from pg_index where indrelid = 693304;
indexrelid | indisprimary
------------+--------------
693307 | t
(1 row)
-- testtblのアクセスメソッドは2番、testtbl_pkeyのアクセスメソッドは403番ということが分かったのでアクセスメソッドを調べてみる
-- 2番はテーブルアクセスメソッド、403番はbtreeのインデックスアクセスメソッドであることがわかる
select amname, amtype from pg_am where oid in (2, 403);
amname | amtype
--------+--------
heap | t
btree | i
(2 rows)
注：PostgreSQL のデータに関して
PostgreSQL はデータ更新時に新規レコードとして挿入し、古いレコードは物理的な削除をせず削除フラグを立てる、という仕組み（追記型アーキテクチャ）でトランザクションの同時実行制御を実現しています。このため、更新をかける度に削除フラグが立っているレコードが溜まり、テーブルの物理的な容量は増大します。
テーブルの肥大化を抑制するためには定期的に VACUUM を実行して削除フラグが立っているレコードを除去する必要があります。
最後に VACUUM を実行した後どれくらいの頻度でレコードが更新されてたかや、VACUUM が実行されないままテーブルサイズが意図しない大きさになっていないかといった情報は、 pg_class の他に後述の pg_stat 系の情報から確認できます。
利用可能な拡張機能を確認してみよう
PostgreSQL は（自作も含め）さまざまなサードパーティーの拡張機能を利用できるのが特長の1つです（これはフォルシアが PostgreSQL をメイン DB として採用し続ける大きな理由でもあります。参考：https://www.forcia.com/blog/002454.html ）。
これらは一般的に extension という形でパッケージ化され、利用者が適宜インストールすることで DB 内で利用可能となります。
また、 extension にはバージョニングの仕組みがあり、extension を削除することなくパッチを当てることで副作用を最小限に抑えたバージョンアップが可能です。
今 DB に導入済の（ないし利用可能な）拡張機能がどれか、バージョンはいくつか、どのバージョンが利用可能か、といった情報は、 pg_available_extensions, pg_available_extension_versions から確認できます。
特に pg_available_extension_versions ではインストール可能なバージョンの一覧や拡張間の依存関係などを詳細に確認できます。自分の PostgreSQL のバージョンに即した拡張機能が利用可能か、また自作の拡張機能が正しくバージョニングできる状態にあるかを確認する時などに重宝します。
注意点としては、あくまで拡張のインストールコマンドが実行可能であることしかわからないので、実際にインストールが成功するかどうかは実行するまでわからない、という点が挙げられます（例えば拡張の古いバージョンで互換性が切れているが、定義があるので表示されるというケース）。ここに関しては使用している PostgreSQL のバージョン、拡張機能と相談する必要があります。
例
-- インストール済の拡張とそのバージョンを確認する
-- `installed_version is null` とするとインストール可能だが未インストールの拡張一覧が確認できる
select name, installed_version from pg_available_extensions where installed_version is not null;
name | installed_version
---------+-------------------
plpgsql | 1.0
(1 row)
-- 適当なcontribモジュールのバージョン一覧を確認してみる
-- https://www.postgresql.jp/document/14/html/contrib.html
select * from pg_available_extension_versions where name = 'adminpack';
name | version | installed | superuser | trusted | relocatable | schema | requires | comment
-----------+---------+-----------+-----------+---------+-------------+------------+----------+-----------------------------------------
adminpack | 1.0 | f | t | f | f | pg_catalog | [NULL] | administrative functions for PostgreSQL
adminpack | 1.1 | f | t | f | f | pg_catalog | [NULL] | administrative functions for PostgreSQL
adminpack | 2.0 | f | t | f | f | pg_catalog | [NULL] | administrative functions for PostgreSQL
adminpack | 2.1 | f | t | f | f | pg_catalog | [NULL] | administrative functions for PostgreSQL
(4 rows)
統計情報を活用してパフォーマンス改善に役立てよう
PostgreSQL はテーブルやクエリに関する様々な統計情報を収集しています。カラムに関する情報は pg_stats テーブルで確認できます。
pg_stats では値のヒストグラムや最頻値、物理的な並び順と論理的な並び順との相関などが確認できます。これらの値はプランナが行数を見積もる際に参照している値でもあるため、EXPLAIN した結果実態とかけ離れた行数見積もりが返ってくるケースでは統計情報を確認することでヒントが得られるかもしれません。
また公式ドキュメントには明示的な記載がありません（頑張って探せばあるかもしれません。。）が、pg_stat_xx という名のテーブルは多くが pg_catalog に含まれます。
パフォーマンスチューニングの際、これらの情報を活用することで問題が解決するケースも少なくないでしょう。非常に多くのテーブルが提供されており用途も様々ですが、ここではその一部を紹介します。
- pg_stat_database
データベース全体の統計情報（トランザクション数やコネクション数、各操作の対象となった行数など）を確認できます。特にblks_read（ブロックをディスクから読み込んだ回数）とblks_hit（ブロックをキャッシュから読み込んだ回数）からキャッシュヒット率が確認でき、共有バッファのキャッシュが適切に使用されているかの検証に便利です。 - pg_stat_user_tables
テーブル単位で有効(無効)な行数や、VACUUM 後に挿入された行数の推定値などを確認できます。特にテーブルサイズを確認する際などによく参照する印象です。ユーザーの作成したテーブルに絞って提供されるので、パフォーマンス状況を確認する用途ならばpg_classよりこちらを確認する方が扱いやすいですね。
似たようなテーブルにpg_statio_user_tablesというものも用意されており、pg_stat_databaseで挙げたキャッシュヒット率などをテーブル単位で確認したい場合はこちらから確認できます。 - pg_stat_activity
サーバープロセス単位で活動状況に関連した情報（どれくらいのプロセスが稼働中か、滞留しているリクエストがないかなど）を確認できます。私のチームでもpg_stat_activityを参照した結果、不要なインスタンスが大量に DB へ接続していることが判明したことがあります。
例
-- blks_hit + blks_read が全体の読み込み回数なので、これに対する blks_hit の割合がキャッシュヒット率
-- pg_stat_databaseではキャッシュヒット率の他にもコミット回数やロールバック回数、更新や削除の回数なども確認できる
select xact_commit, xact_rollback, blks_read,blks_hit, 100.0*blks_hit/(blks_hit+blks_read) as cache_ratio, tup_updated, tup_deleted from pg_stat_database where datid = '693302';
xact_commit | xact_rollback | blks_read | blks_hit | cache_ratio | tup_updated | tup_deleted
-------------+---------------+-----------+----------+---------------------+-------------+-------------
114 | 5 | 404 | 14250 | 97.2430735635321414 | 5 | 0
(1 row)
-- pg_stat_user_tablesはテーブルごとのレコード数を比較する際に重宝する（例では1テーブルしか作成していないので比較になっていないが）
select relname, n_live_tup from pg_stat_user_tables;
relname | n_live_tup
---------+------------
testtbl | 6
(1 row)
-- pg_statio_user_table ではレコード(heap)とインデックスそれぞれのキャッシュヒット率が確認できる
select relname, 100.0*heap_blks_hit/(heap_blks_hit+heap_blks_read) as heap_cache_ratio, 100.0*idx_blks_hit/(idx_blks_hit+idx_blks_read) as idx_cache_ratio from pg_statio_user_tables;
relname | heap_cache_ratio | idx_cache_ratio
---------+---------------------+---------------------
testtbl | 87.5000000000000000 | 71.4285714285714286
(1 row)
-- pg_stat_activityで接続中のプロセスを確認。今実行したクエリがactiveになっている
select pid, state, query from pg_stat_activity where datid = '693302' and state is not null;
pid | state | query
-------+--------+----------------------------------------------------------------------------------------------
10370 | active | select pid, state, query from pg_stat_activity where datid = '693302' and state is not null;
(1 row)
-- 別画面からpsqlでログインし、 `begin; select 1;` まで実行した状態（コミットはしてない）
select pid, state, query from pg_stat_activity where datid = '693302' and state is not null;
pid | state | query
-------+---------------------+----------------------------------------------------------------------------------------------
10370 | active | select pid, state, query from pg_stat_activity where datid = '693302' and state is not null;
10475 | idle in transaction | select 1;
(2 rows)
最後に
今回の記事ではほんの一部しか紹介できませんでしたが、 pg_catalog にはまだまだ沢山の統計情報があります（100 を優に超えるテーブルが用意されており、バージョンアップによって増え続けています）。
PostgreSQL を利用する上ではそのアーキテクチャの独自性もあり、保守作業やパフォーマンス改善をするにあたって様々な困難と向き合うことになると思いますが、提供されている豊富なメタデータと慣れ親しみ適切に活用することで、より快適な PostgreSQL ライフを送ることができると思います。
Discussion