16.PG数据库的表膨胀

1.什么是表膨胀

表膨胀是指表的数据和索引所占文件系统的空间，在有效数据量并未发生大的变化的情况下，不断增大。久而久之，关系文件被大量空洞填满，浪费了大量的磁盘空间。甚至某些特殊场景下，一个表中只有一条简单的数据，但是表对应的物理文件可能已经达到M级甚至G级。那么危害是什么？
 

1).空间持续上涨，到达某一个点后，需要执行一个高额代价的vacuum full(或者cluster等可以重组表的命令)，但vacuum full又是AccessExclusiveLock，8级锁，会阻塞一切访问，意味着在完成清理重组之前，都无法访问该表。
2).扫描的效率变低，即使所有记录都是dead状态，PostgreSQL的顺序扫描也会扫描对象所有的老版本，直到执行vacuum将dead的记录删除
2.为什么会表膨胀

这是因为PostgreSQL的独特MVCC机制。业界主流的MVCC一般有三种实现方式：
1).以Oracle和Innodb为代表的，写新数据时，把旧数据转移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来，所以可以有效避免膨胀。
2).以SQL Server为代表的，把旧版本的数据写入专门的临时表空间，新数据写入日志，然后去更改数据。这种方式，旧版本的数据放入了专门的临时表空间，所以也可以有效地避免膨胀。
3).写新数据时，旧数据不删除，而是把新数据插入，将旧数据标记为无效，PostgreSQL就是使用的这种实现方法，新老数据存放在一起，在被清理之前，会一直占据着空间，所以会导致膨胀。

3.PostgreSQL的MVCC实现方式优缺点如下

优点
1)无论事务进行了多少操作，事务回滚可以立即完成，Oracle中使用了回滚段，如Oracle数据库宕机时如果有很多事务正在运行，这时数据库再启动后，需要把之前的事务做回滚，当没有回滚完成时，数据行上仍然有锁的，这时业务仍然不能正常操作，如果恰好碰到要回滚一些很大的事务，情况会更坏。
2)数据可以进行很多更新，不必像Oracle和MySQL的Innodb引擎那样需要经常保证回滚段不会被用完，也不会像oracle数据库那样经常遇到“ORA-1555”错误的困扰

缺点
1)旧版本的数据需要清理。好在在v8.3中，PostgreSQL引入了自动化的autovacuum，采用多进程架构，支持多表同时操作
2)旧版本的数据可能会导致查询需要扫描的数据块增多，从而导致查询变慢
3)空间持续上涨，存储没有被有效利用

4.MVCC实现机制

前面也介绍了数据膨胀的原因，需要先了解一下数据基于MVCC的存储机制：
1)插入很简单，就是将元组插入到页面的空闲空间中；
2)删除则是将元组标记为旧版本，但是即使这个旧版本对所有事务都不可见了，这个元组占用的空间也不会归还给文件系统
3)UPDATE相当于DELETE + INSERT，等于是占用了两条元组的位置，类似DELETE，旧版本的元组依然占用着物理空间。
4)很明显，在一通增删改操作之后，页面上的旧版本元组势必是占有一定比重的。这就导致了物理文件大小明显高于实际的数据量。

为此，PostgreSQL引入了vacuum的机制，去清理那些不再需要的死元组。前文也介绍过，vacuum分为普通的vacuum和vacuum full。
普通的vacuum会做大概如下几件事情：
1)清除UPDATE或DELETE操作后留下的"死元组"
2)跟踪表块中可用空间，更新free space map
3)更新visibility map，index only scan以及后续vacuum都会利用到
4)"冻结"表中的行，防止事务ID回卷
5)配合ANALYZE，定期更新统计信息

可以看到，普通的vacuum只是清理死元组，"腾出"空间，在今后有新数据插入时，将优先使用该空间，
直到所有被重用的空间用完时，再考虑新增页面。但是每个页面的空闲空间又不是固定大小的，
所以如果要利用这些空间空间，就需要遍历一遍数据页面来找到它们，这样会造成比较大的开销。
因此就设计了用来记录每个页面剩余空间的空闲空间映射表FSM(Free Space Mapping)，
以便高效的将空闲空间管理起来，方便查找和重新使用。FSM在第一次vacuum之后会出现，
可以使用pg_freespacemap扩展查看。FSM的结构类似于一个Btree，
感兴趣的可以参阅：PgSQL · 原理介绍 · PostgreSQL中的空闲空间管理

