2023年8月18日 星期五

PostgreSQL Reindex 進度查看

在 PG 有一系列 pg_stat_progress_* 的系統表可以查看各個維運指令的執行進度，但這些系統只能夠輔助在我們觀察到單一指令執行慢的時候，去檢視他的執行狀況。

但是當我們在特定需求下需要執行全資料庫的 Reindex 時，或是 Vacuum / Analyze 等等作業，則無從看到整體的執行狀況。

如果想要了解這些就需要從系統表的資訊來進行清查。



資料庫需要啟用 timed_statistics 設定才能進行以下查看，另外像是 Analyze 和 Vacuum 等等也可以透過 pg_stat_all_tables 裡面的資訊，用類似的方法來查詢。

-- Reindex 進度查看 ( Index 數量 )
with complete as
(
    SELECT 
      count(*) as complete
    FROM pg_class
    where relkind = 'i'
    and pg_xact_commit_timestamp(xmin) > '2023-08-18 11:01:39.660168+08' -- 指定 Reindex 開始時間
    and relfilenode != 0 
)
,total as (
    SELECT 
      count(*) as total
    FROM pg_class
    where relkind = 'i'  -- 指定 Index
    and relfilenode != 0  -- 排除一些 Global Instance 共通的 Index
)
select 
  100 * complete / total as percentage
from complete, total;

-- Reindex 進度查看 ( Index 大小 -- 可能會查比較久 )
with complete as
(
    SELECT 
      sum(pg_relation_size(oid)) as complete
    FROM pg_class
    where relkind = 'i'
    and pg_xact_commit_timestamp(xmin) > '2023-08-18 11:03:27.357639+08' -- 指定 Reindex 開始時間
    and relfilenode != 0 
)
,total as (
    SELECT 
      sum(pg_relation_size(oid)) as total
    FROM pg_class
    where relkind = 'i'
    and relfilenode != 0 
)
select 
  round(100 * complete / total, 2) as percentage
from complete, total;

on