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

Summary: in this tutorial, you will learn how to get the size of the databases, tables, indexes, tablespace using some 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:

select pg_relation_size('actor');
Code language: SQL (Structured Query Language) (sql)

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

pg_relation_size
------------------
            16384
Code language: SQL (Structured Query Language) (sql)

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:

SELECT
    pg_size_pretty (pg_relation_size('actor'));
Code language: SQL (Structured Query Language) (sql)

The following is the output in kB

 pg_size_pretty
    ----------------
     16 kB
    (1 row)
Code language: SQL (Structured Query Language) (sql)

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 ('actor')
    );
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

 pg_size_pretty
----------------
 72 kB
(1 row)
Code language: SQL (Structured Query Language) (sql)

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:

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;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

  relation  | total_size
------------+------------
 rental     | 2472 kB
 payment    | 2232 kB
 film       | 688 kB
 film_actor | 536 kB
 inventory  | 464 kB
(5 rows)
Code language: SQL (Structured Query Language) (sql)

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:

SELECT
    pg_size_pretty (
        pg_database_size ('dvdrental')
    );
Code language: SQL (Structured Query Language) (sql)

The statement returns the following result:

pg_size_pretty
----------------
 15 MB
(1 row)
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

    datname     |  size
----------------+---------
 postgres       | 7055 kB
 template1      | 7055 kB
 template0      | 6945 kB
 dvdrental      | 15 MB

PostgreSQL index 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:

SELECT
    pg_size_pretty (pg_indexes_size('actor'));
Code language: SQL (Structured Query Language) (sql)

Here is the output:

 pg_size_pretty
----------------
 32 kB
(1 row)
Code language: SQL (Structured Query Language) (sql)

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')
    );
Code language: SQL (Structured Query Language) (sql)

The statement returns the following output:

 pg_size_pretty
----------------
 43 MB
(1 row)
Code language: SQL (Structured Query Language) (sql)

PostgreSQL value size

To find how much space that needs to store a specific value, you use the pg_column_size() function, for examples:

select pg_column_size(5::smallint);
 pg_column_size
----------------
              2
(1 row)


select pg_column_size(5::int);
 pg_column_size
----------------
              4
(1 row)


select pg_column_size(5::bigint);
 pg_column_size
----------------
              8
(1 row)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned various handy functions to get the size of a database, a table, indexes, a tablespace, and a value.