极端情况下，就会发生每个页面的"填充率"特别低，如下每个页面总计三个元组，有效利用率特别低。
因此，需要引入vacuum full，vacuum full会对表进行重组，也就意味着表的oid会变，
所以不能我们在日常操作中，要定位表的oid的时候，不能通过pg_class的oid来找，
得通过pg_class的relfilenode来找，这样才精确。而且，vacuum full最大会占据原来磁盘空间的两倍，所以请预留好磁盘空间。
说到空间，这里说个小技巧，可以提前touch或者dd一个大文件，比如5GB，放在那里，
关键时刻删除它以释放空间，说不定能救你一命。

5.表膨胀常见原因

1.IO能力差 
2.未开启autovacuum
3.autovacuum触发较迟
4.存在长事务 
5.存在失效的事务槽 
6.设置事务隔离级别:repeatable read 或 serializable 的隔离级别.

6.获取表膨胀的SQL 

获取膨胀率

--获取膨胀率
----SQL I
SELECT
    CURRENT_CATALOG AS datname,
    nspname,
    relname,
    bs * tblpages AS size,
    CASE WHEN tblpages - est_tblpages_ff > 0 THEN
    (tblpages - est_tblpages_ff) / tblpages::float
ELSE
    0
    END AS ratio
FROM (
    SELECT
        ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff,
        tblpages,
        fillfactor,
        bs,
        tblid,
        nspname,
        relname,
        is_na
    FROM (
        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,
            heappages,
            toastpages,
            reltuples,
            toasttuples,
            bs,
            page_hdr,
            tblid,
            nspname,
            relname,
            fillfactor,
            is_na
        FROM (
            SELECT
                tbl.oid AS tblid,
                ns.nspname,
                tbl.relname,
                tbl.reltuples,
                tbl.relpages AS 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')::numeric AS bs,
                CASE WHEN version() ~ 'mingw32'
                    OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN
                    8
                ELSE
                    4
                END AS ma,
                24 AS page_hdr,
                23 + CASE WHEN MAX(coalesce(s.null_frac, 0)) > 0 THEN
                (7 + count(s.attname)) / 8
            ELSE
                0::int
                END + CASE WHEN bool_or(att.attname = 'oid'
                    AND att.attnum < 0) THEN
                    4
                ELSE
                    0
                END AS 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)
                OR sum(
                    CASE WHEN att.attnum > 0 THEN
                        1
                    ELSE
                        0
                    END) <> count(s.attname) AS is_na
            FROM
                pg_attribute AS att
                JOIN pg_class AS tbl ON att.attrelid = tbl.oid
                JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
                LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname
                    AND s.tablename = tbl.relname
                    AND s.inherited = FALSE
                    AND s.attname = att.attname
            LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
        WHERE
            NOT att.attisdropped
            AND tbl.relkind = 'r'
            AND nspname NOT IN ('pg_catalog', 'information_schema')
        GROUP BY
            1,
            2,
            3,
            4,
            5,
            6,
            7,
            8,
            9,
            10) AS s) AS s2) AS s3
WHERE
    NOT is_na;
	
	
	
