pg_stat_activity — Session Monitoring

pg_stat_activity: view "chụp ảnh" mọi backend đang sống, và vì sao đây là công cụ debug production đầu tiên phải nhìn khi cluster có mùi

pg_stat_activity là view hệ thống trong pg_catalog phơi ra một row cho mỗi server process đang sống trên cluster — client backend, autovacuum worker, walsender, background worker, checkpointer, v.v. Đây là view mà on-call mở đầu tiên khi latency spike, khi cluster đơ không rõ lý do, khi một SELECT bình thường chạy 5 phút không xong, khi migration ALTER TABLE treo. Không có nó, câu hỏi "query nào đang block query nào" gần như không trả lời được, incident kéo dài từ vài phút thành vài giờ chỉ vì không xác định được thủ phạm.

Cơ chế hoạt động

Mỗi backend Postgres khi khởi động ghi lại metadata của mình vào một struct trong shared memory (PgBackendStatus); pg_stat_activity là view SQL đọc struct đó và join với các bảng system catalog (pg_database, pg_authid) để trả về tên database, tên user thay vì OID. Mỗi row phản ánh trạng thái tại thời điểm view được đọc — một snapshot in-memory, không phải log historic, không có time-series bên trong.

Các cột quan trọng nhất khi debug:

• pid — process id của backend, dùng để pg_cancel_backend(pid) / pg_terminate_backend(pid).
• backend_type — client backend, autovacuum worker, walsender, parallel worker, checkpointer… Lọc theo cột này để tách "app connection" khỏi các process nội bộ.
• state — active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, disabled.
• query — text của statement gần nhất; nếu state='active' thì đây là câu đang chạy, các state khác thì đây là câu cuối cùng đã chạy.
• query_start, xact_start, state_change, backend_start — bốn mốc thời gian; now() - query_start cho tuổi của query hiện tại, now() - xact_start cho tuổi của transaction.
• wait_event_type + wait_event — nếu backend đang chờ (lock, I/O, IPC), hai cột này chỉ ra chờ cái gì (Lock / transactionid, IO / DataFileRead, Client / ClientRead…).
• application_name, client_addr, client_hostname, client_port — nguồn của connection.
• backend_xid, backend_xmin — xid transaction đã cấp, và xmin horizon do backend này pin.
• query_id (từ Postgres 14) — hash của query, join được với pg_stat_statements.

Query cơ bản nhất, gõ mỗi lần incident:

SELECT pid,
       backend_type,
       state,
       now() - query_start AS query_age,
       now() - xact_start  AS xact_age,
       wait_event_type, wait_event,
       application_name, client_addr,
       left(query, 200)    AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
ORDER BY query_start ASC NULLS LAST;

Hai chi tiết cơ chế quan trọng dev thường bỏ qua. Thứ nhất: text trong cột query bị cắt ở track_activity_query_size byte (mặc định 1024). Với ORM sinh câu 5–10KB, chỉ nhìn pg_stat_activity không đủ, phải tăng GUC này (cần restart vì nó là shared memory). Thứ hai: bản thân view có snapshot semantics — trong cùng một transaction gọi nhiều lần cho kết quả nhất quán; muốn giá trị "mới nhất" phải gọi ngoài transaction hoặc dùng SELECT pg_stat_clear_snapshot() giữa hai lần đọc.

Vấn đề gặp trong production

Failure mode 1: nhìn pg_stat_activity thấy hàng chục backend active mà không biết ai đang block ai. Query chạy lâu có hai lý do khác nhau — thực sự tốn CPU/IO, hoặc đang bị chặn bởi lock của backend khác. Nếu chỉ nhìn query_start và state='active' sẽ không phân biệt được. Đây là lỗi kinh điển khiến incident kéo dài: on-call pg_terminate_backend nhầm vào query nạn nhân, để backend giữ lock ngồi lại tiếp tục block chuỗi.

-- Sai: chỉ lọc query 'lâu', không biết nguyên nhân
SELECT pid, now() - query_start AS age, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '30s';

-- Đúng: dùng pg_blocking_pids để lộ chuỗi block
SELECT
  a.pid                              AS blocked_pid,
  a.usename                          AS blocked_user,
  now() - a.query_start              AS blocked_age,
  a.wait_event_type, a.wait_event,
  left(a.query, 200)                 AS blocked_query,
  pg_blocking_pids(a.pid)            AS blocked_by,
  (SELECT left(query, 200) FROM pg_stat_activity
     WHERE pid = ANY(pg_blocking_pids(a.pid)) LIMIT 1) AS blocker_query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY blocked_age DESC;

pg_blocking_pids(pid) trả về mảng pid đang giữ lock mà backend truyền vào đang chờ. Không có nó, phải tự join pg_locks với chính nó theo (locktype, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid) — dài, dễ sai.

