How to get table size in PostgreSQL database?

PostgreSQL offers several functions to help you determine the size of your tables, indexes, and the total size of the relations. These functions include pg_table_size, pg_indexes_size, and pg_total_relation_size. Each of these functions provides different aspects of storage size:

• pg_table_size: Returns the size of the table itself, excluding indexes.
• pg_indexes_size: Returns the size of all indexes attached to the table.
• pg_total_relation_size: Returns the total size of the table, including indexes and any additional storage.

SQL Query to Retrieve Table Sizes

To efficiently retrieve the size of all tables in a PostgreSQL database, you can use the following SQL query. This query lists the table name, table size, index size, and total size, ordered by the total size in descending order:

with
  tables as (
    select
      concat_ws('.', t.table_schema, t.table_name) as table_name,
      concat_ws('.', t.table_schema, t.table_name)::regclass as tableoid
    from information_schema.tables as t
    where t.table_schema = 'public'
  )
select
  table_name,
  pg_size_pretty(pg_table_size(tableoid)) as table_size,
  pg_size_pretty(pg_indexes_size(tableoid)) as indexes_size,
  pg_size_pretty(pg_total_relation_size(tableoid)) as size
from tables
order by pg_total_relation_size(tableoid) desc;

Summary

Retrieving table sizes in PostgreSQL is straightforward and can provide valuable insights into your database's storage usage. Using the provided SQL query, you can easily monitor and manage your database's storage, ensuring optimal performance and resource allocation, such as partitioning or sharding.

Happy resizing tables!