PostgreSQL PWI - 1. Wait Interface

データベースのパフォーマンスを評価するうえで重要な指標の一つである「Elapsed Time（経過時間）」は、ユーザーがクエリを要求してから結果を受け取るまでに要する総時間を指します。
「CPU Time」はクエリを処理するために実際に消費された時間を、「Wait Time（待機時間）」は要求された処理が非アクティブな状態でキュー内に待機している時間を意味します。

この中でも Wait Timeは、さまざまな要因によって発生する Wait Event（待機イベント） によって長くなる傾向があり、プロセスが目的の処理を完了するまでにかかる時間を増加させる原因となります。

このようなWait Timeを最適化するために、各種DBMSでは独自の方法でWait Eventをカテゴリ別に分類・管理しています。

本稿では「Wait Event」をテーマに、PostgreSQLにおいてそれらがどのように分類・管理されているかを探り、それをもとにデータベース性能の向上につながるアプローチについて考察していきます。

PostgreSQLは、Wait Eventに関連する性能の問題を扱う際に役立つ情報を様々な方法で提供しています。最初に提供された情報は比較的単純なものでしたが、いくつかのバージョンを経て、現在ではWait Eventに対してより詳細なレベルの情報が提供されています。

PostgreSQL ver.9.6からWait Eventを扱う体系が確立されました。それ以前は、個別に命名されたり分類されていなかったWait Eventを、新たに明確な名前と分類で定義し、各Wait EventをWait Event Type別に分類しました。

PostgreSQL ver.9.6では69種類のWait Eventが新たに命名され、以下のような分類がなされました。

PostgreSQLでは、これら3種類のロックを中心としてWait Event Typeの分類を行っています。これは、プロセスが作業を行うために使用するリソースの大半がロックにより管理されており、多くのWait Eventの原因がロックに起因するためです。

📢 PostgreSQL 9.6以降、pg_stat_activityビューに以下のような変更が加えられました

[〜ver.9.5]

[ver.9.6〜]

Wait Eventはver.9.6以降、さらに細かく分類されるようになり、PostgreSQL ver.16では以下のようなWait Event Typeが定義されています。

📢 ver.9.6からver.16までの間に、Wait Eventは69種類から230種類以上に増加しました

主な変更点：

1. LWLockNamed, LWLockTranche → LWLockに統一
2. 新たにClient、IO、ExtensionなどのTypeが追加
3. 一部のWait Event名や所属Typeが変更

PostgreSQLはWait Event体系の確立と並行して、情報を取得するためのシステムビューも改善してきました。特にpg_stat_activityビューでは、ver.9.6からwait_event_typeおよびwait_eventカラムが追加され、より明確に待機状況を把握できるようになっています。

PostgreSQLでは、待機時間に関する問題を解明し、ボトルネックの原因を突き止めるために、「Wait Event」の発生状況を把握できる複数のシステムビューを提供しています。

特定のSQLクエリが遅延している原因が、CPU資源の不足にあるのか、それともロックなどの待機状態によるものなのかを判別するためには、プロセス単位での現在の待機状況を把握する必要があります。

そのためには、まずどのプロセスがどの種類のリソース待ちにあるのか（たとえばロック、I/O、クライアント待機など）を特定し、その待機時間の長さ、競合相手となっているプロセス、関連するSQL文やテーブル名などの情報を組み合わせて分析していくことが重要です。

PostgreSQLが提供するpg_stat_activityとpg_locksという2つのシステムビューは、このような詳細なモニタリングを実現するために非常に有効です。

以下では、これらのビューの役割と使用方法について詳しく解説します。

pg_stat_activityは、PostgreSQLのプロセスごとの状態を確認できるシステムビューです。特にwait_event_typeとwait_eventカラムが、どのタイプの待機が発生しているかを明示します。

📢 stateカラム（プロセス状態）

pg_locksは現在取得中・待機中のロック情報を確認できるビューです。対象オブジェクトやロックモード、プロセスIDなどの情報が得られます。

📢 詳細：PostgreSQL: Documentation: 16: 54.12. pg_locks

• pg_locks.pid = pg_stat_activity.pid により、プロセスの詳細と突き合わせることができます
• pg_locks.relation = pg_class.oid により、ロック対象のオブジェクト名も取得可能

