How To Get Size Of All Tables And Database On PostgreSQL

dbtut July 20, 2018 PostgreSQL

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;

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To Get Size Of All Tables And Database On PostgreSQL

About dbtut

Leave a Reply Cancel reply