Comprehensive Guide to Listing and Ordering Tables by Size in PostgreSQL

Fundamental Principles of PostgreSQL Table Size Queries

In PostgreSQL database management, understanding table space usage is crucial for performance optimization and storage planning. PostgreSQL provides multiple system views and functions to obtain table size information, primarily through the information_schema.tables view and pg_catalog system tables.

Core System Function Analysis

PostgreSQL offers a family of functions specifically designed for relational object size calculation:

The pg_total_relation_size(relation regclass) function returns the total disk space usage of the specified relation, including all indexes and TOAST data. This function calculates the complete size of the relation, providing the most accurate overview of table storage consumption.

The pg_relation_size(relation regclass) function returns only the size of the main relation data, excluding indexes and TOAST data. This function is more suitable when analyzing the storage characteristics of table core data.

The pg_size_pretty(bigint) function converts byte counts into human-readable formats (such as KB, MB, GB), facilitating intuitive understanding of storage scale.

Information Schema Based Query Solution

For single-schema environments, particularly in the public schema, the following query can be used:

SELECT
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS size_pretty,
  pg_total_relation_size(quote_ident(table_name)) AS size_bytes
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY size_bytes DESC;

Key aspects of this query include:

• Using quote_ident() function to properly handle special characters in table names
• Restricting to specific schema through WHERE clause
• Ordering by byte size in descending order for quick identification of largest tables

Extended Query for Multi-Schema Environments

In databases containing multiple schemas, the query scope needs to be expanded:

SELECT 
  table_schema, 
  table_name, 
  pg_relation_size('"'||table_schema||'"."'||table_name||'"') AS size_bytes
FROM information_schema.tables
ORDER BY size_bytes DESC;

Characteristics of this query:

• Displays the schema name for each table
• Uses string concatenation to build fully qualified table names
• Suitable for table size analysis across multiple schemas

Alternative Approach Using pg_catalog System Tables

Beyond information_schema, direct queries to pg_catalog system tables are also possible:

SELECT
  schema_name,
  relname AS table_name,
  pg_size_pretty(table_size) AS size_pretty,
  table_size AS size_bytes
FROM (
  SELECT
    pg_catalog.pg_namespace.nspname AS schema_name,
    relname,
    pg_relation_size(pg_catalog.pg_class.oid) AS table_size
  FROM pg_catalog.pg_class
    JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

Advantages of this method:

• Filters out system tables using WHERE schema_name NOT LIKE 'pg_%'
• Directly accesses metadata through pg_class and pg_namespace system tables
• Provides finer-grained control capabilities

Practical Application Scenarios

Table size queries serve multiple important purposes in database management:

Performance Optimization: Identifying the largest tables by space consumption, which are typically primary sources of performance bottlenecks. By analyzing data distribution and access patterns of these tables, targeted optimization strategies can be developed.

Storage Planning: Monitoring table growth trends to support capacity planning. Regular execution of table size queries helps predict storage requirements and prevent space shortage issues.

Maintenance Operations: When performing maintenance operations like VACUUM and REINDEX, prioritizing large tables can significantly improve operational efficiency.

Query Result Interpretation and Considerations

When executing table size queries, several important considerations should be noted:

The size values in query results reflect the actual disk space occupied by tables, which may include dead tuples due to PostgreSQL's MVCC mechanism. Therefore, the actual effective data volume might be smaller than the displayed size.

For tables with substantial update operations, it's recommended to analyze query results after VACUUM operations to obtain more accurate effective data sizes.

When executing these queries in multi-user environments, attention should be paid to potential performance impacts on the system, particularly in databases containing large numbers of tables.

Extended Functionality and Advanced Usage

Beyond basic table size queries, functionality can be extended for more comprehensive storage analysis:

Combining with index size queries to analyze the space ratio between tables and their indexes:

SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(tablename)) AS table_size,
  pg_size_pretty(pg_total_relation_size(tablename) - pg_relation_size(tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename) DESC;

This extended query provides detailed breakdown of table data versus index space, aiding in index strategy optimization and storage allocation decisions.