Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
As with most database systems, PostgreSQL offers us various system functions to easily calculate the disk size of the objects. We can get the size of a table using these functions. These functions;
pg_table_size: The size of a table, excluding indexes.
pg_total_relation_size: Total size of a table.
pg_relation_size: The size of an object (table index, etc.) on disk. It is possible to get more detailed information from this function with additional parameters.
pg_size_pretty: Other functions return results in bytes. Converts this into readable format (kb, mb, gb)
Find the Size of a Table
index (primary key) in size_test_table. We get the table size as follows;
1 2 3 4 | SELECT pg_table_size('size_test_table') AS data_size, pg_relation_size('size_test_table_pkey') AS index_size, pg_table_size('size_test_table') + pg_relation_size('size_test_table_pkey') AS total_size1, pg_total_relation_size('size_test_table') AS total_size2; |
Let’s show it better;
1 | SELECT pg_size_pretty(pg_total_relation_size('size_test_table')); |
Find the Size of all Tables in a Database
The indexes of a table in PostgreSQL can be in different tablespace (on different disk if desired).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | WITH tbl_spc AS ( SELECT ts.spcname AS spcname FROM pg_tablespace ts JOIN pg_database db ON db.dattablespace = ts.oid WHERE db.datname = current_database() ) ( SELECT t.schemaname, t.tablename, pg_table_size('"' || t.schemaname || '"."' || t.tablename || '"') AS table_disc_size, NULL as index, 0 as index_disc_size, COALESCE(t.tablespace, ts.spcname) AS tablespace FROM pg_tables t, tbl_spc ts UNION ALL SELECT i.schemaname, i.tablename, 0, i.indexname, pg_relation_size('"' || i.schemaname || '"."' || i.indexname || '"'), COALESCE(i.tablespace, ts.spcname) FROM pg_indexes i, tbl_spc ts ) ORDER BY table_disc_size DESC,index_disc_size DESC; |
You can create a view from the query above and query from view;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | CREATE OR REPLACE VIEW v_disc_spaces AS WITH tbl_spc AS ( SELECT ts.spcname AS spcname FROM pg_tablespace ts JOIN pg_database db ON db.dattablespace = ts.oid WHERE db.datname = current_database() ) ( SELECT t.schemaname, t.tablename, pg_table_size('"' || t.schemaname || '"."' || t.tablename || '"') AS table_disc_size, NULL as index, 0 as index_disc_size, COALESCE(t.tablespace, ts.spcname) AS tablespace FROM pg_tables t, tbl_spc ts UNION ALL SELECT i.schemaname, i.tablename, 0, i.indexname, pg_relation_size('"' || i.schemaname || '"."' || i.indexname || '"'), COALESCE(i.tablespace, ts.spcname) FROM pg_indexes i, tbl_spc ts ); |
All tables in a schema are listed in alphabetical order;
1 2 3 4 5 6 7 | SELECT tablename, pg_size_pretty(sum(table_disc_size)) AS table_disc_size, pg_size_pretty(sum(index_disc_size)) AS index_disc_size FROM v_disc_spaces WHERE schemaname = 'public' GROUP BY tablename ORDER BY tablename; |
Total size of the biggest 10 tables;
1 2 3 4 5 6 7 8 9 | SELECT t.tablename, pg_size_pretty(pg_total_relation_size('"' || t.schemaname || '"."' || t.tablename || '"')) AS table_total_disc_size FROM pg_tables t WHERE t.schemaname = 'public' ORDER BY pg_total_relation_size('"' || t.schemaname || '"."' || t.tablename || '"') DESC LIMIT 10; |
In addition, we could list tables using pg_class instead of pg_tables. Now it’s your creativity.
If you want to list the databases by their size, you should read the following article.
“How to Find The Size of all Databases in PostgreSQL”
