za-arthur/postgres_queries.md

• Download ZIP

$select application_name,    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) send_lag,    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) replay_lag,    replay_lagfrom pg_stat_replication;$select slot_name, slot_type, active,    case when not pg_is_in_recovery() then pg_size_pretty(pg_current_wal_lsn()- restart_lsn) endas current_lag_bytes,    case when not pg_is_in_recovery() then pg_size_pretty(pg_current_wal_lsn()- confirmed_flush_lsn) endas current_flush_lag_bytesfrom pg_replication_slots sorder bys.slot_name;

Vacuum stat

SELECTp.pid, now()-a.xact_startAS duration,    coalesce(wait_event_type||'.'|| wait_event,'f')AS waiting,    CASE        WHENa.query ~*'^autovacuum.*to prevent wraparound' THEN'wraparound'        WHENa.query ~*'^vacuum' THEN'user'        ELSE'regular'    ENDAS mode,p.datnameAS database,p.relid::regclassAS table,p.phase,    pg_size_pretty(p.heap_blks_total* current_setting('block_size')::int)AS table_size,    pg_size_pretty(pg_total_relation_size(relid))AS total_size,    pg_size_pretty(p.heap_blks_scanned* current_setting('block_size')::int)AS scanned,    pg_size_pretty(p.heap_blks_vacuumed* current_setting('block_size')::int)AS vacuumed,    round(100.0*p.heap_blks_scanned/p.heap_blks_total,1)AS scanned_pct,    round(100.0*p.heap_blks_vacuumed/p.heap_blks_total,1)AS vacuumed_pct,p.index_vacuum_count,    round(100.0*p.dead_tuple_bytes/p.max_dead_tuple_bytes,1)AS dead_pctFROM pg_stat_progress_vacuum pJOIN pg_stat_activity a using (pid)ORDER BY now()-a.xact_startDESC;

Last vacuum run

SELECT relname, last_vacuum, last_autovacuumFROM pg_stat_all_tables;

Find the database and table causing the wraparound

SELECT datname,        age(datfrozenxid),2^31-1000000-age(datfrozenxid)as remainingFROM pg_databaseORDER BY3;SELECTc.relnamespace::regnamespaceas schema_name,c.relnameas table_name,       greatest(age(c.relfrozenxid),age(t.relfrozenxid))as age,2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid))as remainingFROM pg_class cLEFT JOIN pg_class tONc.reltoastrelid=t.oidWHEREc.relkindIN ('r','m')ORDER BY4;

Table bloat (check_postgres)

