【PostgreSQL】pg_stat_activity で接続中のセッション状態を確認してみた
本記事では、pg_stat_activity ビューを使って、
接続中のセッションがどのような状態にあるかを確認してみました。
pg_stat_activity は、現在 PostgreSQL に接続しているセッションの情報をリアルタイムで確認できるビューです。
下記公式ドキュメントにある通り、state を確認することでそのセッションが何をしているか確認できるようなので、今回やってみました。
27.2.3. pg_stat_activity
pg_stat_activityはサーバプロセス毎に、そのプロセスの現在の活動に関連する情報を表示する1行を持ちます。
...
state text
現在のバックエンドの総体的な状態です。 以下のいずれかの値を取ることができます。
active: バックエンドは問い合わせを実行中です。idle: バックエンドは新しいクライアントからのコマンドを待機しています。idle in transaction: バックエンドはトランザクションの内部にいますが、現在実行中の問い合わせがありません。
...
PostgreSQL 環境の準備
検証のための実行環境は EC2（AL2023）にインストールした PostgreSQL を使用しました。
EC2 に PostgreSQL をインストールする手順としては下記をご参照ください。
PostgreSQL 17.10 を利用し、検証していきます。
[ec2-user@ip-xx-xx-xx-xx ~]$ sudo -u postgres psql
psql (17.10)
Type "help" for help.
postgres=# select * from version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 17.10 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
やってみた
前提準備
まず PostgreSQL へ接続し、適当な DB を作成しておきます。
postgres=# CREATE DATABASE blog_sample_db;
CREATE DATABASE
その後ターミナルをもう 1 つ追加し、そちらも PostgreSQL へ接続しておきます。
現在は2つターミナルがある状態です。
それぞれのターミナルから blog_sample_db データベースへ接続し、各セッションの PID を確認しておきます。
-- データベース切り替え
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- PID 確認
blog_sample_db=# SELECT pg_backend_pid();
pg_backend_pid
----------------
69073
(1 row)
-- データベース切り替え
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- PID 確認
blog_sample_db=# SELECT pg_backend_pid();
pg_backend_pid
----------------
69079
(1 row)
pg_backend_pid () → integer
現在のセッションにアタッチされたサーバプロセスのプロセスIDを返します。
セッション状態の確認
ターミナル B から現在の pg_stat_activity ビューを確認します。
blog_sample_db=# SELECT
pid,
usename,
state,
query,
now() - query_start AS elapsed
FROM pg_stat_activity
WHERE datname = 'blog_sample_db';
pid | usename | state | query | elapsed
-------+----------+--------+------------------------------------+----------------
69073 | postgres | idle | SELECT pg_backend_pid(); | 00:02:27.84419
69079 | postgres | active | SELECT +| 00:00:00
| | | pid, +|
| | | usename, +|
| | | state, +|
| | | query, +|
| | | now() - query_start AS elapsed+|
| | | FROM pg_stat_activity +|
| | | WHERE datname = 'blog_sample_db'; |
(2 rows)
上記より、プロセスID 69073 のセッションは state が idle になっています。
ターミナルAは何もしていない状態なので、クライアントからの新規コマンドを待機している状態です。
また、69079(ターミナルBのセッション) は、コマンドを実行しているセッションなので、state が問い合わせ実行中を意味する active となっています。
続いて、ターミナルA で現在のセッションを30秒間スリープさせてみて、セッション状態が変化するかを確認してみましょう。
blog_sample_db=# SELECT pg_sleep(30);
(30秒間スリープ。この間は何も表示されない。)
pg_sleepは、指定された秒数が経過するまで、現在のセッションのプロセスを休止させます。
上記実行後、すぐにターミナル B で pg_stat_activity を確認します。
blog_sample_db=# SELECT
pid,
usename,
state,
query,
now() - query_start AS elapsed
FROM pg_stat_activity
WHERE datname = 'blog_sample_db';
pid | usename | state | query | elapsed
-------+----------+--------+------------------------------------+-----------------
69073 | postgres | active | SELECT pg_sleep(30); | 00:00:07.140144
69079 | postgres | active | SELECT +| 00:00:00
| | | pid, +|
| | | usename, +|
| | | state, +|
| | | query, +|
| | | now() - query_start AS elapsed+|
| | | FROM pg_stat_activity +|
| | | WHERE datname = 'blog_sample_db'; |
(2 rows)
上記の通り、ターミナル A で実行したクエリと現在の state が active となることが確認できました。
最後にトランザクション実行中に state がどう変化するかも見てみます。
検証のため、ターミナルAでトランザクションを開始し、放置します。
blog_sample_db=# BEGIN;
BEGIN
blog_sample_db=*#
ターミナルBから pg_stat_activity を確認します。
blog_sample_db=# SELECT
pid,
usename,
state,
query,
now() - query_start AS elapsed
FROM pg_stat_activity
WHERE datname = 'blog_sample_db';
pid | usename | state | query | elapsed
-------+----------+---------------------+------------------------------------+-----------------
69073 | postgres | idle in transaction | BEGIN; | 00:01:08.304266
69079 | postgres | active | SELECT +| 00:00:00
| | | pid, +|
| | | usename, +|
| | | state, +|
| | | query, +|
| | | now() - query_start AS elapsed+|
| | | FROM pg_stat_activity +|
| | | WHERE datname = 'blog_sample_db'; |
(2 rows)
上記より state が idle in transaction になっていますね。
トランザクション中の状態も pg_stat_activity ビューから確認できることがわかりました。
終わりに
今回は、pg_stat_activityビューを用いて、セッションがどのような状態にあるかを確認してみました。
問い合わせ実行中は active、トランザクション中は idle in transaction と state の変化で状態を追えることがわかりました。
本ビューを用いることで、クエリが遅くなった際などに、長いトランザクションが他に実行されていないか？などを調べることができてとても便利だと思いました。
今後は、実際の現場でも使ってみようと思います。
なお、idle in transaction が長時間放置されると VACUUM の妨害につながる可能性があります。
それについてはまた別の記事で検証できたらと思います。
本記事がどなたかのお役に立てば幸いです。
参考情報
