How To Calculate A PostgreSQL Database Size

Knowing where you stand on disk usage is really important in a database. The last thing you want to do is run out of disk space. The following tutorial will list some simple commands to help you find or calculate the disk usage of several PostgreSQL objects. This includes tables, indexes, views, tablespaces, and so on. For the following examples I used the PostgreSQL 9.3.2

How to find the largest table in a PostgreSQL database

To get the largest table we need to query the pg_class table. This is a catalog containing pretty much anything that has columns or is similar to a table. We can get the ten largest tables with the following query:

Once we execute the query we get our results:

Defining the above SELECT:

  • relname is the name of the object. This could be a table, index, or view
  • relpages is the  size of the given table in pages. This is only an estimate and is updated periodically.
  • relkind is the type of object and is defined as follows: r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table.

A note on pg_toast tables. TOAST (The Oversized-Attribute Storage Technique) tables are created when the data set of a table is too big. By default PostgreSQL uses 8kB page sizes. If tuples are larger than 8kB they cannot span multiple pages. To fix this, large values are compressed and split up into multiple physical rows.

How to find the largest index, sequence, view, materialized view, or composite type in a PostgreSQL database

Similar to our query for finding the largest table, however now we look for a different type of relkind value.

How to calculate the disk space used by a PostgreSQL database

The pg_database_size() function will return the file size in bytes of a given database. It only accepts one parameter which is the database name.

The output is returned in bytes. To print out the size in a more user friendly format we can call the same pg_database_size() function, but this time we pass it into pg_size_pretty().

How to calculate the disk space used by a PostgreSQL table

There are two functions for retrieving the disk usage of a table. The first, pg_relation_size() which returns the size of a table excluding the index.

The second option, pg_total_relation_size() which will include the index and toasted data.

How to calculate the index size of a table

pg_indexes_size() returns the physical disk usage of only indexes for a particular table.

How to calculate the size of a PostgreSQL tablespace

Lastly we calculate the size of tablespaces, this we do with the function pg_tablespace_size().

 

Leave a Reply