I have a table which has 0 dead tuples, but at the same time the bloat value is 1.7.

The wasted bytes is around 21GB.

Is it possible to have 0 dead tuples but the table being bloated?

If so, on what basis the wasted bytes is calculated?

EDIT: Below is the query I used to get the information. This query was from AWS support.

   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;

CodePudding user response：

A table can be bloated with empty space even if there is not a single dead tuple. The query you show uses heuristics and has been known to get it wrong occasionally.

Use pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('tablename');

That will show you the actual bloat.

CodePudding user response：

Your query considers any space not thought to be used by live tuples to be wasted. So that would include space that is currently occupied by dead tuples, and space that used to be occupied by dead tuples but have since been vacuumed away and is currently available for reuse. It also includes space which is reserved by fillfactor settings, and so is available for updates but not for inserts.