-------------------------------------SQL II
-- new table bloat query
-- still needs work; is often off by +/- 20%
WITH constants AS (
    -- define some constants for sizes of things
    -- for reference down the query and easy maintenance
    SELECT
        current_setting('block_size')::numeric AS bs,
        23 AS hdr,
        8 AS ma
),
no_stats AS (
    -- screen out table who have attributes
    -- which dont have stats, such as JSON
    SELECT
        table_schema,
        table_name,
        n_live_tup::numeric AS est_rows,
        pg_table_size(relid)::numeric AS table_size
    FROM
        information_schema.columns
        JOIN pg_stat_user_tables AS psut ON table_schema = psut.schemaname
            AND table_name = psut.relname
        LEFT OUTER JOIN pg_stats ON table_schema = pg_stats.schemaname
        AND table_name = pg_stats.tablename
        AND column_name = attname
    WHERE
        attname IS NULL
        AND table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY
        table_schema,
        table_name,
        relid,
        n_live_tup
),
null_headers AS (
    -- calculate null header sizes
    -- omitting tables which dont have complete stats
    -- and attributes which aren't visible
    SELECT
        hdr + 1 + (sum(
                CASE WHEN null_frac <> 0 THEN
                    1
                ELSE
                    0
                END) / 8) AS nullhdr,
        SUM((1 - null_frac) * avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        schemaname,
        tablename,
        hdr,
        ma,
        bs
    FROM
        pg_stats
        CROSS JOIN constants
        LEFT OUTER JOIN no_stats ON schemaname = no_stats.table_schema
            AND tablename = no_stats.table_name
    WHERE
        schemaname NOT IN ('pg_catalog', 'information_schema')
        AND no_stats.table_name IS NULL
        AND EXISTS (
            SELECT
                1
            FROM
                information_schema.columns
            WHERE
                schemaname = columns.table_schema
                AND tablename = columns.table_name)
        GROUP BY
            schemaname,
            tablename,
            hdr,
            ma,
            bs
),
data_headers AS (
    -- estimate header and row size
    SELECT
        ma,
        bs,
        hdr,
        schemaname,
        tablename,
        (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
        null_headers
),
table_estimates AS (
    -- make estimates of how large the table should be
    -- based on row and page size
    SELECT
        schemaname,
        tablename,
        bs,
        reltuples::numeric AS est_rows,
        relpages * bs AS table_bytes,
        CEIL((reltuples * (datahdr + nullhdr2 + 4 + ma - (
                    CASE WHEN datahdr % ma = 0 THEN
                        ma
                    ELSE
                        datahdr % ma
                    END)) / (bs - 20))) * bs AS expected_bytes,
        reltoastrelid
    FROM
        data_headers
        JOIN pg_class ON tablename = relname
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
            AND schemaname = nspname
    WHERE
        pg_class.relkind = 'r'
),
estimates_with_toast AS (
    -- add in estimated TOAST table sizes
    -- estimate based on 4 toast tuples per page because we dont have
    -- anything better.  also append the no_data tables
    SELECT
        schemaname,
        tablename,
        TRUE AS can_estimate,
        est_rows,
        table_bytes + (coalesce(toast.relpages, 0) * bs) AS table_bytes,
        expected_bytes + (ceil(coalesce(toast.reltuples, 0) / 4) * bs) AS expected_bytes
    FROM
        table_estimates
        LEFT OUTER JOIN pg_class AS toast ON table_estimates.reltoastrelid = toast.oid
        AND toast.relkind = 't'
),
table_estimates_plus AS (
    -- add some extra metadata to the table data
    -- and calculations to be reused
    -- including whether we cant estimate it
    -- or whether we think it might be compressed
    SELECT
        current_database() AS databasename,
        schemaname,
        tablename,
        can_estimate,
        est_rows,
        CASE WHEN table_bytes > 0 THEN
            table_bytes::numeric
        ELSE
            NULL::numeric
        END AS table_bytes,
        CASE WHEN expected_bytes > 0 THEN
            expected_bytes::numeric
        ELSE
            NULL::numeric
        END AS expected_bytes,
        CASE WHEN expected_bytes > 0
            AND table_bytes > 0
            AND expected_bytes <= table_bytes THEN
            (table_bytes - expected_bytes)::numeric
        ELSE
            0::numeric
        END AS bloat_bytes
    FROM
        estimates_with_toast
    UNION ALL
    SELECT
        current_database() AS databasename,
        table_schema,
        table_name,
        FALSE,
        est_rows,
        table_size,
        NULL::numeric,
        NULL::numeric
    FROM
        no_stats
),
bloat_data AS (
    -- do final math calculations and formatting
    SELECT
        current_database() AS databasename,
        schemaname,
        tablename,
        can_estimate,
        table_bytes,
        round(table_bytes / (1024 ^ 2)::numeric, 3) AS table_mb,
        expected_bytes,
        round(expected_bytes / (1024 ^ 2)::numeric, 3) AS expected_mb,
        round(bloat_bytes * 100 / table_bytes) AS pct_bloat,
        round(bloat_bytes / (1024::numeric ^ 2), 2) AS mb_bloat,
        table_bytes,
        expected_bytes,
        est_rows
    FROM
        table_estimates_plus)
        -- filter output for bloated tables
        SELECT
            databasename,
            schemaname,
            tablename,
            can_estimate,
            est_rows,
            pct_bloat,
            mb_bloat,
            table_mb
        FROM
            bloat_data
            -- this where clause defines which tables actually appear
            -- in the bloat chart
            -- example below filters for tables which are either 50%
            -- bloated and more than 20mb in size, or more than 25%
            -- bloated and more than 4GB in size
    WHERE (pct_bloat >= 50
        AND mb_bloat >= 10)
    OR (pct_bloat >= 25
        AND mb_bloat >= 1000)
ORDER BY
    pct_bloat DESC;

（2）获取膨胀情况

--获取膨胀情况
--SQL I
SELECT
    current_database(),
    schemaname,
    tablename,
    /*reltuples::bigint, relpages::bigint, otta,*/
    ROUND((
        CASE WHEN otta = 0 THEN
            0.0
        ELSE
            sml.relpages::float / otta
        END)::numeric, 1) AS tbloat,
    CASE WHEN relpages < otta THEN
        0
    ELSE
        bs * (sml.relpages - otta)::bigint
    END AS wastedbytes,
    iname,
    /*ituples::bigint, ipages::bigint, iotta,*/
    ROUND((
        CASE WHEN iotta = 0
            OR ipages = 0 THEN
            0.0
        ELSE
            ipages::float / iotta
        END)::numeric, 1) AS ibloat,
    CASE WHEN ipages < iotta THEN
        0
    ELSE
        bs * (ipages - iotta)
    END AS wastedibytes
FROM (
    SELECT
        schemaname,
        tablename,
        cc.reltuples,
        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 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 (
        SELECT
            ma,
            bs,
            schemaname,
            tablename,
            (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
                schemaname,
                tablename,
                hdr,
                ma,
                bs,
                SUM((1 - null_frac) * avg_width) AS datawidth,
                MAX(null_frac) AS maxfracsum,
                hdr + (
                    SELECT
                        1 + count(*) / 8
                    FROM
                        pg_stats s2
                    WHERE
                        null_frac <> 0
                        AND s2.schemaname = s.schemaname
                        AND s2.tablename = s.tablename) AS nullhdr
                FROM
                    pg_stats s,
                    (
                        SELECT
                            (
                                SELECT
                                    current_setting('block_size')::numeric) AS bs,
                                CASE WHEN substring(v, 12, 3) IN ('8.0', '8.1', '8.2') THEN
                                    27
                                ELSE
                                    23
                                END AS hdr,
                                CASE WHEN v ~ 'mingw32' THEN
                                    8
                                ELSE
                                    4
                                END AS ma
                            FROM (
                                SELECT
                                    version() AS v) AS foo) AS constants
                        GROUP BY
                            1,
                            2,
                            3,
                            4,
                            5) AS foo) AS rs
                    JOIN pg_class cc ON cc.relname = rs.tablename
                    JOIN pg_namespace nn ON cc.relnamespace = nn.oid
                        AND nn.nspname = rs.schemaname
                        AND nn.nspname <> 'information_schema'
                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;

（3）获取索引膨胀:管理员用户

--获取索引膨胀
--SQL I
-- btree index stats query
-- estimates bloat for btree indexes
WITH btree_index_atts AS (
    SELECT
        nspname,
        indexclass.relname AS index_name,
        indexclass.reltuples,
        indexclass.relpages,
        indrelid,
        indexrelid,
        indexclass.relam,
        tableclass.relname AS tablename,
        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
        indexrelid AS index_oid
    FROM
        pg_index
        JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid
        JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid
        JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace
        JOIN pg_am ON indexclass.relam = pg_am.oid
    WHERE
        pg_am.amname = 'btree'
        AND indexclass.relpages > 0
        AND nspname NOT IN ('pg_catalog', 'information_schema')
),
index_item_sizes AS (
    SELECT
        ind_atts.nspname,
        ind_atts.index_name,
        ind_atts.reltuples,
        ind_atts.relpages,
        ind_atts.relam,
        indrelid AS table_oid,
        index_oid,
        current_setting('block_size')::numeric AS bs,
        8 AS maxalign,
        24 AS pagehdr,
        CASE WHEN max(coalesce(pg_stats.null_frac, 0)) = 0 THEN
            2
        ELSE
            6
        END AS index_tuple_hdr,
        sum((1 - coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024)) AS nulldatawidth
    FROM
        pg_attribute
        JOIN btree_index_atts AS ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid
            AND pg_attribute.attnum = ind_atts.attnum
        JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
        -- stats for regular index columns
            AND ((pg_stats.tablename = ind_atts.tablename
                    AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
                -- stats for functional indexes
                OR (pg_stats.tablename = ind_atts.index_name
                    AND pg_stats.attname = pg_attribute.attname))
    WHERE
        pg_attribute.attnum > 0
    GROUP BY
        1,
        2,
        3,
        4,
        5,
        6,
        7,
        8,
        9
),
index_aligned_est AS (
    SELECT
        maxalign,
        bs,
        nspname,
        index_name,
        reltuples,
        relpages,
        relam,
        table_oid,
        index_oid,
        coalesce(ceil(reltuples * (6 + maxalign - CASE WHEN index_tuple_hdr % maxalign = 0 THEN
                        maxalign
                    ELSE
                        index_tuple_hdr % maxalign
                    END + nulldatawidth + maxalign - CASE /* Add padding to the data to align on MAXALIGN */
                    WHEN nulldatawidth::integer % maxalign = 0 THEN
                        maxalign
                    ELSE
                        nulldatawidth::integer % maxalign
                    END)::numeric / (bs - pagehdr::numeric) + 1), 0) AS expected
    FROM
        index_item_sizes
),
raw_bloat AS (
    SELECT
        current_database() AS dbname,
        nspname,
        pg_class.relname AS table_name,
        index_name,
        bs * (index_aligned_est.relpages)::bigint AS totalbytes,
        expected,
        CASE WHEN index_aligned_est.relpages <= expected THEN
            0
        ELSE
            bs * (index_aligned_est.relpages - expected)::bigint
        END AS wastedbytes,
        CASE WHEN index_aligned_est.relpages <= expected THEN
            0
        ELSE
            bs * (index_aligned_est.relpages - expected)::bigint * 100 / (bs * (index_aligned_est.relpages)::bigint)
        END AS realbloat,
        pg_relation_size(index_aligned_est.table_oid) AS table_bytes,
        stat.idx_scan AS index_scans
    FROM
        index_aligned_est
        JOIN pg_class ON pg_class.oid = index_aligned_est.table_oid
        JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid
),
format_bloat AS (
    SELECT
        dbname AS database_name,
        nspname AS schema_name,
        table_name,
        index_name,
        round(realbloat) AS bloat_pct,
        round(wastedbytes / (1024 ^ 2)::numeric) AS bloat_mb,
        round(totalbytes / (1024 ^ 2)::numeric, 3) AS index_mb,
        round(table_bytes / (1024 ^ 2)::numeric, 3) AS table_mb,
        index_scans
    FROM
        raw_bloat)
    -- final query outputting the bloated indexes
    -- change the where and order by to change
    -- what shows up as bloated
    SELECT
        *
    FROM
        format_bloat
    WHERE (bloat_pct > 50
        AND bloat_mb > 10)
ORDER BY
    bloat_mb DESC;

（4）普通用户获取索引膨胀率

--SQL II
--普通用户索引膨胀率查询
-- WARNING: executed with a non-superuser role, the query inspect only index on tables you are granted to read.
-- WARNING: rows with is_na = 't' are known to have bad statistics ("name" type is not supported).
-- This query is compatible with PostgreSQL 8.2 and after
SELECT
    current_database(),
    nspname AS schemaname,
    tblname,
    idxname,
    bs * (relpages)::bigint AS real_size,
    bs * (relpages - est_pages)::bigint AS extra_size,
    100 * (relpages - est_pages)::float / relpages AS extra_pct,
    fillfactor,
    CASE WHEN relpages > est_pages_ff THEN
        bs * (relpages - est_pages_ff)
    ELSE
        0
    END AS bloat_size,
    100 * (relpages - est_pages_ff)::float / relpages AS bloat_pct,
    is_na
    -- , 100-(pst).avg_leaf_density AS pst_avg_bloat, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples, relpages -- (DEBUG INFO)
FROM (
    SELECT
        coalesce(1 + ceil(reltuples / floor((bs - pageopqdata - pagehdr) / (4 + nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
) AS est_pages,
        coalesce(1 + ceil(reltuples / floor((bs - pageopqdata - pagehdr) * fillfactor / (100 * (4 + nulldatahdrwidth)::float))), 0) AS est_pages_ff,
        bs,
        nspname,
        tblname,
        idxname,
        relpages,
        fillfactor,
        is_na
        -- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
    FROM (
        SELECT
            maxalign,
            bs,
            nspname,
            tblname,
            idxname,
            reltuples,
            relpages,
            idxoid,
            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 THEN
                    0
                WHEN nulldatawidth::integer % maxalign = 0 THEN
                    maxalign
                ELSE
                    nulldatawidth::integer % maxalign
                END)::numeric AS nulldatahdrwidth,
            pagehdr,
            pageopqdata,
            is_na
            -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
        FROM (
            SELECT
                n.nspname,
                i.tblname,
                i.idxname,
                i.reltuples,
                i.relpages,
                i.idxoid,
                i.fillfactor,
                current_setting('block_size')::numeric AS bs,
                CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
                WHEN version() ~ 'mingw32'
                    OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN
                    8
                ELSE
                    4
                END AS maxalign,
                /* per page header, fixed size: 20 for 7.X, 24 for others */
                24 AS pagehdr,
                /* per page btree opaque data */
                16 AS pageopqdata,
                /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
                CASE WHEN max(coalesce(s.null_frac, 0)) = 0 THEN
                    2 -- IndexTupleData size
                ELSE
                    2 + ((32 + 8 - 1) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
                END AS 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 WHEN i.atttypid = 'pg_catalog.name'::regtype THEN
                        1
                    ELSE
                        0
                    END) > 0 AS is_na
            FROM (
                SELECT
                    ct.relname AS 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 WHEN a1.attnum IS NULL THEN
                        ic.idxname
                    ELSE
                        ct.relname
                    END AS attrelname
                FROM (
                    SELECT
                        idxname,
                        reltuples,
                        relpages,
                        tbloid,
                        idxoid,
                        fillfactor,
                        indkey,
                        pg_catalog.generate_series(1, indnatts) AS attpos
                    FROM (
                        SELECT
                            ci.relname AS idxname,
                            ci.reltuples,
                            ci.relpages,
                            i.indrelid AS tbloid,
                            i.indexrelid AS 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 indkey
                        FROM
                            pg_catalog.pg_index i
                            JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid
                        WHERE
                            ci.relam = (
                                SELECT
                                    oid
                                FROM
                                    pg_am
                                WHERE
                                    amname = 'btree')
                                AND ci.relpages > 0) AS idx_data) AS ic
                        JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid
                        LEFT JOIN pg_catalog.pg_attribute a1 ON ic.indkey[ic.attpos] <> 0
                            AND a1.attrelid = ic.tbloid
                            AND a1.attnum = ic.indkey[ic.attpos]
                    LEFT JOIN pg_catalog.pg_attribute a2 ON ic.indkey[ic.attpos] = 0
                        AND a2.attrelid = ic.idxoid
                        AND a2.attnum = ic.attpos) i
                JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace
                JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname
                    AND s.tablename = i.attrelname
                    AND s.attname = i.attname
            GROUP BY
                1,
                2,
                3,
                4,
                5,
                6,
                7,
                8,
                9,
                10,
                11) AS rows_data_stats) AS rows_hdr_pdg_stats) AS relation_stats
ORDER BY
    nspname,
    tblname,
    idxname;

7.如何消除表膨胀

vacuum full database_db1; 

消除表的情况比较复杂。最主要是找到导致表膨胀的原因，这样才能更快的找到消除表膨胀的方法。