Analyzing Disk Space Usage of Tables and Indexes in PostgreSQL: From Basic Functions to Comprehensive Queries

Overview of PostgreSQL Database Object Size Functions

PostgreSQL provides a series of built-in functions to query the disk space usage of database objects. These functions are part of the system function library and are specifically designed for administrative tasks. Unlike database systems such as Oracle, PostgreSQL encapsulates these functionalities into easy-to-use functions, avoiding complex multi-table join queries.

Key functions include:

• pg_total_relation_size(relation regclass): Returns the total size of the specified relation (table or index), including all related storage such as table data, indexes, and TOAST data
• pg_table_size(relation regclass): Returns the disk space occupied by a table, including main table data, TOAST tables, TOAST indexes, etc.
• pg_indexes_size(relation regclass): Returns the total size of all indexes on a table
• pg_size_pretty(bigint): Converts byte counts into human-readable formats (e.g., KB, MB, GB)

Querying Space Usage for a Single Table

For querying a single table, you can directly use the pg_total_relation_size function. This function accepts a regclass-type parameter, which can be a table name or a schema-qualified table name. For example, to query the total size of a table named "sales":

SELECT pg_size_pretty(pg_total_relation_size('sales'));

If the table is in a specific schema, use the fully qualified name:

SELECT pg_size_pretty(pg_total_relation_size('"public"."orders"'));

It is important to note that quote handling in table names is crucial. In PostgreSQL, if a table name contains uppercase letters, special characters, or conflicts with reserved keywords, double quotes must be used. String concatenation in function calls must correctly handle quote escaping.

Comprehensive Query for All Tables' Space Usage

In actual database management, it is often necessary to view the space usage of all tables. This can be achieved by combining the information_schema.tables system view with size functions. information_schema.tables contains basic information about all tables in the database, including table schema and table name.

The basic query structure is as follows:

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

This query excludes system catalog tables, displaying only user-defined tables, and sorts them in descending order by size, making it easy to quickly identify the tables occupying the most space.

Detailed Space Breakdown Query

For a more detailed analysis of storage structure, you can separately query table data and index sizes. The following query provides a breakdown view of table size, index size, and total size:

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

The key improvement in this query is the use of the pg_table_size() function instead of the simpler pg_relation_size(). pg_table_size() includes all related storage such as table metadata and TOAST data, ensuring that the sum of table size and index size equals the total size, providing more accurate space analysis.

Space Queries in the psql Command-Line Tool

In addition to SQL queries, PostgreSQL's interactive terminal psql offers commands for quickly viewing space usage. Although these commands have limited functionality, they are practical for daily management and quick checks.

To view database sizes:

\l+

This command lists all databases and their size information. Example output:

berbatik_prd_commerce    | berbatik_prd     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 19 MB   | pg_default |
berbatik_stg_commerce    | berbatik_stg     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8633 kB | pg_default |
bursasajadah_prd         | bursasajadah_prd | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 1122 MB | pg_default |

To view table sizes:

\d+

After connecting to a specific database, this command displays size information for all tables in the current database. Example output:

public | tuneeca_prd | table | tomcat | 8192 bytes |
public | tuneeca_stg | table | tomcat | 1464 kB    |

It is important to note that these psql commands can only be used within the psql environment and cannot be directly invoked in applications or scripts. For automated monitoring and reporting, the SQL queries introduced earlier must still be used.

Performance Considerations and Best Practices

When executing space queries in large production databases, performance impact must be considered. Here are some best practices:

1. Avoid Frequent Queries: Size calculation functions need to scan system catalogs and storage structures, which may consume significant resources in large databases. It is recommended to cache query results or schedule executions during off-peak hours.
2. Use Materialized Views: For frequently queried space information, create materialized views and refresh them periodically to avoid recalculating each time.
3. Handle Partitioned Tables: For partitioned tables, pg_total_relation_size only returns the size of the parent table. Querying all child partitions is necessary to obtain complete size information.
4. Monitor Space Growth: Combined with PostgreSQL's statistical information, track table space growth trends to promptly identify abnormal growth patterns.

Comparison with Other Database Systems

Compared to database systems like Oracle, PostgreSQL's space query mechanism is more concise and unified. In Oracle, it is typically necessary to query multiple system views (e.g., user_segments, user_lobs, user_indexes) and perform complex joins and aggregations. PostgreSQL, however, provides a more direct interface through encapsulated functions.

This design difference reflects the distinct philosophies of the two database systems: Oracle offers lower-level access to storage information, allowing finer control but requiring more complex queries; PostgreSQL provides more user-friendly interfaces through abstraction layers, simplifying common administrative tasks.

Practical Application Scenarios

Accurate space information is crucial in multiple scenarios:

• Capacity Planning: Predict storage needs and plan hardware upgrades
• Performance Optimization: Identify overly large tables or indexes and consider partitioning or archiving
• Cost Management: In cloud environments, precise space information aids in cost control and optimization
• Maintenance Operations: Understanding object sizes before performing maintenance operations like VACUUM or REINDEX helps assess operational impact

Through the methods introduced in this article, database administrators can comprehensively grasp the storage usage of PostgreSQL databases, providing data support for effective database management and optimization.