How to Get Sizes of Database Objects in PostgreSQL

Summary: in this tutorial, you will learn how to get the sizes of database objects including databases, tables, indexes, tablespaces, and values.

Getting PostgreSQL table sizes

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

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

pg_relation_size
------------------
            16384

To make the result more human-readable, you use the pg_size_pretty() function.

The pg_size_pretty() function formats a number using bytes, kB, MB, GB, or TB appropriately. For example:

SELECT
    pg_size_pretty (pg_relation_size('actor')) size;

The following is the output in kB

size
-------
 16 kB
(1 row)

Note that the pg_relation_size() function returns the size of the table only, not including indexes or additional objects.

To get the total size of a table, you use the pg_total_relation_size() function. For example, the following statement uses the pg_total_relation_size() to retrieve the total size of the actor table:

SELECT
    pg_size_pretty (
        pg_total_relation_size ('actor')
    ) size;

The following shows the output:

size
-------
 72 kB
(1 row)

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 the 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;

Here is the output:

relation  | total_size
------------+------------
 rental     | 2352 kB
 payment    | 1816 kB
 film       | 936 kB
 film_actor | 488 kB
 inventory  | 440 kB
(5 rows)

Getting PostgreSQL database sizes

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

The statement returns the following result:

size
-------
 15 MB
(1 row)

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:

datname  |  size
-----------+---------
 postgres  | 8452 kB
 template1 | 7892 kB
 template0 | 7681 kB
 dvdrental | 15 MB
(4 rows)

Getting PostgreSQL index sizes

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 film table, you use the following statement:

SELECT
    pg_size_pretty (pg_indexes_size('actor')) size;

Here is the output:

size
-------
 32 kB
(1 row)

Getting PostgreSQL tablespace sizes

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. For example, the following statement returns the size of the pg_default tablespace:

SELECT
    pg_size_pretty (
        pg_tablespace_size ('pg_default')
    ) size;

Output:

size
-------
 48 MB
(1 row)

Getting PostgreSQL value sizes

To find how much space is needed to store a specific value, you use the pg_column_size() function, for example:

SELECT
  pg_column_size(5 :: smallint) smallint_size,
  pg_column_size(5 :: int) int_size,
  pg_column_size(5 :: bigint) bigint_size;

Output:

smallint_size | int_size | bigint_size
---------------+----------+-------------
             2 |        4 |           8
(1 row)

Summary

• Use the pg_size_pretty() function to format the size.
• Use the pg_relation_size() function to get the size of a table.
• Use the pg_total_relation_size() function to get the total size of a table.
• Use the pg_database_size() function to get the size of a database.
• Use the pg_indexes_size() function to get the size of an index.
• Use the pg_total_index_size() function to get the size of all indexes on a table.
• Use the pg_tablespace_size() function to get the size of a tablespace.
• Use the pg_column_size() function to obtain the size of a column of a specific type.