SELECT schemaname, tablename,    pg_size_pretty(wastedbytes), tbloatFROM (SELECT        schemaname, tablename,        ROUND((CASE WHEN otta=0 THEN0.0 ELSEsml.relpages::float/otta END)::numeric,1)AS tbloat,        CASE WHEN relpages< otta THEN0 ELSE bs*(sml.relpages-otta)::BIGINT ENDAS wastedbytesFROM (SELECT            schemaname, tablename,cc.relpages, bs,            CEIL((cc.reltuples*((datahdr+ma-                (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float))AS ottaFROM (SELECT                ma,bs,schemaname,tablename,                (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numericAS datahdr,                (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END)))AS nullhdr2FROM (SELECT                    schemaname, tablename, hdr, ma, bs,SUM((1-null_frac)*avg_width)AS datawidth,MAX(null_frac)AS maxfracsum,                    hdr+(SELECT1+count(*)/8FROM pg_stats s2WHERE null_frac<>0ANDs2.schemaname=s.schemanameANDs2.tablename=s.tablename                    )AS nullhdrFROM pg_stats s, (SELECT                        (SELECT current_setting('block_size')::numeric)AS bs,                        CASE WHENsubstring(v,12,3)IN ('8.0','8.1','8.2') THEN27 ELSE23 ENDAS hdr,                        CASE WHEN v ~'mingw32' THEN8 ELSE4 ENDAS maFROM (SELECT version()AS v)AS foo                )AS constantsGROUP BY1,2,3,4,5            )AS foo        )AS rsJOIN pg_class ccONcc.relname=rs.tablenameJOIN pg_namespace nnONcc.relnamespace=nn.oidANDnn.nspname=rs.schemanameANDnn.nspname<>'information_schema'    )AS sml)AS smlORDER BY wastedbytesDESC, tablename;

Table bloat (aggregated, check_postgres)

SELECT schemaname,    pg_size_pretty(sum(wastedbytes))FROM (SELECT        schemaname, tablename,        ROUND((CASE WHEN otta=0 THEN0.0 ELSEsml.relpages::float/otta END)::numeric,1)AS tbloat,        CASE WHEN relpages< otta THEN0 ELSE bs*(sml.relpages-otta)::BIGINT ENDAS wastedbytesFROM (SELECT            schemaname, tablename,cc.relpages, bs,            CEIL((cc.reltuples*((datahdr+ma-                (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float))AS ottaFROM (SELECT                ma,bs,schemaname,tablename,                (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numericAS datahdr,                (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END)))AS nullhdr2FROM (SELECT                    schemaname, tablename, hdr, ma, bs,SUM((1-null_frac)*avg_width)AS datawidth,MAX(null_frac)AS maxfracsum,                    hdr+(SELECT1+count(*)/8FROM pg_stats s2WHERE null_frac<>0ANDs2.schemaname=s.schemanameANDs2.tablename=s.tablename                    )AS nullhdrFROM pg_stats s, (SELECT                        (SELECT current_setting('block_size')::numeric)AS bs,                        CASE WHENsubstring(v,12,3)IN ('8.0','8.1','8.2') THEN27 ELSE23 ENDAS hdr,                        CASE WHEN v ~'mingw32' THEN8 ELSE4 ENDAS maFROM (SELECT version()AS v)AS foo                )AS constantsGROUP BY1,2,3,4,5            )AS foo        )AS rsJOIN pg_class ccONcc.relname=rs.tablenameJOIN pg_namespace nnONcc.relnamespace=nn.oidANDnn.nspname=rs.schemanameANDnn.nspname<>'information_schema'    )AS sml)AS smlGROUP BY1;

Table bloat

SELECT current_database(), schemaname, tblname,  pg_size_pretty((bs*tblpages)::bigint)AS real_size,  CASE WHEN tblpages- est_tblpages_ff>0    THEN pg_size_pretty(((tblpages-est_tblpages_ff)*bs)::bigint)    ELSE'0'  ENDAS bloat_size,  CASE WHEN tblpages- est_tblpages_ff>0    THEN100* (tblpages- est_tblpages_ff)/tblpages::float    ELSE0  ENDAS bloat_pctFROM (SELECT    ceil( reltuples/ ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) )+ ceil( toasttuples/4 )AS est_tblpages_ff,    tblpages, fillfactor, bs, schemaname, tblnameFROM (SELECT      (4+ tpl_hdr_size+ tpl_data_size+ (2*ma)- CASE WHEN tpl_hdr_size%ma=0 THEN ma ELSE tpl_hdr_size%ma END- CASE WHEN ceil(tpl_data_size)::int%ma=0 THEN ma ELSE ceil(tpl_data_size)::int%ma END      )AS tpl_size, (heappages+ toastpages)AS tblpages,      reltuples, toasttuples, bs, page_hdr, schemaname, tblname, fillfactorFROM (SELECTtbl.oidAS tblid,ns.nspnameAS schemaname,tbl.relnameAS tblname,tbl.reltuples,tbl.relpagesAS heappages, coalesce(toast.relpages,0)AS toastpages,        coalesce(toast.reltuples,0)AS toasttuples,        coalesce(substring(          array_to_string(tbl.reloptions,'')FROM'fillfactor=([0-9]+)')::smallint,100)AS fillfactor,        current_setting('block_size')::numericAS bs,        CASE WHEN version()~'mingw32'OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN8 ELSE4 ENDAS ma,24AS page_hdr,23+ CASE WHENMAX(coalesce(s.null_frac,0))>0 THEN (7+count(s.attname) )/8 ELSE0::int END+ CASE WHEN bool_or(att.attname='oid'andatt.attnum<0) THEN4 ELSE0 ENDAS tpl_hdr_size,sum( (1-coalesce(s.null_frac,0))* coalesce(s.avg_width,0) )AS tpl_data_size,        bool_or(att.atttypid='pg_catalog.name'::regtype)ORsum(CASE WHENatt.attnum>0 THEN1 ELSE0 END)<>count(s.attname)AS is_naFROM pg_attributeAS attJOIN pg_classAS tblONatt.attrelid=tbl.oidJOIN pg_namespaceAS nsONns.oid=tbl.relnamespaceLEFT JOIN pg_statsAS sONs.schemaname=ns.nspnameANDs.tablename=tbl.relnameANDs.inherited=falseANDs.attname=att.attnameLEFT JOIN pg_classAS toastONtbl.reltoastrelid=toast.oidWHERE NOTatt.attisdroppedANDtbl.relkindin ('r','m')GROUP BY1,2,3,4,5,6,7,8,9,10ORDER BY2,3    )AS s  )AS s2)AS s3WHERE (CASE WHEN tblpages- est_tblpages_ff>0    THEN100* (tblpages- est_tblpages_ff)/tblpages::float    ELSE0  END)>0ORDER BY6desc;

Table bloat (aggregated)

SELECT schemaname,   pg_size_pretty(sum((bs*tblpages))::bigint)AS real_size,  pg_size_pretty(sum(CASE WHEN tblpages- est_tblpages_ff>0    THEN (tblpages-est_tblpages_ff)*bs    ELSE0  END)::bigint)AS bloat_sizeFROM (SELECT    ceil( reltuples/ ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) )+ ceil( toasttuples/4 )AS est_tblpages_ff,    tblpages, bs, schemanameFROM (SELECT      (4+ tpl_hdr_size+ tpl_data_size+ (2*ma)- CASE WHEN tpl_hdr_size%ma=0 THEN ma ELSE tpl_hdr_size%ma END- CASE WHEN ceil(tpl_data_size)::int%ma=0 THEN ma ELSE ceil(tpl_data_size)::int%ma END      )AS tpl_size, (heappages+ toastpages)AS tblpages,      reltuples, toasttuples, bs, page_hdr, schemaname, fillfactorFROM (SELECTtbl.oidAS tblid,ns.nspnameAS schemaname,tbl.relnameAS tblname,tbl.reltuples,tbl.relpagesAS heappages, coalesce(toast.relpages,0)AS toastpages,        coalesce(toast.reltuples,0)AS toasttuples,        coalesce(substring(          array_to_string(tbl.reloptions,'')FROM'fillfactor=([0-9]+)')::smallint,100)AS fillfactor,        current_setting('block_size')::numericAS bs,        CASE WHEN version()~'mingw32'OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN8 ELSE4 ENDAS ma,24AS page_hdr,23+ CASE WHENMAX(coalesce(s.null_frac,0))>0 THEN (7+count(s.attname) )/8 ELSE0::int END+ CASE WHEN bool_or(att.attname='oid'andatt.attnum<0) THEN4 ELSE0 ENDAS tpl_hdr_size,sum( (1-coalesce(s.null_frac,0))* coalesce(s.avg_width,0) )AS tpl_data_size,        bool_or(att.atttypid='pg_catalog.name'::regtype)ORsum(CASE WHENatt.attnum>0 THEN1 ELSE0 END)<>count(s.attname)AS is_naFROM pg_attributeAS attJOIN pg_classAS tblONatt.attrelid=tbl.oidJOIN pg_namespaceAS nsONns.oid=tbl.relnamespaceLEFT JOIN pg_statsAS sONs.schemaname=ns.nspnameANDs.tablename=tbl.relnameANDs.inherited=falseANDs.attname=att.attnameLEFT JOIN pg_classAS toastONtbl.reltoastrelid=toast.oidWHERE NOTatt.attisdroppedANDtbl.relkindin ('r','m')GROUP BY1,2,3,4,5,6,7,8,9,10ORDER BY2,3    )AS s  )AS s2)AS s3GROUP BY1;

Index bloat

SELECT current_database(), nspnameAS schemaname, tblname, idxname,  pg_size_pretty(bs*(relpages)::bigint)AS real_size,  CASE WHEN relpages> est_pages_ff    THEN pg_size_pretty((bs*(relpages-est_pages_ff))::bigint)    ELSE'0'  ENDAS bloat_size,100* (relpages-est_pages_ff)::float/ relpagesAS bloat_pctFROM (SELECT      coalesce(1+         ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))),0      )AS est_pages_ff,      bs, nspname, tblname, idxname, relpagesFROM (SELECT bs, nspname, tblname, idxname, reltuples, relpages, fillfactor,            ( index_tuple_hdr_bm+                maxalign- CASE-- Add padding to the index tuple header to align on MAXALIGN                  WHEN index_tuple_hdr_bm%maxalign=0 THEN maxalign                  ELSE index_tuple_hdr_bm%maxalign                END+ nulldatawidth+ maxalign- CASE-- Add padding to the data to align on MAXALIGN                  WHEN nulldatawidth=0 THEN0                  WHEN nulldatawidth::integer%maxalign=0 THEN maxalign                  ELSE nulldatawidth::integer%maxalign                END            )::numericAS nulldatahdrwidth, pagehdr, pageopqdataFROM (SELECTn.nspname,i.tblname,i.idxname,i.reltuples,i.relpages,i.idxoid,i.fillfactor, current_setting('block_size')::numericAS bs,              CASE-- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)                WHEN version() ~'mingw32'OR version() ~'64-bit|x86_64|ppc64|ia64|amd64' THEN8                ELSE4              ENDAS maxalign,/* per page header, fixed size: 20 for 7.X, 24 for others*/24AS pagehdr,/* per page btree opaque data*/16AS pageopqdata,/* per tuple header: add IndexAttributeBitMapData if some cols are null-able*/              CASE WHENmax(coalesce(s.null_frac,0))=0                  THEN8-- IndexTupleData size                  ELSE8+ ((32+8-1 )/8)-- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)              ENDAS index_tuple_hdr_bm,/* data len: we remove null values save space using it fractionnal part from stats*/sum( (1-coalesce(s.null_frac,0))* coalesce(s.avg_width,1024))AS nulldatawidth,max( CASE WHENi.atttypid='pg_catalog.name'::regtype THEN1 ELSE0 END )>0AS is_naFROM (SELECTct.relnameAS tblname,ct.relnamespace,ic.idxname,ic.attpos,ic.indkey,ic.indkey[ic.attpos],ic.reltuples,ic.relpages,ic.tbloid,ic.idxoid,ic.fillfactor,                  coalesce(a1.attnum,a2.attnum)AS attnum, coalesce(a1.attname,a2.attname)AS attname, coalesce(a1.atttypid,a2.atttypid)AS atttypid,                  CASE WHENa1.attnum ISNULL                  THENic.idxname                  ELSEct.relname                  ENDAS attrelnameFROM (SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey,pg_catalog.generate_series(1,indnatts)AS attposFROM (SELECTci.relnameAS idxname,ci.reltuples,ci.relpages,i.indrelidAS tbloid,i.indexrelidAS idxoid,                          coalesce(substring(                              array_to_string(ci.reloptions,'')from'fillfactor=([0-9]+)')::smallint,90)AS fillfactor,i.indnatts,pg_catalog.string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(i.indkey)),'')::int[]AS indkeyFROMpg_catalog.pg_index iJOINpg_catalog.pg_class ciONci.oid=i.indexrelidWHEREci.relam=(SELECToidFROM pg_amWHERE amname='btree')ANDci.relpages>0                  )AS idx_data              )AS icJOINpg_catalog.pg_class ctONct.oid=ic.tbloidLEFT JOINpg_catalog.pg_attribute a1ONic.indkey[ic.attpos]<>0ANDa1.attrelid=ic.tbloidANDa1.attnum=ic.indkey[ic.attpos]LEFT JOINpg_catalog.pg_attribute a2ONic.indkey[ic.attpos]=0ANDa2.attrelid=ic.idxoidANDa2.attnum=ic.attpos            ) iJOINpg_catalog.pg_namespace nONn.oid=i.relnamespaceJOINpg_catalog.pg_stats sONs.schemaname=n.nspnameANDs.tablename=i.attrelnameANDs.attname=i.attnameGROUP BY1,2,3,4,5,6,7,8,9,10,11      )AS rows_data_stats  )AS rows_hdr_pdg_stats)AS relation_statsWHERE (100* (relpages-est_pages_ff)::float/ relpages)>0ORDER BY7desc;