Failure mode 2: query text bị truncate, dev không nhận ra ORM sinh câu gì. SQLAlchemy hoặc Hibernate có thể sinh câu vài KB (nhiều JOIN, IN (?, ?, ..., ?) với vài trăm bind param). Với track_activity_query_size=1024 mặc định, pg_stat_activity.query chỉ trả về phần đầu; dev nhìn thấy SELECT ... FROM huge_view v0 JOIN ... rồi câu bị cắt, không hiểu nó tới từ endpoint nào. Cách sửa: tăng GUC lên 8192 hoặc 16384, và bắt buộc set application_name trong connection string cho mọi service để dò ngược tới code path:

postgresql://svc@db/app?application_name=checkout-api

Trong log_line_prefix cũng nên có %a để log lộ luôn service. Không có application_name rõ ràng, một cluster nhận traffic từ 10 service khác nhau qua cùng PgBouncer coi như mù.

Failure mode 3: sau PgBouncer transaction mode, client_addr là địa chỉ PgBouncer, không phải app instance. Trong pooling transaction (mặc định thực dụng ở prod), một server connection được share giữa nhiều client connection giữa các transaction — nên pg_stat_activity chỉ nhìn thấy pool worker, không thấy app pod. Kết hợp thêm với việc application_name reset theo mỗi transaction, việc truy nguồn incident xuống pod cụ thể trở nên bất khả nếu không set từ app. Cách phòng: bật application_name_add_host bên PgBouncer (nếu có), hoặc app chủ động SET application_name = 'checkout-api@pod-abc' ngay sau khi lấy connection từ pool.

Failure mode 4: query trả về <insufficient privilege> hoặc rỗng. User đọc pg_stat_activity không phải superuser và không có role pg_read_all_stats, sẽ chỉ thấy được row của chính mình cho các cột nhạy cảm. Monitoring user (Datadog agent, Prometheus exporter) cần được grant:

-- Sai: monitoring user thường
CREATE ROLE mon LOGIN PASSWORD '...';
-- exporter chạy pg_stat_activity thấy query = null cho mọi backend khác

-- Đúng: grant role built-in
GRANT pg_read_all_stats, pg_monitor TO mon;

pg_monitor (từ Postgres 10) là role tổng hợp bao pg_read_all_stats + pg_read_all_settings + pg_stat_scan_tables — chuẩn cho monitoring user.

Failure mode 5: polling pg_stat_activity quá dày làm chính nó thành nguồn tải. Một dashboard scrape mỗi giây, mỗi lần SELECT * full cột trên cluster 2000 connection, cộng thêm 10 team đều dựng dashboard giống nhau — bản thân view rẻ, nhưng lock nhẹ trên ProcArray và snapshot dựng liên tục vẫn thấy trong perf top khi cluster đông backend. Cách phòng: scrape 5–15 giây một lần cho metric, chỉ chạy query "liệt kê từng row" khi incident. Với alert thì đẩy về counter/gauge tổng hợp, đừng để exporter kéo cả 2000 row mỗi vòng.

Cách debug và monitor

Triệu chứng để mở view: latency spike đồng loạt (endpoint không đổi mà p99 vọt); alert "connection count gần max_connections"; migration hoặc ALTER TABLE treo; replica lag tăng đột ngột (walsender bị chậm). Cứ mở pg_stat_activity là bước một, không phải bước cuối.

Query "top offenders" — session đang active lâu nhất, kèm blocking:

SELECT pid,
       now() - query_start        AS query_age,
       now() - xact_start         AS xact_age,
       state,
       wait_event_type || ':' || wait_event AS waiting_on,
       pg_blocking_pids(pid)      AS blocked_by,
       application_name,
       left(query, 300)           AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
ORDER BY query_start ASC NULLS LAST
LIMIT 20;

Đếm theo state để có "bức tranh cluster" nhanh:

SELECT state,
       count(*)                          AS conns,
       max(now() - state_change)         AS oldest_in_state,
       max(now() - xact_start)           AS oldest_xact
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY conns DESC;

Tìm walsender chậm khi debug replication lag:

SELECT pid, application_name, client_addr, state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;   -- join khái niệm với pg_stat_activity qua pid

Metric tối thiểu cần đẩy lên monitoring (Prometheus postgres_exporter, Datadog Postgres integration, pganalyze — cả ba đều scrape trực tiếp view này):

• pg_stat_activity_count{state=...} — số backend theo state.
• pg_stat_activity_max_tx_duration — max(now() - xact_start) toàn cluster.
• pg_stat_activity_max_query_duration — max(now() - query_start) WHERE state='active'.
• Số backend wait_event_type='Lock' — dấu hiệu lock contention.
• Số backend theo application_name — capacity per service, phát hiện leak connection.

