Check size of a database, a table or an index#
PostgreSQL® offers different commands and functions to get disk space usage for a database, a table, or an index. The results of executing specific commands and functions may vary, which can cause misinterpretation or confusion.
This article provides commands and functions used to check disk space usage for a database, a table, and an index. It also shows differences between the results these commands and functions return.
Get a database size#
Retrieve the database size using either the \l+ [ pattern ] command `` or the the pg_database_size function.
Use the \l+ [ pattern ] command#
testdb2=> \l+
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+-----------+------------+------------------------------------
_aiven | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/postgres +| No Access | pg_default |
...
testdb2 | avnadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 66 MB | pg_default |
(6 rows)
testdb2=> \l+ testdb2
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
testdb2 | avnadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 66 MB | pg_default |
(1 row)
Use the pg_database_size function#
testdb2=> select pg_database_size('testdb2');
pg_database_size
------------------
68895523
(1 row)
testdb2=> select pg_size_pretty(pg_database_size('testdb2'));
pg_size_pretty
----------------
66 MB
(1 row)
Compare the outputs of the \l+ DB_NAME command and the pg_database_size function
The outputs for the testdb2 database size are the same for both methods. Since the pg_database_size function returns the database size in bytes, we use the pg_size_pretty function to retrieve an easy-to-read output.
Get a table size#
To get the table size, you can use either the \dt+ [ pattern ] command or the pg_table_size function.
Use the \dt+ [ pattern ] command#
testdb2=> \dt+ mytable1
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
-------------+----------+-------+----------+-------------+---------------+-------+-------------
test_schema | mytable1 | table | myowner | permanent | heap | 14 MB |
(1 row)
Use the pg_table_size function#
testdb2=> select pg_size_pretty(pg_table_size('mytable1'));
pg_size_pretty
----------------
14 MB
(1 row)
Get an index size#
Retrieve the individual index size using either the the \di+ [ pattern ] command or the pg_database_size function.
Use the \di+ [ pattern ] command#
testdb2=> \di+ mytable1_indx1
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
-------------+----------------+-------+----------+----------+-------------+---------------+--------+-------------
test_schema | mytable1_indx1 | index | myowner | mytable1 | permanent | btree | 912 kB |
(1 row)
Use the pg_indexes_size function#
The pg_indexes_size function can be used to query the size for all the indexes that belong to a table.
testdb2=> select pg_size_pretty(pg_indexes_size('mytable1'));
pg_size_pretty
----------------
912 kB
(1 row)
Get a size for a table and its indices#
To get disk space usage for a table and its indexes, you can use the pg_total_relation_size function, which computes the total disk space used by the table, all its indices, and TOAST data:
testdb2=> select pg_size_pretty(pg_total_relation_size('mytable1'));
pg_size_pretty
----------------
15 MB
(1 row)
Warning
It is not recommended to use the pg_relation_size function as it computes the disk space used by only one fork of the relation. To get the total size of all the relation’s forks, use higher-level functions pg_total_relation_size or pg_table_size.
Tip
WAL files also contribute to the service disk usage. For more information, check About PostgreSQL® disk usage