How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL

Summary: This tutorial shows you how to get PostgreSQL database, table, indexes, tablespace, and value size using various handy functions.

PostgreSQL table size

To get the size of a specific table, you use the pg_relation_size() function. For example, you can get the size of the actor table in the dvdrental sample database as follows:

The pg_relation_size() function returns the size of a specific table in bytes:

To make the result more human readable, you use the pg_size_pretty()function. The pg_size_pretty() function takes the result of another function and format it using bytes, kB, MB, GB or TB as appropriate. For example:

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:

The following shows the output:

You can use the pg_total_relation_size() function to find the size of biggest tables including indexes.

For example, the following query returns top 5 biggest tables in the dvdrental database:

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 dvdrental database:

The statement returns the following result:

To get the size of each database in the current database server, you use the following statement:

PostgreSQL indexes size

To get 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 of that table.

For example, to get the total size of all indexes attached to the film table, you use the following statement:

Here is the 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:

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 tutorial, you have learned various handy functions to get the size of a database, a table, indexes, a tablespace, and a value.