Getting the database and database object size

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 of a certain table, one could get information about the progress by comparing the original database table size to the one which is being restored. Finally, the database object size often gives information about bloats.

Bloats are wasted disk space due to concurrent data manipulation, heavily updated system often gets bloated due the nature of MVCC. Bloats are often cleaned up by vacuum, thus disabling auto vacuum is not recommended.

To get the database size, one can get the oid database from the pg_database table and run the du -h /data_directory/base/oid Linux command. data_directory is the database cluster folder specified in the postgresql.conf configuration file.

A quick look at the PostgreSQL cluster folders can be quite useful to get familiar with PostgreSQL's main concepts and operations such as configuration files. 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:

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
   datname | size 
-----------------+---------
 postgres | 8093 kB
 template1 | 7481 kB
 template0 | 7481 kB

One can get the table size, including indexes and TOAST tables, using the pg_total_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 tablespaces. Take a look at the following query:

SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) FROM pg_tables LIMIT 2;
  tablename | pg_size_pretty 
--------------+----------------
 pg_statistic | 280 kB
 pg_type | 184 kB
(2 rows)

Finally, to get the index size, one could use the pg_relation_size function, as follows:

SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid::regclass)) FROM pg_index LIMIT 2;
          indexrelid | pg_size_pretty 
------------------------------+----------------
 pg_toast.pg_toast_2604_index | 8192 bytes
 pg_toast.pg_toast_2606_index | 8192 bytes
(2 rows)

For simplicity, the psql tool also provides meta commands to get the size of the database, table, and index as follows:

l+: To list database information including size.
dtis+: The letters t, i, and s stand for table, index, and sequence respectively. This meta command lists database objects including tables, indexes and sequences. The + is used to show the object size on the hard disk.

The table size on the hard disk gives you hints about restoring the data progress of the table. Let us assume you are moving a table from one database to another using the copy command; you cannot see how many rows were inserted, but you can compare the two table sizes, which indicates your progress.

Table of Contents for Getting the database and database object size

Create new playlist

Sign In

Sign Up

Table of Contents for
Getting the database and database object size