Thursday , December 1 2022

How To Find the Size of Tables and Indexes in PostgreSQL

As with most database systems, PostgreSQL offers us various system functions to easily calculate the disk size of the objects. We can get the size of a table using these functions. These functions;

pg_table_size: The size of a table, excluding indexes.
pg_total_relation_size: Total size of a table.
pg_relation_size: The size of an object (table index, etc.) on disk. It is possible to get more detailed information from this function with additional parameters.
pg_size_pretty: Other functions return results in bytes. Converts this into readable format (kb, mb, gb)

Find the Size of a Table

index (primary key) in size_test_table. We get the table size as follows;

Let’s show it better;

Find the Size of all Tables in a Database

The indexes of a table in PostgreSQL can be in different tablespace (on different disk if desired).

You can create a view from the query above and query from view;

All tables in a schema are listed in alphabetical order;

Total size of the biggest 10 tables;

In addition, we could list tables using pg_class instead of pg_tables. Now it’s your creativity.

If you want to list the databases by their size, you should read the following article.

How to Find The Size of all Databases in PostgreSQL

Şahap Aşçı
Author: Şahap Aşçı

About Şahap Aşçı

Leave a Reply

Your email address will not be published. Required fields are marked *