How To Get Size Of All Tables And Database On PostgreSQL

dbtut

6 years ago

In a Cluster you can list all the databases and their sizes withe below script.

I prefer one application db for a cluster generally.

Because postgresql more appropriate for this. I suggest you too the same thing.

SELECT pd.datname AS dbname,
   pg_database_size(pd.datname) AS dbsizebytes,
   pg_size_pretty(pg_database_size(pd.datname))
   AS database_size
FROM pg_database pd
UNION ALL
SELECT 'TOTAL' AS database_name,
   sum(pg_database_size(pd2.datname))
   AS dbsizebytes,
   pg_size_pretty(sum(pg_database_size(pd2.datname)))
   AS database_size
FROM pg_database pd2
ORDER BY dbsizebytes DESC;

SELECT pd.datname AS dbname,

pg_database_size(pd.datname) AS dbsizebytes,

pg_size_pretty(pg_database_size(pd.datname))

AS database_size

FROM pg_database pd

UNION ALL

SELECT 'TOTAL' AS database_name,

sum(pg_database_size(pd2.datname))

AS dbsizebytes,

pg_size_pretty(sum(pg_database_size(pd2.datname)))

AS database_size

FROM pg_database pd2

ORDER BY dbsizebytes DESC;

You can also use the following script to list schema names in a database.

SELECT schema_name
from information_schema.schemata;

1 2	SELECT schema_name from information_schema.schemata;

You can use the following script to list the size of all tables in a schema.

You should write your own schema name at “filter_schemaname_by_writing_your_schemaname_here”.

SELECT sut.relname AS tablename,
   pg_size_pretty(pg_relation_size(psut.relid))
   AS tablesize,
   pg_size_pretty(pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid))
   AS related_objects_size,
   pg_size_pretty(pg_total_relation_size(psut.relid))
   AS totaltablesize,
   sut.n_live_tup AS rowcount
FROM pg_catalog.pg_statio_user_tables AS psut
JOIN pg_stat_user_tables AS sut
USING (relname)
WHERE sut.schemaname = 'filter_schemaname_by_writing_your_schemaname_here'
UNION ALL
SELECT 'TOTAL' AS tablename,
   pg_size_pretty(sum(pg_relation_size(psut.relid)))
   AS tablesize,
   pg_size_pretty(sum(pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid)))
   AS related_objects_size,
   pg_size_pretty(sum(pg_total_relation_size(psut.relid)))
   AS totaltablesize,
   sum(sut.n_live_tup) AS rowcount
FROM pg_catalog.pg_statio_user_tables AS psut
JOIN pg_stat_user_tables AS sut
USING (relname)
WHERE sut.schemaname = 'filter_schemaname_by_writing_your_schemaname_here'
ORDER BY totaltablesize DESC;

SELECT sut.relname AS tablename,

pg_size_pretty(pg_relation_size(psut.relid))

AS tablesize,

pg_size_pretty(pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid))

AS related_objects_size,

pg_size_pretty(pg_total_relation_size(psut.relid))

AS totaltablesize,

sut.n_live_tup AS rowcount

FROM pg_catalog.pg_statio_user_tables AS psut

JOIN pg_stat_user_tables AS sut

USING (relname)

WHERE sut.schemaname = 'filter_schemaname_by_writing_your_schemaname_here'

UNION ALL

SELECT 'TOTAL' AS tablename,

pg_size_pretty(sum(pg_relation_size(psut.relid)))

AS tablesize,

pg_size_pretty(sum(pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid)))

AS related_objects_size,

pg_size_pretty(sum(pg_total_relation_size(psut.relid)))

AS totaltablesize,

sum(sut.n_live_tup) AS rowcount

FROM pg_catalog.pg_statio_user_tables AS psut

JOIN pg_stat_user_tables AS sut

USING (relname)

WHERE sut.schemaname = 'filter_schemaname_by_writing_your_schemaname_here'

ORDER BY totaltablesize DESC;