bloat_tables

SELECT bdw.schemaname,
    bdw.relname,
    bdw.datawidth,
    cc.reltuples::bigint AS reltuples,
    cc.relpages::bigint AS relpages,
    ceil(cc.reltuples * bdw.datawidth / current_setting('block_size'::text)::numeric::double precision)::bigint AS expectedpages,
    100::double precision - cc.reltuples * 100::double precision * bdw.datawidth / (current_setting('block_size'::text)::numeric * cc.relpages::numeric)::double precision AS bloatpct
   FROM ( SELECT ns.nspname AS schemaname,
            tbl.oid AS relid,
            tbl.relname,
                CASE
                    WHEN every(s.avg_width IS NOT NULL) THEN sum((1::double precision - s.null_frac) * s.avg_width::double precision) + max(s.null_frac) * 24::double precision
                    ELSE NULL::double precision
                END AS datawidth
           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
          WHERE att.attnum > 0 AND tbl.relkind = 'r'::"char"
          GROUP BY ns.nspname, tbl.oid, tbl.relname) bdw
     JOIN pg_class cc ON cc.oid = bdw.relid AND cc.relpages > 1 AND bdw.datawidth IS NOT NULL;

btree_index_atts

WITH btree_index_atts AS (
         SELECT pg_namespace.nspname,
            pg_class.relname,
            pg_class.reltuples,
            pg_class.relpages,
            pg_index.indrelid,
            pg_class.relam,
            regexp_split_to_table(pg_index.indkey::text, ' '::text)::smallint AS attnum,
            pg_index.indexrelid AS index_oid
           FROM pg_index
             JOIN pg_class ON pg_class.oid = pg_index.indexrelid
             JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
             JOIN pg_am ON pg_class.relam = pg_am.oid
          WHERE pg_am.amname = 'btree'::name
        ), index_item_sizes AS (
         SELECT i.nspname,
            i.relname,
            i.reltuples,
            i.relpages,
            i.relam,
            s.starelid,
            a.attrelid AS table_oid,
            i.index_oid,
            current_setting('block_size'::text)::numeric AS bs,
                CASE
                    WHEN version() ~ 'mingw32'::text OR version() ~ '64-bit'::text THEN 8
                    ELSE 4
                END AS maxalign,
            24 AS pagehdr,
                CASE
                    WHEN max(COALESCE(s.stanullfrac, 0::real)) = 0::double precision THEN 2
                    ELSE 6
                END AS index_tuple_hdr,
            sum((1::double precision - COALESCE(s.stanullfrac, 0::real)) * COALESCE(s.stawidth, 2048)::double precision) AS nulldatawidth
           FROM pg_attribute a
             JOIN pg_statistic s ON s.starelid = a.attrelid AND s.staattnum = a.attnum
             JOIN btree_index_atts i ON i.indrelid = a.attrelid AND a.attnum = i.attnum
          WHERE a.attnum > 0
          GROUP BY i.nspname, i.relname, i.reltuples, i.relpages, i.relam, s.starelid, a.attrelid, i.index_oid, current_setting('block_size'::text)::numeric
        ), index_aligned AS (
         SELECT s1.maxalign,
            s1.bs,
            s1.nspname,
            s1.relname AS index_name,
            s1.reltuples,
            s1.relpages,
            s1.relam,
            s1.table_oid,
            s1.index_oid,
            ((2 + s1.maxalign -
                CASE
                    WHEN (s1.index_tuple_hdr % s1.maxalign) = 0 THEN s1.maxalign
                    ELSE s1.index_tuple_hdr % s1.maxalign
                END)::double precision + s1.nulldatawidth + s1.maxalign::double precision -
                CASE
                    WHEN (s1.nulldatawidth::integer % s1.maxalign) = 0 THEN s1.maxalign
                    ELSE s1.nulldatawidth::integer % s1.maxalign
                END::double precision)::numeric AS nulldatahdrwidth,
            s1.pagehdr
           FROM index_item_sizes s1
        ), otta_calc AS (
         SELECT s2.bs,
            s2.nspname,
            s2.table_oid,
            s2.index_oid,
            s2.index_name,
            s2.relpages,
            COALESCE(ceil(s2.reltuples * (4::numeric + s2.nulldatahdrwidth)::double precision / (s2.bs::double precision - s2.pagehdr::double precision)) +
                CASE
                    WHEN am.amname = ANY (ARRAY['hash'::name, 'btree'::name]) THEN 1
                    ELSE 0
                END::double precision, 0::double precision) AS otta
           FROM index_aligned s2
             LEFT JOIN pg_am am ON s2.relam = am.oid
        ), raw_bloat AS (
         SELECT current_database() AS dbname,
            sub.nspname,
            c.relname AS table_name,
            sub.index_name,
            sub.bs * sub.relpages::bigint::numeric AS totalbytes,
                CASE
                    WHEN sub.relpages::double precision <= sub.otta THEN 0::numeric
                    ELSE sub.bs * (sub.relpages::double precision - sub.otta)::bigint::numeric
                END AS wastedbytes,
                CASE
                    WHEN sub.relpages::double precision <= sub.otta THEN 0::numeric
                    ELSE sub.bs * (sub.relpages::double precision - sub.otta)::bigint::numeric * 100::numeric / (sub.bs * sub.relpages::bigint::numeric)
                END AS realbloat,
            pg_relation_size(sub.table_oid::regclass) AS table_bytes,
            stat.idx_scan AS index_scans
           FROM otta_calc sub
             JOIN pg_class c ON c.oid = sub.table_oid
             JOIN pg_stat_user_indexes stat ON sub.index_oid = stat.indexrelid
        )
 SELECT raw_bloat.dbname AS database_name,
    raw_bloat.nspname AS schema_name,
    raw_bloat.table_name,
    raw_bloat.index_name,
    round(raw_bloat.realbloat, 1) AS bloat_pct,
    raw_bloat.wastedbytes AS bloat_bytes,
    pg_size_pretty(raw_bloat.wastedbytes::bigint) AS bloat_size,
    raw_bloat.totalbytes AS index_bytes,
    pg_size_pretty(raw_bloat.totalbytes::bigint) AS index_size,
    raw_bloat.table_bytes,
    pg_size_pretty(raw_bloat.table_bytes) AS table_size,
    raw_bloat.index_scans
   FROM raw_bloat
  WHERE raw_bloat.realbloat > 50::numeric AND raw_bloat.wastedbytes > 50000000::numeric
  ORDER BY raw_bloat.wastedbytes DESC;

