README.md

The Problem

Your postgres server is out of space to the point that VACUUM FULL hits a table too large to write back out to disk even though other tables may still have space to give up.

The Solution

Start with the tables that have the least amount of "un-wasted" space as they will require the minimal amount of free space to process, and hopefully the cumulative effect of releasing all of the space from the smaller tables allows you to process your larger tables as you go. If not, then there's no solution available to you short of deleting data or expading your disk.

The Kludge

Unforutnately postgres doesn't have a mechanism to show waste/bloat inside its datafiles, but there are ways to apporoximate it.

This abominable kludge is the result of smushing this query from check_postgres to show "wasted space" together with this query to show table sizes.

Caveats

• This only produces a result set, you need to turn it into a series of VACUUM FULL schema.table statements.
• For sanity's sake you probably want to set the wastedbytes > 0 to a more reasonable threshold to avoid needlessly VACUUM FULL-ing a tables that won't release meaningful amounts of space.
• You can probably do math on the cumulative values of the wasted column, plus the starting free space on your partition, compared with the unwasted column to pre-check if running this script is a viable solution for you at all without actually having to run it.

psql_unbloat.sql

SELECT DISTINCT
    nspname, relname,
    pg_size_pretty(total_size) "size",
    pg_size_pretty(wastedbytes) "wasted",
    total_size - wastedbytes "unwasted_bytes",
    pg_size_pretty(total_size - wastedbytes) "unwasted"
FROM
    (
      SELECT nspname, relname,
        pg_total_relation_size(C.oid) AS "total_size"
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE nspname NOT IN ('pg_catalog', 'information_schema')
        AND C.relkind <> 'i'
        AND nspname !~ '^pg_toast'
    ) size INNER JOIN (
         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
     ) as waste
    ON size.nspname = waste.schemaname AND size.relname = waste.tablename
    WHERE wastedbytes > 0
ORDER BY unwasted_bytes ASC