PostgreSQL check the size of objects in the database
This article describes how to view the size of databases, tables, indexes, and tablespaces in PostgreSQL.
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.