Postgres表无dead tuples但存1.7倍膨胀的原因及浪费空间计算问询

一、无dead tuples但表出现膨胀是完全可能的，常见场景如下： #

• 页内碎片化：PostgreSQL的元组（tuple）存储需要符合数据对齐规则，且每个数据页存在固定的页头、行指针开销。当存在大量变长字段更新（比如短文本更新为长文本导致元组迁移）、频繁小批量删除/更新后，vacuum清理完dead tuples后，页内残留的空闲空间可能因为大小不合适，无法容纳新的元组，导致实际占用页数远高于理论需要值，产生无dead tuples的膨胀。
• 自定义fillfactor配置：如果表的fillfactor（填充因子）被设置为低于100，PostgreSQL会在每个数据页预留指定比例的空闲空间供后续更新使用，此时就算没有任何dead tuples，实际占用页数也会高于理论最小值，直接表现为bloat值升高，比如fillfactor设为60的话，理论bloat值就会达到1.7左右，和你遇到的情况完全匹配。
• 已回收空间未被复用：批量删除数据后，vacuum只会将清理出的页标记为可复用，不会主动将空闲空间还给操作系统。如果后续没有足够的新数据写入填满这些空闲页，就会出现实际占用页数高、dead tuples为0的情况。
• 统计信息滞后：n_dead_tup和查询用到的pg_stats统计信息不是实时更新的，如果统计信息过时，也可能出现dead tuples统计为0、但实际计算出的bloat值虚高的情况。

二、查询的浪费字节数计算规则 #

你提供的AWS提供的bloat查询，核心逻辑是先计算存储当前所有存活元组理论需要的最少页数，再用实际占用页数的差值乘以块大小得到浪费空间，具体规则如下：

1. 基础参数获取：首先读取当前实例的块大小bs（默认8KB）、PostgreSQL版本对应的行头开销、系统对齐参数。
2. 平均行空间计算：从pg_stats系统表读取表字段的平均宽度、空值率，综合计算加上行头、对齐padding、空值位图开销后，单行数据的平均占用空间。
3. 理论最小页数计算：用表的总存活元组数量cc.reltuples乘以单行平均空间，除以单页可用空间（块大小减去固定页头开销）后向上取整，得到理论最少需要的页数otta（Optimal Table Size）。
4. 浪费空间计算：如果实际占用页数relpages小于等于理论最小页数otta，浪费空间记为0；否则浪费空间为(实际页数 - 理论最小页数) * 块大小，也就是多占用的页的总存储空间。

-- 你使用的bloat查询语句如下：
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;

内容的提问来源于stack exchange，提问作者Mano