PostgreSQL 主要監控指令 — pg_stat_activity 和 pg_stat_user_tables

監控資料庫如果沒有 pg_stat_activity 跟 pg_stat_user_tables，就像只看體溫來判斷健康狀況一樣。你只看大方向，根本不知道問題在哪裡。這兩個 PostgreSQL 關鍵指令不只讓你觀察，還能讓你主動分析資料庫裡到底發生什麼事。

什麼是 pg_stat_activity？

pg_stat_activity 是 PostgreSQL 的系統視圖，會顯示所有連線到你資料庫的資訊。它會回答你：誰連進來、現在在跑什麼 query、還有哪些連線卡在閒置狀態。這就是你分析伺服器目前活動狀況的利器。

來看看 pg_stat_activity 裡面幾個主要欄位。datname 是 client 連到的資料庫名稱，usename 是連線用戶名。application_name 會顯示用這個連線的應用程式名稱，client_addr 是 client 的 IP 位址。backend_start 是 client 連進來的時間，state 代表目前連線狀態（active、idle、idle in transaction），query 則是現在或最後執行的查詢。

範例 1：查看所有活躍連線

想看目前活躍的連線，可以執行這個查詢：

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

注意 query 這個欄位。它會顯示現在正在執行的查詢。如果某個查詢跑很久，可能有什麼問題。

範例 2：分析 transaction 狀態

有時候連線會卡在 idle in transaction 狀態。這代表 transaction 開了但沒結束，這樣很容易造成 lock。

SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

怎麼解決？ 如果你發現有「卡住」的 transaction，可以用這個指令把它結束掉：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

有些工程師會玩得太開心。建議你先問一下團隊，這個 process 能不能「殺」掉。呃，抱歉，我是說 — 結束這個連線啦。

監控資料表使用狀況：pg_stat_user_tables 視圖

如果 pg_stat_activity 是看連線，pg_stat_user_tables 就是看資料表效能。你可以知道：哪些表被讀寫最頻繁、哪些表最常被用、哪裡可能有效能瓶頸。

這裡是 pg_stat_user_tables 幾個重點欄位，幫你分析資料表。relname 是資料表名稱，seq_scan 是 sequential scan 次數，idx_scan 是用 index scan 的次數。n_tup_ins 是插入的 row 數，n_tup_upd 是更新的 row 數，n_tup_del 是刪除的 row 數。

範例 1：比對 index 使用率跟 sequential scan

如果 index 用得很少（idx_scan 幾乎是 0），大概可以優化一下查詢。

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

範例結果：

如果你看到 orders 這個表有超多 sequential scan（seq_scan），可以考慮加個 index。想像一下 orders 有 3500 次 sequential scan，只有 100 次 index scan，而 employees 只有 50 次 sequential scan、1000 次 index scan — 這就很明顯該優化了。

範例 2：分析資料表操作次數

想知道資料表有多「活躍」，可以查詢插入、更新、刪除的 row 數：

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

你可以發現什麼？ 插入（n_tup_ins）跟刪除（n_tup_del）次數很高的表，可能是資料庫的 hot spot。這代表它們的效能要特別注意。

實戰效能分析：結合 pg_stat_activity 跟 pg_stat_user_tables 的資料

在分析資料庫效能時，可以把這兩個來源的資料合起來用。先用 pg_stat_activity 找出慢查詢，再用 pg_stat_user_tables 看這些查詢用到哪些表。如果查詢都在 seq_scan 很高的表上跑，可以考慮優化查詢或加 index。

查詢範例：

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';