Get Table,Database, Indexes and Value Size in PostgreSQL

Posted By : Sanjay Saini | 30-Aug-2019

Hello Guys,

 

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.

For example,

you can see the size of the app_user table in the prosuite sample database as follows:

 select pg_relation_size('app_user');

Output : 

 

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.

For example:

 
 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;

Output :

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'));
	

Output :

 

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.

 

Thanks

 

 

About Author

Author Image
Sanjay Saini

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.

Request for Proposal

Name is required

Comment is required

Sending message..