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”
