Learn

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_size will 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_table_size and pg_indexes_size return the size of the table’s data and table’s indexes in bytes. The sum of these two functions is equal to pg_total_relation_size
  • pg_size_pretty can 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 time_series.

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.

tbl_size idx_size total_size
352KB 184KB 536KB

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;
idx_size
184 kB

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 pg_size_pretty.

Instructions

1.

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.

2.

Write the query to find the total size of the indexes on house_prices.

3.

The table house_prices has only one index, mock.house_prices_pk. Call pg_total_relation_size() on this index, compare this result to your answer from the previous exercise. Are they the same?

4.

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?

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?