Skip to content

Instantly share code, notes, and snippets.

Created April 29, 2019 23:35
Show Gist options
  • Save wrossmann/1915c2a01097e64e6277a4b7e4b57a65 to your computer and use it in GitHub Desktop.
Save wrossmann/1915c2a01097e64e6277a4b7e4b57a65 to your computer and use it in GitHub Desktop.

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.


  • 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.
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"
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(),
tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND((CASE WHEN otta = 0 THEN 0.0 ELSE sml.relpages::FLOAT / otta END)::NUMERIC, 1) AS tbloat,
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
SELECT schemaname,
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
(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
SELECT schemaname,
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,
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment