PostgreSQL check the size of objects in the database

As a database administrator, you often need to look at the database footprint, which includes the size of databases, tables, indexes, and tablespaces, in order to allocate reasonable storage space for them.

PostgreSQL database size

You can use the pg_database_size() function to get the size of the entire database. For example, the following statement returns the size of the testdb database:

SELECT pg_database_size('testdb');

 pg_database_size
------------------
          9044483

The pg_database_size() function returns the size of the database in bytes, which is not easy to read. You can use the pg_size_pretty() function to convert bytes to more readable values, as follows:

SELECT
  pg_size_pretty(
    pg_database_size('testdb')
  );

The statement returns the following results:

 pg_size_pretty
----------------
 8833 kB

If you want to get the Storage size of all databases in the current database server, use the following statement:

SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS size
  FROM pg_database;

   datname   |  size
-------------+---------
 postgres    | 8561 kB
 template1   | 8401 kB
 template0   | 8401 kB
 testdb      | 8833 kB
 sakila      | 16 MB
 testdb2     | 8521 kB
 test_new_db | 8401 kB

PostgreSQL table size

You can use the pg_relation_size() function to get size of a table. For example, the following statement returns the size of the actor table in the Sakila sample database:

SELECT
  pg_size_pretty(
    pg_relation_size('actor')
  );

 pg_size_pretty
----------------
 16 kB

The pg_relation_size() function returns the size of the table’s data, excluding the size of the indexes in the table. To get the total size of the table, use the pg_total_relation_size() function as follows:

SELECT
  pg_size_pretty(
    pg_total_relation_size('actor')
  );

 pg_size_pretty
----------------
 72 kB

To get the size of each table in the database, you can use the following statement:

SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size('actor')) size
FROM pg_tables
WHERE schemaname = 'public';

      tablename       | size
----------------------+-------
 actor                | 72 kB
 film                 | 72 kB
 payment_p2007_02     | 72 kB
 payment_p2007_03     | 72 kB
 payment_p2007_04     | 72 kB
 payment_p2007_05     | 72 kB
 payment_p2007_06     | 72 kB
 payment_p2007_01     | 72 kB
 address              | 72 kB
 category             | 72 kB
 city                 | 72 kB
 country              | 72 kB
 customer             | 72 kB
 film_actor           | 72 kB
 film_category        | 72 kB
 inventory            | 72 kB
 language             | 72 kB
 rental               | 72 kB
 staff                | 72 kB
 store                | 72 kB
 payment              | 72 kB
 film_copy            | 72 kB
 city_copy            | 72 kB
 film_r               | 72 kB
 film_ranting_g_title | 72 kB

PostgreSQL index size

PostgreSQL pg_indexes_size() function is used to get the size of indexes on a specified table. For example, to get the total size of all indexes on the actor table, use the following statement:

SELECT
  pg_size_pretty(
    pg_indexes_size('actor')
  );

 pg_size_pretty
----------------
 32 kB

PostgreSQL tablespace size

PostgreSQL pg_tablespace_size() function is used to get the size of a specified tablespace.

The following statement returns the size of the pg_default tablespace:

SELECT
  pg_size_pretty (
      pg_tablespace_size('pg_default')
  );

 pg_size_pretty
----------------
 67 MB

PostgreSQL value size

PostgreSQL pg_column_size() function is used to get the space occupied by the specified value, for example:

The following statement returns the size of a value of smallint type:

select pg_column_size(1::smallint);

 pg_column_size
----------------
              2

The following statement returns the size of a value of int type:

select pg_column_size(1::int);

 pg_column_size
----------------
              4

The following statement returns the size of a value of bigint type:

select pg_column_size(1::bigint);

 pg_column_size
----------------
              8

Conclusion

This article describes several functions to get the size of databases, tables, indexes, tablespaces and values.