SimpleBackupsSimpleBackups

How to Analyze PostgreSQL Table Size

Posted on

When your database becomes large, you'll want to dig further and see what tables is using space and identify if how to optimize this.
This post introduces a SQL query that provides insights into the size of tables within your PostgreSQL database, helping you make informed choices about your database structure.

The PostgreSQL Table Size Query

The query below provides an overview of the largest tables in a PostgreSQL database, arranged from largest to smallest. It reveals the total size of each table, including all associated indexes and any additional data objects.

SELECT
    schemaname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS table_size
FROM
    pg_stat_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

Query Breakdown

Let's dissect the components to understand what each part contributes to the output:

  • pg_stat_user_tables: A system view that provides statistics specifically for user-created tables, helping us filter out system tables.

  • schemaname: Identifies the schema that contains each table, useful for distinguishing tables across different namespaces in your database.

  • relname: The name of the table.

  • pg_total_relation_size(): A function that calculates the total size of a table, inclusive of:

    • The main table data
    • All indexes
    • TOAST (The Oversized Attribute Storage Technique) data, which stores oversized fields
    • TOAST index

  • pg_size_pretty(): Converts the byte count into a human-readable format, displaying results in KB, MB, or GB as applicable.

Output Columns

The query produces the following columns:

  1. schema_name: Specifies the schema where each table is located.
  2. table_name: The name of the table, making it easy to identify tables for further action.
  3. table_size: Provides the total size of the table in a human-readable format, combining all associated storage objects.

Example Output

Here’s a sample of what you might see when running this query:

 schema_name |    table_name     | table_size
-------------+-------------------+-------------
 public      | customer_data     | 2945 MB
 public      | orders            | 892 MB
 analytics   | daily_metrics     | 154 MB

In this example, we can immediately identify the largest table in the database, customer_data, which occupies almost 3 GB of space. Knowing this allows us to assess whether it requires optimization or partitioning.

Making the Most of Table Size Data

Running this query periodically can help keep your database performant and cost-effective. For example:

  • Identify Bloat: If a table’s size grows significantly over time, consider running VACUUM to free up unused space.
  • Optimize Indexes: Large tables often require optimized indexing to speed up queries.
  • Consider Partitioning: For tables with substantial growth, partitioning by time or another logical division can improve query efficiency and maintenance.

Table of contents