Find bloated tables in Postgres

find_bloated_tables.sql

WITH bloat AS (
  SELECT
    ns.nspname AS schema,
    c.relname AS "table",
    pg_relation_size(c.oid)::bigint AS physical_size,
    (8192::bigint * c.relpages) AS expected_size
  FROM
    pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace ns ON ns.oid = c.relnamespace
  WHERE
    c.relkind = 'r'
    AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT
  schema,
  "table",
  pg_size_pretty(physical_size) AS physical_size,
  pg_size_pretty(expected_size) AS expected_size,
  CASE
    WHEN physical_size > 0 THEN ROUND(100 * (physical_size - expected_size)::numeric / physical_size, 2)
    ELSE 0
  END AS bloat_percentage
FROM
  bloat
ORDER BY
  bloat_percentage DESC
LIMIT 20;