Skip to content

Instantly share code, notes, and snippets.

@ZsBT

ZsBT/db-size.pg.sql

Last active June 9, 2022 00:27
Show Gist options
  • Save ZsBT/769e4ccc9de77e6e1dd3a4293354f7f4 to your computer and use it in GitHub Desktop.
Save ZsBT/769e4ccc9de77e6e1dd3a4293354f7f4 to your computer and use it in GitHub Desktop.
Download ZIP
show occupied space in a postgresql database
Raw
db-size.pg.sql
create view v_sizeof_dbs as
select datname dbname
,pg_size_pretty(pg_database_size(datname)) hsize
from pg_database
order by pg_database_size(datname) desc;
create view v_sizeof_tables as
SELECT table_catalog,table_schema,table_name
,pg_size_pretty(pg_table_size(table_schema||'.'||table_name)) table_hsize
FROM information_schema.tables
where not table_schema in ('pg_catalog','information_schema')
and table_type like '%TABLE%'
ORDER BY (pg_table_size(table_schema||'.'||table_name)) desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment