PostgreSQL - get sizes of the database, tables and indexes

postgresql-size-of-objects.md

PostgreSQL table size

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

SELECT pg_relation_size('TableName');

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

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

For example:

The following is the output in kB
 pg_size_pretty
----------------
     16 kB

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 including the size of indexes and other objects, use the pg_total_relation_size() function.

SELECT pg_size_pretty (pg_total_relation_size ('TableName'));

 pg_size_pretty
----------------
 72 kB
(1 row)

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 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     | 2472 kB
 payment    | 2232 kB
 film       | 688 kB
 film_actor | 536 kB
 inventory  | 464 kB
(5 rows)

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

The statement returns the following result:
pg_size_pretty
----------------
 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;

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

Size of all databases

SELECT t1.datname AS databaseName, pg_size_pretty(pg_database_size(t1.datname)) as databaseSize FROM pg_database t1 ORDER BY pg_database_size(t1.datname) DESC;

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:

SELECT pg_size_pretty (pg_indexes_size('actor'));

Here is the output:
 pg_size_pretty
----------------
 32 kB
(1 row)

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:
 pg_size_pretty
----------------
 43 MB
(1 row)

PostgreSQL value size

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

postgres=# select pg_column_size(5::smallint);
 pg_column_size
----------------
              2
(1 row)
 
postgres=# select pg_column_size(5::int);
 pg_column_size
----------------
              4
(1 row)
 
postgres=# select pg_column_size(5::bigint);
 pg_column_size
----------------
              8
(1 row)