Skip to content

Instantly share code, notes, and snippets.

@eusonlito

eusonlito/postgresql-table-size-rows.sql

Last active February 5, 2024 12:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save eusonlito/87790d4b1d6a8e2a5340ec73ca5e3c9c to your computer and use it in GitHub Desktop.
Save eusonlito/87790d4b1d6a8e2a5340ec73ca5e3c9c to your computer and use it in GitHub Desktop.
Download ZIP
List PostgreSQL database tables by size and rows
Raw
postgresql-table-size-rows.sql
SELECT
"s"."relname" AS "table_name",
pg_total_relation_size("s"."schemaname" || '.' || "s"."relname") / 1024 / 1024 AS "total_size",
pg_relation_size("s"."schemaname" || '.' || "s"."relname") / 1024 / 1024 AS "table_size",
(pg_total_relation_size("s"."schemaname" || '.' || "s"."relname") - pg_relation_size("s"."schemaname" || '.' || "s"."relname")) / 1024 / 1024 AS "index_size",
"s"."n_live_tup" AS "table_rows"
FROM
"pg_stat_user_tables" "s"
JOIN
"pg_class" "c" ON "s"."relid" = "c"."oid"
ORDER BY
pg_total_relation_size("s"."schemaname" || '.' || "s"."relname") DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment