Get Table,Database, Indexes and Value Size in PostgreSQL
Posted By : Sanjay Saini | 30-Aug-2019
In this blog, I'm showing you how to take PostgreSQL database, table, indexes, tablespace, and value size using various convenient functions.
PostgreSQL table size :
To see the size of a specific table, you use the pg_relation_size() function.
you can see the size of the app_user table in the prosuite sample database as follows:
To obtain the result more individual readable, you use the pg_size_pretty()function. The pg_size_pretty() function gets the result of another function and format it using bytes, kB, MB, GB or TB as appropriate.
SELECT pg_size_pretty (pg_relation_size('app_user'));
The following is the output in kB
The pg_relation_size() function returns the size of the table only, not included indexes or additional objects. To get the total size of a table, you use the pg_total_relation_size() function.
For example, to get the total size of the actor table, you use the following statement:
SELECT pg_size_pretty ( pg_total_relation_size ('app_user') );
The following shows the output:
You can use the pg_total_relation_size() function to find the size of the biggest tables including indexes. For example, the following query returns top 5 biggest tables in the prosuite database:
SELECT relname AS "relation", pg_size_pretty ( pg_total_relation_size (C .oid) ) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace) WHERE nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND C .relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size (C .oid) DESC LIMIT 5;
Here is the output:
PostgreSQL database size :
To get the size of the whole database, you use the pg_database_size() function.
For example, the following statement returns the size of the prosuite database:
SELECT pg_size_pretty ( pg_database_size ('prosuite') );
The statement returns the following result:
To get the size of each database in the current database server, you use the following statement:
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
PostgreSQL indexes size:
To get the total size of all indexes attached to a table, you use the pg_indexes_size() function. The pg_indexes_size() function accepts the OID or table name as the argument and returns the total disk space used by all indexes attached to that table.
For example, to get the total size of all indexes attached to the app_user table, you use the following statement:
SELECT pg_size_pretty (pg_indexes_size('app_user'));
PostgreSQL tablespace size :
To get the size of a tablespace, you use the pg_tablespace_size() function. The pg_tablespace_size() function accepts a tablespace name and returns the size in bytes.
The following statement returns the size of the pg_default tablespace:
SELECT pg_size_pretty ( pg_tablespace_size ('pg_default') );
The statement returns the following output:
PostgreSQL value size:
To find how much space that needs to store a specific value, you use the pg_column_size() function, for examples:
In this blog, you have learned various handy functions to get the size of a database, a table, indexes, a tablespace, and a value.
Sanjay has been working on web application development using frameworks like Java, groovy and grails. He loves listening to music , playing games and going out with friends in free time.