Tool cấp CLI thường dùng: pg_top (giống top cho Postgres, phơi trực tiếp pg_stat_activity), pgbadger (parse log để bổ sung góc nhìn historic mà pg_stat_activity không có), pg_stat_statements (aggregate per-query, bổ trợ chứ không thay thế). Trong incident on-call, watch -n 1 "psql -c '<query top offenders>'" là combo đủ để theo dõi realtime.

Rule phòng ngừa: bật track_activity_query_size ≥ 4096 cho cluster có ORM; set application_name từ app, không dựa vào default; grant pg_monitor cho monitoring user; alert trên max_query_duration > 30s và wait_event_type='Lock' count > baseline, chứ không đợi "user kêu chậm".

Tradeoff

pg_stat_activity là "snapshot in-memory" — cực rẻ để đọc (không đụng disk, không đụng WAL) và luôn phản ánh trạng thái hiện tại, nhưng chính vì thế nó không giữ lịch sử: một query đã kết thúc trước lúc mở view sẽ không xuất hiện. Muốn hậu kiểm phải song song bật log_min_duration_statement + pg_stat_statements. Trong incident đang diễn ra, đây là công cụ debug production quan trọng nhất; sau incident, log và pg_stat_statements mới là nguồn truy vết.

Cột query bị cắt bởi track_activity_query_size là tradeoff shared-memory vs độ chi tiết: tăng lên 8KB × max_connections=2000 = 16MB shared memory thêm, không đáng kể, nhưng cần restart. Cột wait_event_type/wait_event chỉ chính xác tại lúc chụp — nếu backend đang oscillate giữa nhiều wait event, một lần đọc dễ trượt; muốn phân phối chính xác phải sampling nhiều lần (thư viện pg_wait_sampling làm điều đó). Cuối cùng, view là read-only: pg_cancel_backend (huỷ statement) và pg_terminate_backend (cắt connection) là action tương ứng — cancel với active, terminate với idle in transaction hoặc khi cancel không tác dụng. Quy tắc thực tế: mọi query khắc phục incident nên bắt đầu bằng SELECT ... FROM pg_stat_activity và đọc wait_event_type + pg_blocking_pids trước khi bấm pg_terminate_backend — cắt sai backend thường làm incident dài thêm chứ không rút ngắn.

Câu hỏi phỏng vấn

pg_stat_activity dùng để làm gì, các cột chính là gì, và trong incident production khi một query "chạy 5 phút không xong" thì query view này thế nào để tìm nguyên nhân?

pg_stat_activity là view trong pg_catalog phơi một row cho mỗi server process đang sống — client backend, autovacuum worker, walsender, background worker. Nó đọc struct trạng thái trong shared memory, không đụng disk, nên rẻ và luôn phản ánh trạng thái hiện tại; đổi lại nó không giữ lịch sử — query đã kết thúc không xuất hiện. Cột chính khi debug: pid (để pg_cancel_backend/pg_terminate_backend), state (active / idle / idle in transaction / idle in transaction (aborted)), query (bị cắt ở track_activity_query_size, mặc định 1024 byte), query_start / xact_start / state_change (tính tuổi query và transaction), wait_event_type + wait_event (đang chờ gì — Lock, IO, Client, IPC), backend_xmin (xmin horizon backend này pin, liên quan tới VACUUM), application_name + client_addr (nguồn connection), query_id (từ PG14, join sang pg_stat_statements). Khi một query treo 5 phút, quy trình đúng là: mở pg_stat_activity lọc state='active' và sort theo query_start, đọc wait_event_type để biết bản chất — nếu Lock/transactionid thì query đang bị chặn bởi backend khác, dùng pg_blocking_pids(pid) lấy pid blocker, xem query của blocker và state của nó (thường là idle in transaction — thủ phạm thực); nếu IO/DataFileRead thì đang bottleneck disk, không phải lock; nếu Client/ClientRead thì server đợi client gửi tiếp, client mới là chỗ chậm. Xử lý: pg_cancel_backend(pid) cho query active, pg_terminate_backend(pid) cho backend idle in transaction giữ lock. Điểm ăn điểm là: (1) không chỉ trả lời "để xem query đang chạy" mà nêu được cột wait_event_type + pg_blocking_pids là combo lộ chuỗi block; (2) nêu được limit — query bị truncate cần tăng track_activity_query_size, view không có lịch sử nên phải bổ sung pg_stat_statements + log_min_duration_statement, application_name phải set từ app mới truy được nguồn qua PgBouncer; (3) grant pg_monitor cho monitoring user, không thì exporter thấy query=null cho backend của user khác.

Hands-on

Mục tiêu: dựng Postgres, sinh một query chạy > 30s, tìm nó qua pg_stat_activity, phát hiện chuỗi block, cancel query nạn nhân, terminate backend blocker, đo metric như production sẽ scrape.

