史上最全PostgreSQL?DBA最常用SQL

更新時(shí)間：2022年10月17日 15:48:09 作者：古道輕風(fēng)

這篇文章主要介紹了PostgreSQL?DBA最常用SQL?,主要包括背景及常用查詢語(yǔ)句，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

背景

建立視圖, 方便查詢

create schema dba;  

create view dba.invalid_index as select indisvalid, indexrelid::regclass, indrelid::regclass, pg_get_indexdef(indexrelid) from pg_index where not indisvalid;
  
create view dba.ro_conflicts as select datname,pg_stat_get_db_conflict_all(oid) conflict_all,pg_stat_get_db_conflict_bufferpin(oid) conflict_bufferpin,pg_stat_get_db_conflict_lock(oid) conflict_lock,pg_stat_get_db_conflict_snapshot(oid) conflict_snapshot,pg_stat_get_db_conflict_startup_deadlock(oid) conflict_deadlock,pg_stat_get_db_conflict_tablespace(oid) conflict_tbs from pg_database;

create or replace procedure dba.tps() as $$
declare
  v1 int8;
  v2 int8;
begin
  select txid_snapshot_xmax(txid_current_snapshot()) into v1;
  commit;
  perform pg_sleep(1);
  select txid_snapshot_xmax(txid_current_snapshot()) into v2;
  commit;
  raise notice 'tps: %', v2-v1;
end;
$$ language plpgsql ;
  
-- 在主節(jié)點(diǎn)查詢
create view dba.ro_delay as select application_name,client_addr,client_port,write_lag,replay_lag,sync_state from pg_stat_replication ;  
  
-- 在standby節(jié)點(diǎn)執(zhí)行， 檢查replay比receive的延遲
create view dba.node_delay as select * from pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn())) as t(delay);
  
-- 在standby節(jié)點(diǎn)執(zhí)行， 檢查receiver接收wal比上游產(chǎn)生wal的延遲. 
create view dba.ro_delay_on_standby as select pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn , received_lsn)) from pg_stat_wal_receiver;
  
-- 在standby節(jié)點(diǎn)執(zhí)行， 接收wal的速度。
CREATE OR REPLACE PROCEDURE dba.wal_receive_bw()
 LANGUAGE plpgsql
AS $procedure$
declare
  v1 pg_lsn;
  v2 pg_lsn;
begin
  select pg_last_wal_receive_lsn() into v1;
  commit;
  perform pg_sleep(1);
  select pg_last_wal_receive_lsn() into v2;
  commit;
  raise notice 'wal receive bw: %/s', pg_size_pretty(pg_wal_lsn_diff(v2,v1));
end;
$procedure$;

-- 在standby節(jié)點(diǎn)執(zhí)行， replay wal的速度。 
CREATE OR REPLACE PROCEDURE dba.wal_replay_bw()
 LANGUAGE plpgsql
AS $procedure$
declare
  v1 pg_lsn;
  v2 pg_lsn;
begin
  select pg_last_wal_replay_lsn() into v1;
  commit;
  perform pg_sleep(1);
  select pg_last_wal_replay_lsn() into v2;
  commit;
  raise notice 'wal replay bw: %/s', pg_size_pretty(pg_wal_lsn_diff(v2,v1));
end;
$procedure$;  

create view dba.topsql as select calls,total_time,total_time/calls,query from pg_stat_statements where query !~ 'rds' order by total_time desc limit 5;  
  
create view dba.qps as with                                                 
a as (select sum(calls) s from pg_stat_statements),     
b as (select sum(calls) s from pg_stat_statements , pg_sleep(1))     
select     
b.s-a.s          -- QPS    
from a,b;   
  
create view dba.session_acting_cnt as select count(*) from pg_stat_activity where wait_event is not null and (backend_xid is not null or backend_xmin is not null);  
  
create view dba.sessions as select * from pg_stat_activity where wait_event is not null and (backend_xid is not null or backend_xmin is not null);  
  
create view dba.locks as with      
t_wait as      
(      
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,     
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,      
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name     
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted     
),     
t_run as     
(     
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,     
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,     
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name     
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted     
),     
t_overlap as     
(     
  select r.* from t_wait w join t_run r on     
  (     
    r.locktype is not distinct from w.locktype and     
    r.database is not distinct from w.database and     
    r.relation is not distinct from w.relation and     
    r.page is not distinct from w.page and     
    r.tuple is not distinct from w.tuple and     
    r.virtualxid is not distinct from w.virtualxid and     
    r.transactionid is not distinct from w.transactionid and     
    r.classid is not distinct from w.classid and     
    r.objid is not distinct from w.objid and     
    r.objsubid is not distinct from w.objsubid and     
    r.pid <> w.pid     
  )      
),      
t_unionall as      
(      
  select r.* from t_overlap r      
  union all      
  select w.* from t_wait w      
)      
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,     
string_agg(     
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||     
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||     
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||      
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||      
'SQL (Current SQL in Transaction): '||chr(10)||    
case when query is null then 'NULL' else query::text end,      
chr(10)||'--------'||chr(10)      
order by      
  (  case mode      
    when 'INVALID' then 0     
    when 'AccessShareLock' then 1     
    when 'RowShareLock' then 2     
    when 'RowExclusiveLock' then 3     
    when 'ShareUpdateExclusiveLock' then 4     
    when 'ShareLock' then 5     
    when 'ShareRowExclusiveLock' then 6     
    when 'ExclusiveLock' then 7     
    when 'AccessExclusiveLock' then 8     
    else 0     
  end  ) desc,     
  (case when granted then 0 else 1 end)    
) as lock_conflict    
from t_unionall     
group by     
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;    
  