dead_tuples

 SELECT av.relation,
    av.n_tup_ins,
    av.n_tup_upd,
    av.n_tup_del,
    av.hot_update_ratio,
    av.n_live_tup,
    av.n_dead_tup,
    av.reltuples,
    av.av_threshold,
    av.last_vacuum,
    av.last_analyze,
    av.n_dead_tup::double precision > av.av_threshold AS av_needed,
        CASE
            WHEN av.reltuples > 0::double precision THEN round((100.0 * av.n_dead_tup::numeric)::double precision / av.reltuples)
            ELSE 0::double precision
        END AS pct_dead
   FROM ( SELECT (n.nspname::text || '.'::text) || c.relname::text AS relation,
            pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
            pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
            pg_stat_get_tuples_deleted(c.oid) AS n_tup_del,
                CASE
                    WHEN pg_stat_get_tuples_updated(c.oid) > 0 THEN pg_stat_get_tuples_hot_updated(c.oid)::real / pg_stat_get_tuples_updated(c.oid)::double precision
                    ELSE 0::double precision
                END AS hot_update_ratio,
            pg_stat_get_live_tuples(c.oid) AS n_live_tup,
            pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
            c.reltuples,
            round(current_setting('autovacuum_vacuum_threshold'::text)::integer::double precision + current_setting('autovacuum_vacuum_scale_factor'::text)::numeric::double precision * c.reltuples) AS av_threshold,
            date_trunc('minute'::text, GREATEST(pg_stat_get_last_vacuum_time(c.oid), pg_stat_get_last_autovacuum_time(c.oid))) AS last_vacuum,
            date_trunc('minute'::text, GREATEST(pg_stat_get_last_analyze_time(c.oid), pg_stat_get_last_analyze_time(c.oid))) AS last_analyze
           FROM pg_class c
             LEFT JOIN pg_index i ON c.oid = i.indrelid
             LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND n.nspname !~ '^pg_toast'::text) av
  ORDER BY av.n_dead_tup::double precision > av.av_threshold DESC, av.n_dead_tup DESC;