In order to manage database disk utilization, you should first be able to measure disk utilization. As a database user, you can use the following functions to check the size of a relation in a database.
pg_total_relation_sizewill return the size of the table and all its indexes in bytes. These values are often in the millions or billions and thus hard to read.
pg_indexes_sizereturn the size of the table’s data and table’s indexes in bytes. The sum of these two functions is equal to
pg_size_prettycan be used with the functions above to format a number in bytes as KB, MB, or GB.
All of the logic to get the table or index size is in the function itself, although PostgreSQL does store table size in specific internal tables, you can simply write a statement like
SELECT pg_table_size(table_name); to get a table’s size. Let’s look into an example using the table
SELECT pg_size_pretty(pg_table_size('time_series')) as tbl_size, pg_size_pretty(pg_indexes_size('time_series')) as idx_size, pg_size_pretty(pg_total_relation_size('time_series')) as total_size;
From this query, we can see that the table has an index with a size of about 180KB, data size of about 350KB, and a total size equal to the sum.
Because indexes are relations in their own right, you can also call
pg_total_relation_size on a single index to get the size of the index. For example, if we know that
time_series has an index named
pk_mocked_data, we can check the total relation size of that index with the following.
SELECT pg_size_pretty( pg_total_relation_size('pk_mocked_data') ) as idx_size;
One other thing to consider is that in PostgreSQL, when you
SELECT a function value, the column in the response will have the same name as the function. For clarity, it can help to include
... as <desired name> in your queries, otherwise columns in the response would be named
There is a table in the database named
house_prices which contains several thousand fake house prices and locations. Write a query to find the total size of the table data in
mock.house_prices, (i.e, the size excluding any indexes).
Throughout this lesson, your answers should be wrapped in
pg_size_pretty() to make the results more readable. By default,
pg_size_pretty() names the column in your response, but for these exercises you don’t need to worry about aliasing the size columns to a different name.
Write the query to find the total size of the indexes on
house_prices has only one index,
pg_total_relation_size() on this index, compare this result to your answer from the previous exercise. Are they the same?
Find the total relation size of
house_prices, this number should be the sum of index and table data. Is this number consistent with the answers you received in parts 1 and 2?