Capacity planning is an important topic of database management, in terms of size, performance and robustness.
TL;DR: \l+ database_name
Purpose: PostgreSQL DB Size Command
Database size is an important metric for database administrators, that shows growth ratio of the total data. The amount of data we have in our databases effects database performance. Bigger data file (in most cases) need more time to query.
How to Find Database Size in PostgreSQL?
There are two ways to find database size in PostgreSQL.
Quick Way
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges | Size | Tablespace | Description
--------------+----------+----------+------------+------------+------------+-----------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | | 7541 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +| 7297 kB | pg_default | unmodifiable empty database
| | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +| 7525 kB | pg_default | default template for new databases
| | | | | | | postgres=CTc/postgres | | |
testdatabase | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | | 7525 kB | pg_default |
(4 rows)
I know, output above looks not very good on your screen, but it shows details about database.
To find out size of one particular database:
postgres=# \l+ postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges | Size | Tablespace | Description
----------+----------+----------+------------+------------+------------+-----------------+-------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | | 7541 kB | pg_default | default administrative connection database
(1 row)
SQL Way
You can also run a SQL statement to find size of a database:
postgres=# SELECT pg_size_pretty(pg_database_size('postgres'));
pg_size_pretty
----------------
7541 kB
(1 row)
Conclusion
There are two ways to find DB size in PostgreSQL. You can use pg_database_size(‘dbname’); function in your scripts easily.
Please leave a comment below if you have anything to add or have a question.
Leave a Reply