List PostgreSQL Tables by Size with SQL

List PostgreSQL Tables by Size with SQL

This PostgreSQL query lists all tables in the current database sorted by their total size, largest first. It uses pg_total_relation_size() to include the table data, indexes, TOAST storage, and free space map in the size calculation.

Purpose and Overview

Disk space pressure is a common operational concern in any growing database. Knowing which tables consume the most space allows administrators to prioritise archiving, partitioning, or compression efforts. It also helps explain sudden disk growth when a particular table has expanded unexpectedly.

Unlike database-level size queries, this script drills down to the table level within the current database, excluding system catalog and information schema tables.

SQL Script

 1SELECT
 2    nspname,
 3    relname,
 4    pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size
 5FROM
 6    pg_class C
 7LEFT JOIN
 8    pg_namespace N ON (N.oid = C.relnamespace)
 9WHERE
10    nspname NOT IN ('pg_catalog', 'information_schema')
11        AND
12    C.relkind <> 'i'
13        AND
14    nspname !~ '^pg_toast'
15ORDER BY
16    pg_total_relation_size(C.oid) DESC;

Code Breakdown

pg_class and pg_namespace

pg_class is the PostgreSQL system catalog that tracks every table, index, sequence, view, and other relation in the database. Each row has an oid (object identifier) used to reference it from other catalog tables.

pg_namespace maps namespace (schema) OIDs to schema names. The LEFT JOIN connects each object to its schema name via the relnamespace column.

pg_total_relation_size(C.oid)

Returns the total disk space used by the relation and all its associated objects — including the main table data, all indexes, the TOAST table (used for large values), and the free space map. This gives the most complete picture of how much space a table is actually consuming.

pg_size_pretty()

Formats the raw byte count into a human-readable string such as 42 MB or 1.3 GB. Used in the SELECT for display; the raw byte value is used in ORDER BY for accurate numeric sorting.

WHERE Filters

nspname NOT IN ('pg_catalog', 'information_schema') — Excludes PostgreSQL internal system catalog tables and the SQL standard information schema, focusing results on user tables.

C.relkind <> 'i' — Excludes index relations. relkind = 'i' represents indexes; excluding them avoids double-counting storage that is already included in pg_total_relation_size().

nspname !~ '^pg_toast' — Excludes TOAST schema objects. TOAST storage for large column values is already included in the pg_total_relation_size() calculation for the parent table.

ORDER BY pg_total_relation_size(C.oid) DESC

Sorts by the raw byte value descending so the largest tables appear first. The pg_size_pretty() formatted string is not used for sorting because it is a text value and would sort lexicographically rather than numerically.

Selected Columns

nspname: The schema name. Useful when multiple schemas exist in the same database.

relname: The relation (table) name.

total_size: Human-readable total size including table data, indexes, and TOAST storage.

Practical Applications

Disk Space Investigation

When disk usage spikes, run this query to immediately identify which table has grown. Sort order ensures the biggest offender is at the top.

Archiving and Partitioning Candidates

Large, continuously growing tables are the primary candidates for time-based partitioning or archiving older rows to a separate table or external storage.

Index Overhead Analysis

Because pg_total_relation_size() includes indexes, a table that appears disproportionately large relative to its row count may have excessive indexing. Compare with a query against pg_indexes or pg_stat_user_indexes to check index count.

Storage Capacity Planning

Track the top tables over time to project future disk usage growth and plan capacity increases or data lifecycle management.

Extending the Query

Show Table and Index Size Separately

 1SELECT
 2    nspname,
 3    relname,
 4    pg_size_pretty(pg_relation_size(C.oid)) AS table_size,
 5    pg_size_pretty(pg_total_relation_size(C.oid) - pg_relation_size(C.oid)) AS index_size,
 6    pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size
 7FROM
 8    pg_class C
 9LEFT JOIN
10    pg_namespace N ON (N.oid = C.relnamespace)
11WHERE
12    nspname NOT IN ('pg_catalog', 'information_schema')
13    AND C.relkind = 'r'
14    AND nspname !~ '^pg_toast'
15ORDER BY
16    pg_total_relation_size(C.oid) DESC;

Limit to a Specific Schema

1WHERE nspname = 'public'
2  AND C.relkind = 'r'

Include Row Count Estimate

 1SELECT
 2    N.nspname,
 3    C.relname,
 4    pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size,
 5    C.reltuples::bigint AS row_estimate
 6FROM
 7    pg_class C
 8LEFT JOIN
 9    pg_namespace N ON (N.oid = C.relnamespace)
10WHERE
11    N.nspname NOT IN ('pg_catalog', 'information_schema')
12    AND C.relkind = 'r'
13    AND N.nspname !~ '^pg_toast'
14ORDER BY
15    pg_total_relation_size(C.oid) DESC;

Version Compatibility

Tested on PostgreSQL 8.4, 9.x, 10.x, 11.x, 12.x, and 13.0. No version-specific features are used — pg_class, pg_namespace, and pg_total_relation_size() have been available since early PostgreSQL versions.

Best Practices

Run regularly: Include this query in weekly operational reviews to catch unexpected table growth early.

Compare table vs index size: A table where index size greatly exceeds table size is a candidate for index consolidation.

Consider TOAST: Tables storing large text, JSON, or binary columns will have significant TOAST storage included in their total. This is expected and normal.

Use with pg_stat_user_tables: Pair size data with n_dead_tup from pg_stat_user_tables to identify tables that are large partly due to dead rows that need vacuuming.

References

• PostgreSQL Documentation - Database Object Size Functions - Reference for pg_total_relation_size and related size functions
• PostgreSQL Documentation - pg_class - Full reference for the pg_class system catalog
• PostgreSQL Documentation - pg_namespace - Reference for the pg_namespace schema catalog
• Source SQL Script - postgres_tables_by_size.sql - Original script by Hari Sekhon