How to see the size of a table in PostgreSQL (7 Approaches)

Introduction

Understanding the size of a table in PostgreSQL is essential for database performance tuning, storage management, and efficient query planning. This tutorial covers various approaches to determining table size using simple to advanced SQL queries.

7 Approaches to View Table Size in Postgres

Depending on your needs and situations, choose one (or some) of the following methods to go with.

Using pg_relation_size

The pg_relation_size function allows you to see the actual disk space used by a table, excluding indexes:

SELECT pg_relation_size('schema_name.table_name');

Including Indexes

To get the total size including table data and indexes, use:

SELECT pg_total_relation_size('schema_name.table_name');

Formatted Size

For a human-readable format, you can combine the size with pg_size_pretty function:

SELECT pg_size_pretty(pg_total_relation_size('schema_name.table_name'));

This will return the size formatted in bytes, KB, MB, GB, or TB, depending on the table’s size.

Detailed Table Size

If you want to see a detailed breakdown of table size, including the size of its TOAST table and free space, you can join the pg_class, pg_namespace, and pg_tablespace catalog tables:

SELECT 
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size,
  pg_size_pretty(pg_relation_size(C.oid)) AS data_size,
  pg_size_pretty(pg_relation_size(C.oid, 'main')) AS main_fork,
  pg_size_pretty(pg_relation_size(C.oid, 'fsm')) AS free_space_map,
  pg_size_pretty(pg_relation_size(C.oid, 'vm')) AS visibility_map,
  pg_size_pretty((pg_total_relation_size(C.oid) - pg_relation_size(C.oid))) AS external_size
FROM 
  pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE
  relkind = 'r'
  AND nspname = 'schema_name'
  AND relname = 'table_name';

Size of All Tables

To report the size of all tables within a schema or database, you can use a more advanced query:

SELECT 
  table_schema || '.' || table_name AS full_table_name,
  pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS total_size,
  pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) AS data_size
FROM 
  information_schema.tables
WHERE
  table_schema = 'public'
  AND table_type = 'BASE TABLE'
ORDER BY 
  pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

This will produce a list of all public tables sorted by size, from the largest to the smallest, in a user-friendly format.

Size of Tables in a Specific Schema

If you have multiple schemas and you’re only interested in the size of tables within a certain schema. The following query can be helpful:

SELECT 
  table_name,
  pg_size_pretty(pg_total_relation_size('schema_name."' || table_name || '"')) AS size
FROM 
  information_schema.tables
WHERE
  table_schema = 'your_schema'
ORDER BY
  size DESC;

This query will display all tables in your_schema, sorted by size.

Live Table Size Changes

Monitoring the live table size change is useful for tables with heavy write and delete operations. Use the pg_stat_user_tables view to obtain these statistics:

SELECT 
  relname,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) as current_size,
  n_tup_ins - n_tup_del AS net_row_change
FROM 
  pg_stat_user_tables
WHERE
  schemaname = 'schema_name';

This will allow you to see current table sizes along with the net row changes due to insertions and deletions, which can shed light on growth trends.

Conclusion

Throughout this tutorial, we explored a variety of SQL commands and functions available in PostgreSQL to determine table sizes with precise detail as well as a full database overview. Understanding how to interpret and analyze these sizes is crucial for effective database administration and optimization.