Getting PostgreSQL table sizes

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

select pg_relation_size('actor');

Thepg_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 thepg_size_pretty() function.

Thepg_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 thepg_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 thepg_total_relation_size() function. For example, the following statement uses thepg_total_relation_size() to retrieve the total size of theactor table:

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

The following shows the output:

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

You can use thepg_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 thedvdrental database:

SELECT    relnameAS "relation",    pg_size_pretty (        pg_total_relation_size (C .oid)    )AS "total_size"FROM    pg_class CLEFT JOIN pg_namespace NON (N.oid= C .relnamespace)WHERE    nspnameNOT IN (        'pg_catalog',        'information_schema'    )AND C .relkind<> 'i'AND nspname !~'^pg_toast'ORDER BY    pg_total_relation_size (C .oid)DESCLIMIT 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 thepg_database_size() function. For example, the following statement returns the size of thedvdrental 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 sizeFROM 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 thepg_indexes_size() function.

Thepg_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 thefilm 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 thepg_tablespace_size() function.

Thepg_tablespace_size() function accepts a tablespace name and returns the size in bytes. For example, the following statement returns the size of thepg_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 thepg_size_pretty() function to format the size.
• Use thepg_relation_size() function to get the size of a table.
• Use thepg_total_relation_size() function to get the total size of a table.
• Use thepg_database_size() function to get the size of a database.
• Use thepg_indexes_size() function to get the size of an index.
• Use thepg_total_index_size() function to get the size of all indexes on a table.
• Use thepg_tablespace_size() function to get the size of a tablespace.
• Use thepg_column_size() function to obtain the size of a column of a specific type.

Last updated onFebruary 19, 2024

On this page

• Getting PostgreSQL table sizes
• Getting PostgreSQL database sizes
• Getting PostgreSQL index sizes
• Getting PostgreSQL tablespace sizes
• Getting PostgreSQL value sizes
• Summary