create view dba.top10sizetable as   
select schemaname,tablename,pg_size_pretty(pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass)) from pg_tables order by pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass) desc limit 10;  
  
create view dba.top10sizeindex as   
select schemaname,tablename,indexname,pg_size_pretty(pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass)) from pg_indexes order by pg_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass) desc limit 10;  
  
create view dba.top10sizetableindex as   
select schemaname,tablename,pg_size_pretty(pg_total_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass)) from pg_tables order by pg_total_relation_size((quote_ident(schemaname)||'.'||quote_ident(tablename))::regclass) desc limit 10;  
  
create view dba.top10updatetable as  
select schemaname,relname,n_tup_upd,n_tup_del,round(n_tup_hot_upd/(case when n_tup_upd=0 then 1.0 else n_tup_upd::numeric end),4) from pg_stat_all_tables order by n_tup_upd+n_tup_del desc limit 10;  
  
create view dba.top10inserttable as  
select schemaname,relname,n_tup_ins from pg_stat_all_tables order by n_tup_ins desc limit 10;  
  
create view dba.top10deadtable as  
select schemaname,relname,n_dead_tup from pg_stat_all_tables order by n_dead_tup desc limit 10;  
  
create view dba.top10age as  
select relnamespace::regnamespace,relname,pg_size_pretty(pg_relation_size(oid)),age(relfrozenxid) from pg_class where relkind='r' and relnamespace<>'pg_catalog'::regnamespace and relnamespace<>'information_schema'::regnamespace order by age(relfrozenxid) desc,pg_relation_size(oid) desc limit 10;  
  
-- 膨脹點(diǎn)查詢
create view dba.oldestxact as
select datname,usename,xact_start,query_start,backend_xid,backend_xmin,
now()-xact_start as old_ts,
txid_current()-least(backend_xid::text::int8,backend_xmin::text::int8) as old_xacts,
query 
from pg_stat_activity 
where ltrim(lower(query),' ') !~ '^vacuum'
and not (query ~ 'autovacuum' and backend_type <>'client backend')
order by least(backend_xid::text::int8,backend_xmin::text::int8) limit 1;
  
-- 查詢膨脹空間top 10的表  
create view dba.top10bloatsizetable as  
SELECT  
  current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,  
  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,  
  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,  
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,  
  CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,  
  iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,  
  ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,  
  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,  
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,  
  CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,  
  pg_size_pretty(CASE WHEN relpages < otta THEN  
    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END  
    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)  
      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END  
  END) AS totalwastedbytes  