Index bloat (aggregated)

SELECT nspnameAS schemaname,  pg_size_pretty(SUM(bs*(relpages))::bigint)AS real_size,  pg_size_pretty(SUM(CASE WHEN relpages> est_pages_ff    THEN bs*(relpages-est_pages_ff)    ELSE0  END)::bigint)AS bloat_sizeFROM (SELECT      coalesce(1+         ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))),0      )AS est_pages_ff,      bs, nspname, relpagesFROM (SELECT bs, nspname, reltuples, relpages, fillfactor,            ( index_tuple_hdr_bm+                maxalign- CASE                  WHEN index_tuple_hdr_bm%maxalign=0 THEN maxalign                  ELSE index_tuple_hdr_bm%maxalign                END+ nulldatawidth+ maxalign- CASE                  WHEN nulldatawidth=0 THEN0                  WHEN nulldatawidth::integer%maxalign=0 THEN maxalign                  ELSE nulldatawidth::integer%maxalign                END            )::numericAS nulldatahdrwidth, pagehdr, pageopqdataFROM (SELECTn.nspname,i.tblname,i.idxname,i.reltuples,i.relpages,i.idxoid,i.fillfactor, current_setting('block_size')::numericAS bs,              CASE                WHEN version() ~'mingw32'OR version() ~'64-bit|x86_64|ppc64|ia64|amd64' THEN8                ELSE4              ENDAS maxalign,24AS pagehdr,16AS pageopqdata,              CASE WHENmax(coalesce(s.null_frac,0))=0                  THEN8                  ELSE8+ ((32+8-1 )/8)              ENDAS index_tuple_hdr_bm,sum( (1-coalesce(s.null_frac,0))* coalesce(s.avg_width,1024))AS nulldatawidth,max( CASE WHENi.atttypid='pg_catalog.name'::regtype THEN1 ELSE0 END )>0AS is_naFROM (SELECTct.relnameAS tblname,ct.relnamespace,ic.idxname,ic.attpos,ic.indkey,ic.indkey[ic.attpos],ic.reltuples,ic.relpages,ic.tbloid,ic.idxoid,ic.fillfactor,                  coalesce(a1.attnum,a2.attnum)AS attnum, coalesce(a1.attname,a2.attname)AS attname, coalesce(a1.atttypid,a2.atttypid)AS atttypid,                  CASE WHENa1.attnum ISNULL                  THENic.idxname                  ELSEct.relname                  ENDAS attrelnameFROM (SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey,pg_catalog.generate_series(1,indnatts)AS attposFROM (SELECTci.relnameAS idxname,ci.reltuples,ci.relpages,i.indrelidAS tbloid,i.indexrelidAS idxoid,                          coalesce(substring(                              array_to_string(ci.reloptions,'')from'fillfactor=([0-9]+)')::smallint,90)AS fillfactor,i.indnatts,pg_catalog.string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(i.indkey)),'')::int[]AS indkeyFROMpg_catalog.pg_index iJOINpg_catalog.pg_class ciONci.oid=i.indexrelidWHEREci.relam=(SELECToidFROM pg_amWHERE amname='btree')ANDci.relpages>0                  )AS idx_data              )AS icJOINpg_catalog.pg_class ctONct.oid=ic.tbloidLEFT JOINpg_catalog.pg_attribute a1ONic.indkey[ic.attpos]<>0ANDa1.attrelid=ic.tbloidANDa1.attnum=ic.indkey[ic.attpos]LEFT JOINpg_catalog.pg_attribute a2ONic.indkey[ic.attpos]=0ANDa2.attrelid=ic.idxoidANDa2.attnum=ic.attpos            ) iJOINpg_catalog.pg_namespace nONn.oid=i.relnamespaceJOINpg_catalog.pg_stats sONs.schemaname=n.nspnameANDs.tablename=i.attrelnameANDs.attname=i.attnameGROUP BY1,2,3,4,5,6,7,8,9,10,11      )AS rows_data_stats  )AS rows_hdr_pdg_stats)AS relation_statsGROUP BY1;