以下のSQLは、現在デッドロックやロック待機が発生しているプロセスを特定し、それぞれのプロセスが実行しているSQL文や待機時間を照会するものです。

SELECT current_timestamp AS db_time,
       waiter_pid,
       w_info.usename AS waiter_user ,
       w_info.query   AS waiter_query ,
       w_info.query_start AS waiter_query_start ,
       case
           when EXTRACT(EPOCH from current_timestamp - w_info.query_start ) < 0 then 0
           else EXTRACT(EPOCH from current_timestamp - w_info.query_start ) 
       end as waiter_elapsed_time, 
       holder_pid ,
       h_info.usename AS holder_user ,
       h_info.query   AS holder_query ,
       h_info.query_start AS holder_query_start,
       case
           when EXTRACT(EPOCH from current_timestamp - h_info.query_start ) < 0 then 0
           else EXTRACT(EPOCH from current_timestamp - h_info.query_start )
       end as holder_elapsed_time
FROM   (
              SELECT snaptime,
                     locktype,
                     waiter_pid,
                     w_cnt,
                     h_cnt ,
                     CASE
                            WHEN h_cnt=Max(h_cnt) OVER(partition BY waiter_pid) THEN holder_pid
                     END AS holder_pid
              FROM   (
                            SELECT current_timestamp                             AS snaptime,
                                   blocked_locks.locktype                        AS locktype,
                                   blocked_locks.pid                             AS waiter_pid,
                                   count(*) over(partition BY blocked_locks.pid) AS w_cnt,
                                   count(*) over(partition BY blocking_locks.pid)    h_cnt,
                                   blocking_locks.pid                             AS holder_pid
                            FROM   pg_catalog.pg_locks blocked_locks
                            JOIN   pg_catalog.pg_locks blocking_locks
                            ON     blocking_locks.locktype = blocked_locks.locktype
                            AND    blocking_locks.DATABASE IS NOT DISTINCT
                            FROM   blocked_locks.DATABASE
                            AND    blocking_locks.relation IS NOT DISTINCT
                            FROM   blocked_locks.relation
                            AND    blocking_locks.page IS NOT DISTINCT
                            FROM   blocked_locks.page
                            AND    blocking_locks.tuple IS NOT DISTINCT
                            FROM   blocked_locks.tuple
                            AND    blocking_locks.virtualxid IS NOT DISTINCT
                            FROM   blocked_locks.virtualxid
                            AND    blocking_locks.transactionid IS NOT DISTINCT
                            FROM   blocked_locks.transactionid
                            AND    blocking_locks.classid IS NOT DISTINCT
                            FROM   blocked_locks.classid
                            AND    blocking_locks.objid IS NOT DISTINCT
                            FROM   blocked_locks.objid
                            AND    blocking_locks.objsubid IS NOT DISTINCT
                            FROM   blocked_locks.objsubid
                            AND    blocking_locks.pid != blocked_locks.pid
                            WHERE  NOT blocked_locks.granted ) t ) t2
JOIN   pg_catalog.pg_stat_activity w_info
ON     w_info.pid = t2.waiter_pid
JOIN   pg_catalog.pg_stat_activity h_info
ON     h_info.pid = t2.holder_pid WHERE  holder_pid IS NOT null;

また、ロックが設定されたオブジェクトの名前やモードを確認するには、次のようなSQLを使います：

select l.locktype, t.relname, pid, mode, granted, waitstart
from pg_locks l
left join pg_class t on l.relation = t.oid
WHERE l.pid IN (該当するPIDを列挙)
order by pid, locktype, relname;

PostgreSQLでは、ver.9.6以降からWait Eventに関する概念が体系化され、詳細な分類が進みました。これにより、データベースの遅延要因が「なぜ起きているのか」を把握するための分析が可能になりました。

また、pg_stat_activityやpg_locksといったシステムビューを活用することで、実際のプロセス単位での待機状況、ロック状態、実行中のSQLなどを照会できるようになり、性能分析や問題解決における重要な判断材料が得られます。

次回は、Wait Eventの主要な原因となる「Lock」に焦点をあて、PostgreSQLにおけるロックの種類や仕組みについてさらに詳しく解説していきます。

※本記事は、以下のブログ記事を日本語向けに翻訳・再構成したものです：
🔗 PWI - PostgreSQL Wait Interface (EXEM本社ブログ)