FROM (  
  SELECT  
    nn.nspname AS schemaname,  
    cc.relname AS tablename,  
    COALESCE(cc.reltuples,0) AS reltuples,  
    COALESCE(cc.relpages,0) AS relpages,  
    COALESCE(bs,0) AS bs,  
    COALESCE(CEIL((cc.reltuples*((datahdr+ma-  
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,  
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,  
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols  
  FROM  
     pg_class cc  
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'  
  LEFT JOIN  
  (  
    SELECT  
      ma,bs,foo.nspname,foo.relname,  
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,  
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2  
    FROM (  
      SELECT  
        ns.nspname, tbl.relname, hdr, ma, bs,  
        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,  
        MAX(coalesce(null_frac,0)) AS maxfracsum,  
        hdr+(  
          SELECT 1+count(*)/8  
          FROM pg_stats s2  
          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname  
        ) AS nullhdr  
      FROM pg_attribute att  
      JOIN pg_class tbl ON att.attrelid = tbl.oid  
      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace  
      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname  
      AND s.tablename = tbl.relname  
      AND s.inherited=false  
      AND s.attname=att.attname,  
      (  
        SELECT  
          (SELECT current_setting('block_size')::numeric) AS bs,  
            CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')  
              IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,  
          CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma  
        FROM (SELECT version() AS v) AS foo  
      ) AS constants  
      WHERE att.attnum > 0 AND tbl.relkind='r'  
      GROUP BY 1,2,3,4,5  
    ) AS foo  
  ) AS rs  
  ON cc.relname = rs.relname AND nn.nspname = rs.nspname  
  LEFT JOIN pg_index i ON indrelid = cc.oid  
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid  
) AS sml order by wastedbytes desc limit 5;  
  
-- 查詢膨脹空間top 10的索引  
create view dba.top10bloatsizeindex as  
SELECT  
  current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,  
  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,  
  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,  
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,  
  CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,  
  iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,  
  ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,  
  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,  
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,  
  CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,  
  pg_size_pretty(CASE WHEN relpages < otta THEN  
    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END  
    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)  
      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END  
  END) AS totalwastedbytes  
FROM (  
  SELECT  
    nn.nspname AS schemaname,  
    cc.relname AS tablename,  
    COALESCE(cc.reltuples,0) AS reltuples,  
    COALESCE(cc.relpages,0) AS relpages,  
    COALESCE(bs,0) AS bs,  
    COALESCE(CEIL((cc.reltuples*((datahdr+ma-  
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,  
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,  
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols  
  FROM  
     pg_class cc  
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'  
  LEFT JOIN  
  (  
    SELECT  
      ma,bs,foo.nspname,foo.relname,  
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,  
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2  
    FROM (  
      SELECT  
        ns.nspname, tbl.relname, hdr, ma, bs,  
        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,  
        MAX(coalesce(null_frac,0)) AS maxfracsum,  
        hdr+(  
          SELECT 1+count(*)/8  
          FROM pg_stats s2  
          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname  
        ) AS nullhdr  
      FROM pg_attribute att  
      JOIN pg_class tbl ON att.attrelid = tbl.oid  
      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace  
      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname  
      AND s.tablename = tbl.relname  
      AND s.inherited=false  
      AND s.attname=att.attname,  
      (  
        SELECT  
          (SELECT current_setting('block_size')::numeric) AS bs,  
            CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')  
              IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,  
          CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma  
        FROM (SELECT version() AS v) AS foo  
      ) AS constants  
      WHERE att.attnum > 0 AND tbl.relkind='r'  
      GROUP BY 1,2,3,4,5  
    ) AS foo  
  ) AS rs  
  ON cc.relname = rs.relname AND nn.nspname = rs.nspname  
  LEFT JOIN pg_index i ON indrelid = cc.oid  
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid  
) AS sml order by wastedibytes desc limit 5;  
  
-- 查詢膨脹比例top 10的表(浪費(fèi)空間大于10MB的表)  
create view dba.top10bloatratiotable as  
SELECT  
  current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,  
  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,  
  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,  
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,  
  CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,  
  iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,  
  ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,  
  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,  
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,  
  CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,  
  pg_size_pretty(CASE WHEN relpages < otta THEN  
    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END  
    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)  
      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END  
  END) AS totalwastedbytes  
FROM (  
  SELECT  
    nn.nspname AS schemaname,  
    cc.relname AS tablename,  
    COALESCE(cc.reltuples,0) AS reltuples,  
    COALESCE(cc.relpages,0) AS relpages,  
    COALESCE(bs,0) AS bs,  
    COALESCE(CEIL((cc.reltuples*((datahdr+ma-  
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,  
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,  
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols  
  FROM  
     pg_class cc  
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'  
  LEFT JOIN  
  (  
    SELECT  
      ma,bs,foo.nspname,foo.relname,  
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,  
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2  
    FROM (  
      SELECT  
        ns.nspname, tbl.relname, hdr, ma, bs,  
        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,  
        MAX(coalesce(null_frac,0)) AS maxfracsum,  
        hdr+(  
          SELECT 1+count(*)/8  
          FROM pg_stats s2  
          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname  
        ) AS nullhdr  
      FROM pg_attribute att  
      JOIN pg_class tbl ON att.attrelid = tbl.oid  
      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace  
      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname  
      AND s.tablename = tbl.relname  
      AND s.inherited=false  
      AND s.attname=att.attname,  
      (  
        SELECT  
          (SELECT current_setting('block_size')::numeric) AS bs,  
            CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')  
              IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,  
          CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma  
        FROM (SELECT version() AS v) AS foo  
      ) AS constants  
      WHERE att.attnum > 0 AND tbl.relkind='r'  
      GROUP BY 1,2,3,4,5  
    ) AS foo  
  ) AS rs  
  ON cc.relname = rs.relname AND nn.nspname = rs.nspname  
  LEFT JOIN pg_index i ON indrelid = cc.oid  
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid  
) AS sml   
where (CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END) >= 10240000  
order by tbloat desc,wastedbytes desc limit 5;  
  
-- 查詢膨脹比例top 10的索引(浪費(fèi)空間大于10MB的索引)  
create view dba.top10bloatratioindex as  
SELECT  
  current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,  
  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,  
  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,  
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,  
  CASE WHEN relpages < otta THEN '0 bytes'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize,  
  iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,  
  ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,  
  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,  
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,  
  CASE WHEN ipages < iotta THEN '0 bytes' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize,  
  pg_size_pretty(CASE WHEN relpages < otta THEN  
    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END  
    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)  
      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END  
  END) AS totalwastedbytes  
FROM (  
  SELECT  
    nn.nspname AS schemaname,  
    cc.relname AS tablename,  
    COALESCE(cc.reltuples,0) AS reltuples,  
    COALESCE(cc.relpages,0) AS relpages,  
    COALESCE(bs,0) AS bs,  
    COALESCE(CEIL((cc.reltuples*((datahdr+ma-  
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,  
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,  
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols  
  FROM  
     pg_class cc  
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'  
  LEFT JOIN  
  (  
    SELECT  
      ma,bs,foo.nspname,foo.relname,  
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,  
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2  
    FROM (  
      SELECT  
        ns.nspname, tbl.relname, hdr, ma, bs,  
        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,  
        MAX(coalesce(null_frac,0)) AS maxfracsum,  
        hdr+(  
          SELECT 1+count(*)/8  
          FROM pg_stats s2  
          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname  
        ) AS nullhdr  
      FROM pg_attribute att  
      JOIN pg_class tbl ON att.attrelid = tbl.oid  
      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace  
      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname  
      AND s.tablename = tbl.relname  
      AND s.inherited=false  
      AND s.attname=att.attname,  
      (  
        SELECT  
          (SELECT current_setting('block_size')::numeric) AS bs,  
            CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')  
              IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,  
          CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma  
        FROM (SELECT version() AS v) AS foo  
      ) AS constants  
      WHERE att.attnum > 0 AND tbl.relkind='r'  
      GROUP BY 1,2,3,4,5  
    ) AS foo  
  ) AS rs  
  ON cc.relname = rs.relname AND nn.nspname = rs.nspname  
  LEFT JOIN pg_index i ON indrelid = cc.oid  
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid  
) AS sml   
where (CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END) >= 10240000  
order by ibloat desc,wastedibytes desc limit 5;  
  
create view dba.seqs as select max_value-last_value,* from pg_sequences order by max_value-last_value ;  

-- 查詢沒有使用過的大于1MB的索引 top 10 (注意, PK、UK如果只是用于約束, 可能不會(huì)被統(tǒng)計(jì)計(jì)數(shù),但是不能刪掉)    
create view dba.top10notusedidx as     
select pg_size_pretty(pg_relation_size(indexrelid)),* from pg_stat_all_indexes where pg_relation_size(indexrelid)>=1024000 and (idx_scan=0 or idx_tup_read=0 or idx_tup_fetch=0)    
and schemaname not in ('pg_toast','pg_catalog') order by pg_relation_size(indexrelid) desc limit 10;    
    
-- 查詢沒有使用過的大于1MB的表 top 10     
create view dba.top10notusedtab as     
select pg_size_pretty(pg_relation_size(relid)),* from pg_stat_all_tables     
where pg_relation_size(relid)>=1024000 and seq_scan=0 and idx_scan=0 and schemaname not in ('pg_toast','pg_catalog','information_schema') order by pg_relation_size(relid) desc limit 10;    
    
-- 查詢熱表top 10    
create view dba.top10hottab as     
select pg_size_pretty(pg_relation_size(relid)),* from pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema') order by seq_scan+idx_scan desc, pg_relation_size(relid) desc limit 10;      
    
-- 查詢大于1MB的冷表top 10    
create view dba.top10coldtab as     
select pg_size_pretty(pg_relation_size(relid)),* from pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema') and pg_relation_size(relid)>=1024000 order by seq_scan+idx_scan,pg_relation_size(relid) desc   limit 10;      
    
-- 查詢熱索引top 10    
create view dba.top10hotidx as     
select pg_size_pretty(pg_relation_size(indexrelid)),* from pg_stat_all_indexes where     
schemaname not in ('pg_toast','pg_catalog') order by idx_scan+idx_tup_read+idx_tup_fetch desc, pg_relation_size(indexrelid) desc limit 10;    
    
-- 查詢大于1MB的冷索引top 10(注意, PK、UK如果只是用于約束, 可能不會(huì)被統(tǒng)計(jì)計(jì)數(shù),但是不能刪掉)    
create view dba.top10coldidx as     
select pg_size_pretty(pg_relation_size(indexrelid)),* from pg_stat_all_indexes where pg_relation_size(indexrelid)>=1024000     
and schemaname not in ('pg_toast','pg_catalog') order by idx_scan+idx_tup_read+idx_tup_fetch , pg_relation_size(indexrelid) desc limit 10;    
  
-- freeze風(fēng)暴預(yù)測(cè)相關(guān)的3個(gè)視圖
create view dba.v_freeze as    
select     
  e.*,     
  a.*     
from    
(select     
  current_setting('autovacuum_freeze_max_age')::int as v1,            -- 如果表的事務(wù)ID年齡大于該值, 即使未開啟autovacuum也會(huì)強(qiáng)制觸發(fā)FREEZE, 并告警Preventing Transaction ID Wraparound Failures    
  current_setting('autovacuum_multixact_freeze_max_age')::int as v2,  -- 如果表的并行事務(wù)ID年齡大于該值, 即使未開啟autovacuum也會(huì)強(qiáng)制觸發(fā)FREEZE, 并告警Preventing Transaction ID Wraparound Failures    
  current_setting('vacuum_freeze_min_age')::int as v3,                -- 手動(dòng)或自動(dòng)垃圾回收時(shí), 如果記錄的事務(wù)ID年齡大于該值, 將被FREEZE    
  current_setting('vacuum_multixact_freeze_min_age')::int as v4,      -- 手動(dòng)或自動(dòng)垃圾回收時(shí), 如果記錄的并行事務(wù)ID年齡大于該值, 將被FREEZE    
  current_setting('vacuum_freeze_table_age')::int as v5,              -- 手動(dòng)垃圾回收時(shí), 如果表的事務(wù)ID年齡大于該值, 將觸發(fā)FREEZE. 該參數(shù)的上限值為 %95 autovacuum_freeze_max_age    
  current_setting('vacuum_multixact_freeze_table_age')::int as v6,    -- 手動(dòng)垃圾回收時(shí), 如果表的并行事務(wù)ID年齡大于該值, 將觸發(fā)FREEZE. 該參數(shù)的上限值為 %95 autovacuum_multixact_freeze_max_age    
  current_setting('autovacuum_vacuum_cost_delay') as v7,              -- 自動(dòng)垃圾回收時(shí), 每輪回收周期后的一個(gè)休息時(shí)間, 主要防止垃圾回收太耗資源. -1 表示沿用vacuum_cost_delay的設(shè)置    
  current_setting('autovacuum_vacuum_cost_limit') as v8,              -- 自動(dòng)垃圾回收時(shí), 每輪回收周期設(shè)多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty參數(shù)以及周期內(nèi)的操作決定. -1 表示沿用vacuum_cost_limit的設(shè)置    
  current_setting('vacuum_cost_delay') as v9,                         -- 手動(dòng)垃圾回收時(shí), 每輪回收周期后的一個(gè)休息時(shí)間, 主要防止垃圾回收太耗資源.    
  current_setting('vacuum_cost_limit') as v10,                        -- 手動(dòng)垃圾回收時(shí), 每輪回收周期設(shè)多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty參數(shù)以及周期內(nèi)的操作決定.    
  current_setting('autovacuum') as autovacuum                         -- 是否開啟自動(dòng)垃圾回收    
) a,     
LATERAL (   -- LATERAL 允許你在這個(gè)SUBQUERY中直接引用前面的table, subquery中的column     
select     
pg_size_pretty(pg_total_relation_size(oid)) sz,   -- 表的大小(含TOAST, 索引)    
oid::regclass as reloid,    -- 表名(物化視圖)    
relkind,                    -- r=表, m=物化視圖    
coalesce(    
  least(    
    substring(reloptions::text, 'autovacuum_freeze_max_age=(\d+)')::int,     
    substring(reloptions::text, 'autovacuum_freeze_table_age=(\d+)')::int     
  ),    
  a.v1    
)    
-    
age(case when relfrozenxid::text::int<3 then null else relfrozenxid end)     
as remain_ages_xid,   -- 再產(chǎn)生多少個(gè)事務(wù)后, 自動(dòng)垃圾回收會(huì)觸發(fā)FREEZE, 起因?yàn)槭聞?wù)ID    
coalesce(    
  least(    
    substring(reloptions::text, 'autovacuum_multixact_freeze_max_age=(\d+)')::int,     
    substring(reloptions::text, 'autovacuum_multixact_freeze_table_age=(\d+)')::int     
  ),    
  a.v2    
)    
-    
age(case when relminmxid::text::int<3 then null else relminmxid end)     
as remain_ages_mxid,  -- 再產(chǎn)生多少個(gè)事務(wù)后, 自動(dòng)垃圾回收會(huì)觸發(fā)FREEZE, 起因?yàn)椴l(fā)事務(wù)ID    
coalesce(    
  least(    
    substring(reloptions::text, 'autovacuum_freeze_min_age=(\d+)')::int    
  ),    
  a.v3    
) as xid_lower_to_minage,    -- 如果觸發(fā)FREEZE, 該表的事務(wù)ID年齡會(huì)降到多少    
coalesce(    
  least(    
    substring(reloptions::text, 'autovacuum_multixact_freeze_min_age=(\d+)')::int    
  ),    
  a.v4    
) as mxid_lower_to_minage,   -- 如果觸發(fā)FREEZE, 該表的并行事務(wù)ID年齡會(huì)降到多少    
case     
  when v5 <= age(case when relfrozenxid::text::int<3 then null else relfrozenxid end) then 'YES'    
  else 'NOT'    
end as vacuum_trigger_freeze1,    -- 如果手工執(zhí)行VACUUM, 是否會(huì)觸發(fā)FREEZE, 觸發(fā)起因(事務(wù)ID年齡達(dá)到閾值)    
case     
  when v6 <= age(case when relminmxid::text::int<3 then null else relminmxid end) then 'YES'    
  else 'NOT'    
end as vacuum_trigger_freeze2,    -- 如果手工執(zhí)行VACUUM, 是否會(huì)觸發(fā)FREEZE, 觸發(fā)起因(并行事務(wù)ID年齡達(dá)到閾值)    
reloptions                        -- 表級(jí)參數(shù), 優(yōu)先. 例如是否開啟自動(dòng)垃圾回收, autovacuum_freeze_max_age, autovacuum_freeze_table_age, autovacuum_multixact_freeze_max_age, autovacuum_multixact_freeze_table_age    
from pg_class     
  where relkind in ('r','m')    
) e     
order by     
  least(e.remain_ages_xid , e.remain_ages_mxid),  -- 排在越前, 越先觸發(fā)自動(dòng)FREEZE, 即風(fēng)暴來(lái)臨的預(yù)測(cè)    
  pg_total_relation_size(reloid) desc   -- 同樣剩余年齡, 表越大, 排越前    
;    

create view dba.v_freeze_stat as    
select     
wb,                                                     -- 第幾個(gè)BATCH, 每個(gè)batch代表流逝100萬(wàn)個(gè)事務(wù)     
cnt,                                                    -- 這個(gè)batch 有多少表    
pg_size_pretty(ssz) as ssz1,                            -- 這個(gè)batch 這些 表+TOAST+索引 有多少容量    
pg_size_pretty(ssz) as ssz2,                            -- 這個(gè)batch FREEZE 會(huì)導(dǎo)致多少讀IO    
pg_size_pretty(ssz*3) as ssz3,                          -- 這個(gè)batch FREEZE 最多可能會(huì)導(dǎo)致多少寫IO (通常三份 : 數(shù)據(jù)文件, WAL FULL PAGE, WAL)    
pg_size_pretty(min_sz) as ssz4,                         -- 這個(gè)batch 最小的表多大    
pg_size_pretty(max_sz) as ssz5,                         -- 這個(gè)batch 最大的表多大    
pg_size_pretty(avg_sz) as ssz6,                         -- 這個(gè)batch 平均表多大    
pg_size_pretty(stddev_sz) as ssz7,                      -- 這個(gè)batch 表大小的方差, 越大, 說(shuō)明表大小差異化明顯    
min_rest_age,                                           -- 這個(gè)batch 距離自動(dòng)FREEZE最低剩余事務(wù)數(shù)    
max_rest_age,                                           -- 這個(gè)batch 距離自動(dòng)FREEZE最高剩余事務(wù)數(shù)    
stddev_rest_age,                                        -- 這個(gè)batch 距離自動(dòng)FREEZE剩余事務(wù)數(shù)的方差, 越小，說(shuō)明這個(gè)batch觸發(fā)freeze將越平緩, 越大, 說(shuō)明這個(gè)batch將有可能在某些點(diǎn)集中觸發(fā)freeze (但是可能集中觸發(fā)的都是小表)    
corr_rest_age_sz,                                       -- 表大小與距離自動(dòng)freeze剩余事務(wù)數(shù)的相關(guān)性，相關(guān)性越強(qiáng)(值趨向1或-1) stddev_rest_age 與 sz7 說(shuō)明的問題越有價(jià)值    
round(100*(ssz/(sum(ssz) over ())), 2)||' %' as ratio   -- 這個(gè)BATCH的容量占比，占比如果非常不均勻，說(shuō)明有必要調(diào)整表級(jí)FREEZE參數(shù)，讓占比均勻化    
from         
(    
select a.*, b.* from     
(    
select     
  min(least(remain_ages_xid, remain_ages_mxid)) as v_min,   -- 整個(gè)數(shù)據(jù)庫(kù)中離自動(dòng)FREEZE的 最小 剩余事務(wù)ID數(shù)    
  max(least(remain_ages_xid, remain_ages_mxid)) as v_max    -- 整個(gè)數(shù)據(jù)庫(kù)中離自動(dòng)FREEZE的 最大 剩余事務(wù)ID數(shù)    
from v_freeze    
) as a,    
LATERAL (  -- 高級(jí)SQL    
select     
width_bucket(    
  least(remain_ages_xid, remain_ages_mxid),     
  a.v_min,    
  a.v_max,    
  greatest((a.v_max-a.v_min)/1000000, 1)   -- 100萬(wàn)個(gè)事務(wù), 如果要更改統(tǒng)計(jì)例如，修改這個(gè)值即可    
) as wb,      
count(*) as cnt,     
sum(pg_total_relation_size(reloid)) as ssz,     
stddev_samp(pg_total_relation_size(reloid) order by least(remain_ages_xid, remain_ages_mxid)) as stddev_sz,     
min(pg_total_relation_size(reloid)) as min_sz,     
max(pg_total_relation_size(reloid)) as max_sz,     
avg(pg_total_relation_size(reloid)) as avg_sz,     
min(least(remain_ages_xid, remain_ages_mxid)) as min_rest_age,     
max(least(remain_ages_xid, remain_ages_mxid)) as max_rest_age,     
stddev_samp(least(remain_ages_xid, remain_ages_mxid) order by least(remain_ages_xid, remain_ages_mxid)) as stddev_rest_age,     
corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) as corr_rest_age_sz     
from v_freeze     
group by wb     
) as b     
) t     
order by wb; 

create view dba.v_freeze_stat_detail as      
select     
pg_size_pretty(t.ssz) as ssz2,     -- 這個(gè)batch FREEZE 會(huì)導(dǎo)致多少讀IO (表+TOAST+索引)    
pg_size_pretty(t.ssz*3) as ssz3,   -- 這個(gè)batch FREEZE 最多可能會(huì)導(dǎo)致多少寫IO (通常三份 : 數(shù)據(jù)文件, WAL FULL PAGE, WAL)    
pg_size_pretty(t.ssz_sum) as ssz4, -- 所有batch 所有表的總大小  (表+TOAST+索引)    
round(100*(t.ssz/t.ssz_sum), 2)||' %' as ratio_batch,     -- 這個(gè)BATCH的容量占比，目標(biāo)是讓所有BATCH占比盡量一致    
round(100*(pg_total_relation_size(t.reloid)/t.ssz), 2)||' %' as ratio_table,     -- 這個(gè)表占整個(gè)batch的容量占比，大表盡量錯(cuò)開freeze    
t.*      
from         
(    
select a.*, b.* from       
(    
  select     
    min(least(remain_ages_xid, remain_ages_mxid)) as v_min,   -- 整個(gè)數(shù)據(jù)庫(kù)中離自動(dòng)FREEZE的 最小 剩余事務(wù)ID數(shù)    
    max(least(remain_ages_xid, remain_ages_mxid)) as v_max    -- 整個(gè)數(shù)據(jù)庫(kù)中離自動(dòng)FREEZE的 最大 剩余事務(wù)ID數(shù)    
  from v_freeze     
) as a,     
LATERAL (     -- 高級(jí)SQL    
select     
  count(*) over w as cnt,                                                -- 這個(gè)batch 有多少表      
  sum(pg_total_relation_size(reloid)) over () as ssz_sum,                -- 所有batch 所有表的總大小  (表+TOAST+索引)    
  sum(pg_total_relation_size(reloid)) over w as ssz,                     -- 這個(gè)batch 的表大小總和 (表+TOAST+索引)    
  pg_size_pretty(min(pg_total_relation_size(reloid)) over w) as min_sz,  -- 這個(gè)batch 最小的表多大    
  pg_size_pretty(max(pg_total_relation_size(reloid)) over w) as max_sz,  -- 這個(gè)batch 最大的表多大    
  pg_size_pretty(avg(pg_total_relation_size(reloid)) over w) as avg_sz,  -- 這個(gè)batch 平均表多大    
  pg_size_pretty(stddev_samp(pg_total_relation_size(reloid)) over w) as stddev_sz,  -- 這個(gè)batch 表大小的方差, 越大, 說(shuō)明表大小差異化明顯                                                                                                                 
  min(least(remain_ages_xid, remain_ages_mxid)) over w as min_rest_age,             -- 這個(gè)batch 距離自動(dòng)FREEZE最低剩余事務(wù)數(shù)                                                                                                                             
  max(least(remain_ages_xid, remain_ages_mxid)) over w as max_rest_age,             -- 這個(gè)batch 距離自動(dòng)FREEZE最高剩余事務(wù)數(shù)                                                                                                                             
  stddev_samp(least(remain_ages_xid, remain_ages_mxid)) over w as stddev_rest_age,  -- 這個(gè)batch 距離自動(dòng)FREEZE剩余事務(wù)數(shù)的方差, 越小，說(shuō)明這個(gè)batch觸發(fā)freeze將越平緩, 越大, 說(shuō)明這個(gè)batch將有可能在某些點(diǎn)集中觸發(fā)freeze (但是可能集中觸發(fā)的都是小表)    
  corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) over w as corr_rest_age_sz,  -- 表大小與距離自動(dòng)freeze剩余事務(wù)數(shù)的相關(guān)性，相關(guān)性越強(qiáng)(值趨向1或-1) stddev_rest_age 與 stddev_sz 說(shuō)明的問題越有價(jià)值    
  t1.*     
from     
  (    
  select     
    width_bucket(    
      least(tt.remain_ages_xid, tt.remain_ages_mxid),     
      a.v_min,    
      a.v_max,    
      greatest((a.v_max-a.v_min)/1000000, 1)         -- 100萬(wàn)個(gè)事務(wù), 如果要更改統(tǒng)計(jì)例如，修改這個(gè)值即可    
    )     
    as wb,                                           -- 第幾個(gè)BATCH, 每個(gè)batch代表流逝100萬(wàn)個(gè)事務(wù)      
    * from v_freeze tt    
  ) as t1      
  window w as     
  (    
    partition by t1.wb     
  )     
) as b    
) t    
order by     
  t.wb,      
  least(t.remain_ages_xid, t.remain_ages_mxid),       
  pg_total_relation_size(t.reloid) desc       
;      
  
create view dba.top20freezebigtable as 
select relowner::regrole, relnamespace::regnamespace, relname, 
age(relfrozenxid),pg_size_pretty(pg_total_relation_size(oid)) , -- 當(dāng)前年齡 
coalesce(    
  least(    
    substring(reloptions::text, 'autovacuum_freeze_max_age=(\d+)')::int,     
    substring(reloptions::text, 'autovacuum_freeze_table_age=(\d+)')::int     
  ),    
  current_setting('autovacuum_freeze_max_age')::int   
)    
-    
age(case when relfrozenxid::text::int<3 then null else relfrozenxid end)     
as remain_ages_xid,  -- 再產(chǎn)生多少個(gè)事務(wù)后, 自動(dòng)垃圾回收會(huì)觸發(fā)FREEZE, 起因?yàn)槭聞?wù)ID
coalesce(    
  least(    
    substring(reloptions::text, 'autovacuum_freeze_min_age=(\d+)')::int    
  ),    
  current_setting('vacuum_freeze_min_age')::int   
) as xid_lower_to_minage    -- 如果觸發(fā)FREEZE, 該表的事務(wù)ID年齡會(huì)降到多少  
from pg_class where relkind='r' order by pg_total_relation_size(oid) desc limit 20; 


-- 未歸檔wal文件
create view dba.arch_undone as 
select * from pg_ls_archive_statusdir() where name !~ 'done$';

-- 歸檔任務(wù)狀態(tài)
create view dba.arch_status as
select * from pg_stat_get_archiver();

-- wal空間占用
create view dba.walsize as 
select pg_size_pretty(sum(size)) from pg_ls_waldir();

-- 復(fù)制槽狀態(tài)(是否有未使用復(fù)制槽, 可能導(dǎo)致wal日志目錄暴漲(不清理))
create view dba.repslots as 
select * from pg_replication_slots ;

-- 系統(tǒng)強(qiáng)制保留wal大小
create view dba.wal_keep_size as
with a as (select setting from pg_settings where name='wal_keep_segments') , b as (select setting,unit from pg_settings where name='wal_segment_size') select pg_size_pretty(a.setting::int8*b.setting::int8) from a,b;

-- 系統(tǒng)動(dòng)態(tài)檢查點(diǎn)最大wal保留大小
create view dba.max_wal_size as
select setting||' '||unit from pg_settings where name='max_wal_size';
  
-- 長(zhǎng)事務(wù)、prepared statement
create view dba.long_snapshot as 
with a as (select min(transaction::Text::int8) m from pg_prepared_xacts ),
b as (select txid_snapshot_xmin(txid_current_snapshot())::text::int8 as m),
c as (select min(least(backend_xid::text::int8,backend_xmin::text::int8)) m from pg_stat_activity ),
d as (select datname,usename,pid,query_start,xact_start,now(),wait_event,query from pg_stat_activity where backend_xid is not null or backend_xmin is not null
order by least(backend_xid::text::int8,backend_xmin::text::int8) limit 1),
e as (select * from pg_prepared_xacts order by transaction::Text::int8 limit 1)
select b.m-least(a.m,c.m),d.*,e.* from a,b,c,d left join e on (1=1);

常用查詢

1、查詢只讀節(jié)點(diǎn)延遲

-- 在主節(jié)點(diǎn)查詢
select * from dba.ro_delay;  

-- 在只讀節(jié)點(diǎn)查詢
set lock_timeout='10ms';
set statement_timeout='2s';
select * from dba.node_delay;
select * from dba.ro_delay_on_standby;

2、查詢top query, 優(yōu)化之首

select * from dba.topsql;

3、重置top query統(tǒng)計(jì)計(jì)數(shù)器(通常在高峰期來(lái)臨前可以重置,防止結(jié)果干擾)

select pg_stat_statements_reset();

4、查詢 qps , 在psql 終端可以每秒打印一次

select * from dba.qps;  
\watch 1

4.1、查詢tps

call dba.tps();

5、查詢活躍會(huì)話數(shù), 如果超過CPU核數(shù), 說(shuō)明數(shù)據(jù)庫(kù)非常非常繁忙, 需要注意優(yōu)化

select * from dba.session_acting_cnt;

6、當(dāng)前活躍會(huì)話

select * from dba.sessions;

7、查詢鎖等待, 如果有大量長(zhǎng)時(shí)間等待, 需要注意業(yè)務(wù)邏輯是否有問題

select * from dba.locks;

8、查詢占用空間top 10的表

select * from dba.top10sizetable;

9、查詢占用空間top 10的索引

select * from dba.top10sizeindex;

10、查詢占用空間top 10的表(含索引)

select * from dba.top10sizetableindex;

11、查詢膨脹空間top 10的表

select * from dba.top10bloatsizetable;

12、查詢膨脹空間top 10的索引

select * from dba.top10bloatsizeindex;

13、查詢膨脹比例top 10的表

select * from dba.top10bloatratiotable;

14、查詢膨脹比例top 10的索引

select * from dba.top10bloatratioindex;

15、查詢更新和刪除記錄條數(shù)top 10的表

select * from dba.top10updatetable;

16、查詢插入記錄條數(shù)top 10的表

select * from dba.top10inserttable;

17、查詢臟記錄條數(shù)top 10的表

select * from dba.top10deadtable;

18、查詢年齡top 10的表

select * from dba.top10age;

19、查詢當(dāng)前的最老事務(wù)距離當(dāng)前時(shí)間、距離當(dāng)前事務(wù)數(shù), 說(shuō)明膨脹空間大小, 越大可能導(dǎo)致越多膨脹垃圾.

select * from dba.oldestxact; select * from pg_prepared_xacts;

20、查詢序列的剩余空間

select * from dba.seqs;

21、PostgreSQL 誰(shuí)堵塞了誰(shuí)（鎖等待檢測(cè)）- pg_blocking_pids

《PostgreSQL 誰(shuí)堵塞了誰(shuí)（鎖等待檢測(cè)）- pg_blocking_pids》

22、查詢沒有使用過的大于1MB的索引 top 10 (注意, PK、UK如果只是用于約束, 可能不會(huì)被統(tǒng)計(jì)計(jì)數(shù),但是不能刪掉)

select * from dba.top10notusedidx;

23、查詢沒有使用過的大于1MB的表 top 10

select * from dba.top10notusedtab;

24、查詢熱表top 10

select * from dba.top10hottab;

25、查詢大于1MB的冷表top 10

select * from dba.top10coldtab;

26、查詢熱索引top 10

select * from dba.top10hotidx;

27、查詢大于1MB的冷索引top 10(注意, PK、UK如果只是用于約束, 可能不會(huì)被統(tǒng)計(jì)計(jì)數(shù),但是不能刪掉)

select * from dba.top10coldidx;

28、查詢數(shù)據(jù)庫(kù)freeze風(fēng)暴預(yù)測(cè)

select * from dba.v_freeze;

select * from dba.v_freeze_stat;

select * from dba.v_freeze_stat_detail;

查詢top 20的大表大freeze剩余年齡。

select * from dba.top20freezebigtable;
  
-- 結(jié)合dba.tps, 可以通過remain_ages_xid/dba.tps估算每個(gè)表還有多久會(huì)發(fā)生freeze.
call dba.tps();

29、查詢RO節(jié)點(diǎn)讀與replay沖突次數(shù)，建議高頻恢復(fù)中的ro節(jié)點(diǎn)不要跑長(zhǎng)sql。

select * from  dba.ro_conflicts;

30、DBA在RO 節(jié)點(diǎn)人為執(zhí)行SQL前，建議設(shè)置sql超時(shí)，避免長(zhǎng)時(shí)間跑 SQL, 導(dǎo)致不必要的replay延遲和 conflict cancel statement

set statement_timeout ='1s';
set lock_timeout='10ms';

31、RO 節(jié)點(diǎn)的conflict容忍時(shí)間最長(zhǎng)設(shè)置，默認(rèn)為5 min

show max_standby_streaming_delay ;
 max_standby_streaming_delay 
-----------------------------
 5min
(1 row)

32、清理數(shù)據(jù)庫(kù)stat計(jì)數(shù)器

\df *.*reset*
                                              List of functions
   Schema   |                  Name                  |     Result data type     | Argument data types | Type 
------------+----------------------------------------+--------------------------+---------------------+------
 pg_catalog | pg_replication_origin_session_reset    | void                     |                     | func
 pg_catalog | pg_replication_origin_xact_reset       | void                     |                     | func
 pg_catalog | pg_stat_get_bgwriter_stat_reset_time   | timestamp with time zone |                     | func
 pg_catalog | pg_stat_get_db_stat_reset_time         | timestamp with time zone | oid                 | func
 pg_catalog | pg_stat_reset                          | void                     |                     | func
 pg_catalog | pg_stat_reset_shared                   | void                     | text                | func
 pg_catalog | pg_stat_reset_single_function_counters | void                     | oid                 | func
 pg_catalog | pg_stat_reset_single_table_counters    | void                     | oid                 | func
 public     | pg_stat_statements_reset               | void                     |                     | func
(9 rows)

33、在standby節(jié)點(diǎn)執(zhí)行，檢查當(dāng)前standby節(jié)點(diǎn)接收wal的速度

call dba.wal_receive_bw();

34、在standby節(jié)點(diǎn)執(zhí)行，檢查當(dāng)前standby節(jié)點(diǎn)replay wal的速度

call dba.wal_replay_bw();

35、wal文件使用、slot風(fēng)險(xiǎn)查看。

select * from dba.arch_undone;

select * from dba.arch_status;

select * from dba.walsize;

select * from dba.repslots;

select * from dba.wal_keep_size;

select * from dba.max_wal_size;

36、長(zhǎng)事務(wù)、prepared statement