Dựng container với track_activity_query_size tăng lên và log đầy đủ:

docker run -d --name pg-sess \
  -e POSTGRES_PASSWORD=pw -p 5437:5432 \
  postgres:16 \
  -c track_activity_query_size=8192 \
  -c log_min_duration_statement=1000 \
  -c log_line_prefix='%m [%p] %a %u@%d '

sleep 5
docker exec -i pg-sess psql -U postgres <<'SQL'
CREATE TABLE accounts (
  id      bigserial PRIMARY KEY,
  balance numeric NOT NULL DEFAULT 0
);
INSERT INTO accounts(balance) SELECT 100 FROM generate_series(1, 100000);
CREATE ROLE mon LOGIN PASSWORD 'mon';
GRANT pg_monitor TO mon;
SQL

Bước 1 — Sinh query chạy lâu. Terminal 1:

docker exec -it pg-sess psql "postgresql://postgres:pw@localhost/postgres?application_name=long-query"

-- Session A: pg_sleep giả lập query nặng
SELECT pg_sleep(120), count(*) FROM accounts;

Bước 2 — Terminal 2, tìm query chạy > 30s:

docker exec -it pg-sess psql "postgresql://mon:mon@localhost/postgres?application_name=on-call"

-- Session B: liệt kê query active > 30s
SELECT pid, application_name, state,
       now() - query_start AS query_age,
       wait_event_type, wait_event,
       left(query, 200) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend'
  AND now() - query_start > interval '30s'
ORDER BY query_start ASC;

Phải thấy row của application_name='long-query' với query_age tăng dần.

Bước 3 — Tạo chuỗi block để test pg_blocking_pids. Terminal 3 (blocker):

docker exec -it pg-sess psql "postgresql://postgres:pw@localhost/postgres?application_name=blocker"

-- Session C: giữ ACCESS EXCLUSIVE nhưng không commit
BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- Không gõ gì thêm

Terminal 4 (victim):

docker exec -it pg-sess psql "postgresql://postgres:pw@localhost/postgres?application_name=victim"

-- Session D: sẽ bị chặn
SELECT count(*) FROM accounts;

Bước 4 — Trong Session B, lộ chuỗi block:

SELECT
  a.pid                             AS blocked_pid,
  a.application_name                AS blocked_app,
  now() - a.query_start             AS blocked_age,
  a.wait_event_type || ':' || a.wait_event AS waiting_on,
  pg_blocking_pids(a.pid)           AS blocked_by,
  (SELECT application_name FROM pg_stat_activity
     WHERE pid = ANY(pg_blocking_pids(a.pid)) LIMIT 1) AS blocker_app,
  (SELECT state FROM pg_stat_activity
     WHERE pid = ANY(pg_blocking_pids(a.pid)) LIMIT 1) AS blocker_state
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock';

Kết quả: victim với waiting_on='Lock:relation', blocked_by={<blocker_pid>}, blocker_app='blocker', blocker_state='idle in transaction'.

Bước 5 — Xử lý đúng thứ tự. Cancel không tác dụng với backend idle in transaction (không có statement để cancel), phải terminate:

-- Session B
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'blocker'
  AND state = 'idle in transaction';

Session D unblock ngay, SELECT count(*) trả về; session C nhận FATAL: terminating connection due to administrator command.

Bước 6 — Metric extraction như exporter sẽ chạy:

SELECT
  count(*) FILTER (WHERE state='active')                        AS active_count,
  count(*) FILTER (WHERE state='idle')                          AS idle_count,
  count(*) FILTER (WHERE state='idle in transaction')           AS iit_count,
  count(*) FILTER (WHERE wait_event_type='Lock')                AS lock_waiters,
  EXTRACT(EPOCH FROM max(now() - query_start)
          FILTER (WHERE state='active'))                        AS max_query_age_s,
  EXTRACT(EPOCH FROM max(now() - xact_start))                   AS max_xact_age_s
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Đây là hàng metric tối thiểu đẩy lên Prometheus/Datadog. Alert rule production: max_query_age_s > 30 hoặc lock_waiters > baseline trong 2 phút → page on-call.

Bước 7 — Đo giới hạn track_activity_query_size. Trong session B chạy câu dài:

SELECT length(query) AS stored, query
FROM pg_stat_activity
WHERE pid = pg_backend_pid();

stored sẽ ≤ 8191 (do đã bật 8192 lúc start container). Restart với default 1024 để thấy câu dài bị cắt:

docker rm -f pg-sess
docker run -d --name pg-sess -e POSTGRES_PASSWORD=pw -p 5437:5432 postgres:16

Lặp lại: stored giờ ≤ 1023.

Dọn dẹp:

docker rm -f pg-sess