Managing disk space and assigning table spaces to tables as well as databases requires knowledge about the size of the database and database objects. When performing a logical restore, one could get information about the progress by comparing the original database size and the one which is being restored. Finally, the database object size often gives information about bloats.
Getting the database size is an important administration task because it allows the administrator to put migration plans and handle common maintenance issues, such as out-of-space and bloat issues.
To get the database size, one can get the oid database from the pg_database table and run the Linux command, du -h /data_directory/base/oid, where data_directory is the database cluster folder specified in the postgresql.conf configuration file. In this regard, a quick look at the PostgreSQL cluster folders is quite useful. For example, to determine the creation date of a certain database, one could have a look at the PG_VERSION file creation date located in the database directory.
In addition to this, PostgreSQL provides the pg_database_size function to get the database size and the pg_size_pretty function to display the size in a human readable form, as follows:
car_portal=# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database; datname | size ------------+--------- template1 | 6417 kB template0 | 6409 kB postgres | 6540 kB test | 6532 kB car_portal | 29 MB (1 rows)
One can get the table size, including indexes and toast tables, using the pg_totoal_relation_size function. If one is interested only in the table size, one can use the pg_relation_size function. This information helps manage table growth as well as table spaces. Take a look at the following query:
car_portal=# SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) FROM pg_tables WHERE schemaname = 'car_portal_app' LIMIT 2; tablename | pg_size_pretty ------------------------+---------------- advertisement_rating | 16 kB log | 48 kB (1 rows)
Finally, to get the index size, one could use the pg_relation_size function, as follows:
car_portal=# SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid::regclass)) FROM pg_index WHERE indexrelid::regclass::text like 'car_portal_app.%' limit 2; indexrelid | pg_size_pretty ----------------------------------------------------------------------+---------------- car_portal_app.account_email_key | 16 kB car_portal_app.account_history_account_id_search_key_search_date_key | 8192 bytes (1 rows)