Skip to content

Instantly share code, notes, and snippets.

@osapon
Created November 8, 2019 04:57
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save osapon/8abd82ac942a0ccdce7e02b0540b15db to your computer and use it in GitHub Desktop.
Save osapon/8abd82ac942a0ccdce7e02b0540b15db to your computer and use it in GitHub Desktop.
PostgreSQL DB Garbage raito
SELECT pg_database_size('notestock') AS db_size -- Any DB Name
, ROUND(100 * SUM(
CASE (a.n_live_tup + a.n_dead_tup) WHEN 0 THEN 0
ELSE c.relpages * (a.n_dead_tup / (a.n_live_tup + a.n_dead_tup)::numeric)
END
) / SUM(c.relpages), 2) AS garbage_raito
FROM pg_class AS c
JOIN pg_stat_all_tables AS a ON(c.oid = a.relid)
WHERE relpages > 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment