Retrieving Table and Index Storage Size in SQL

1. Overview

Understanding and monitoring database storage usage is an essential aspect of maintaining healthy and performant database systems. Thus, whether we manage a small application or a large enterprise system, knowing how much space our tables and indexes consume helps in capacity planning, performance optimization, and resource allocation.

In this tutorial, we’ll explore the techniques for retrieving table and index storage sizes in MySQL, PostgreSQL, and SQL Server. Specifically, we’ll focus on obtaining two key metrics: The space consumed by table data and the space used by indexes. Moreover, we’ll examine valuable insights like index counts and index-to-data size ratios.

Our examples are based on our University database schema, which includes tables for students, courses, faculty, and related academic data.

2. Why Do We Conduct Storage Analysis?

Storage analysis in database systems is an aspect of database administration that requires careful attention and regular monitoring. It has several purposes:

• proactive capacity planning for growing datasets
• identification of tables with excessive index overhead
• detection of potential storage inefficiencies
• resource allocation for database operations

When analyzing storage, we focus on data size, the space used by table records; and index size, the space consumed by indexes.

Additionally, total size provides a comprehensive view of combined space usage, while the index-to-data ratio helps us understand the proportion of space used by indexes versus data.

This information helps database administrators decide when to archive or partition data, which indexes to maintain or remove, how to optimize storage allocation, and when to plan for storage upgrades.

3. MySQL Storage Size Analysis

MySQL provides comprehensive storage information through its information_schema database. This built-in functionality allows administrators to track storage metrics efficiently without requiring additional tools or complex calculations.

3.1. Basic Table Storage Information

We can query the information_schema.tables view to get detailed insights into how tables use space:

SELECT 
    table_name,
    ROUND((data_length / 1024 / 1024), 2) AS data_size_mb,
    ROUND((index_length / 1024 / 1024), 2) AS index_size_mb,
    table_rows,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS total_size_mb
FROM information_schema.tables
WHERE 
    table_schema = 'University'
    AND table_type = 'BASE TABLE'
ORDER BY 
    total_size_mb DESC;

+---------------+--------------+---------------+------------+---------------+
| TABLE_NAME    | data_size_mb | index_size_mb | TABLE_ROWS | total_size_mb |
+---------------+--------------+---------------+------------+---------------+
| Teaching      |         0.06 |          0.05 |        612 |          0.11 |
| Exam          |         0.02 |          0.05 |        143 |          0.06 |
| Prerequisite  |         0.02 |          0.05 |         50 |          0.06 |
| Registration  |         0.02 |          0.05 |        147 |          0.06 |
| Specification |         0.02 |          0.05 |        137 |          0.06 |
...

The query provides a comprehensive overview of our University database tables, showing the data size in megabytes, index size in megabytes, number of rows, and total size for each table.

In MySQL, data_length and index_length are reported in bytes. When we divide by 1024 twice (first 1024 to convert bytes to kilobytes, and then another 1024 to convert kilobytes to megabytes), we convert the raw byte measurements to a more readable megabyte format.

This conversion allows database administrators to quickly understand the storage consumption without dealing with large, unwieldy byte numbers.

3.2. Detailed Index Analysis

Index storage analysis is important for understanding how indexes impact database size and identifying potential optimization opportunities:

SELECT 
    t.table_name,
    i.index_name,
    COUNT(i.column_name) AS indexed_columns,
    ROUND(t.index_length / COUNT(DISTINCT i.index_name) / 1024, 2) AS avg_index_size_kb
FROM information_schema.tables t
JOIN information_schema.statistics i 
    ON t.table_name = i.table_name 
    AND t.table_schema = i.table_schema
WHERE 
    t.table_schema = 'University'
GROUP BY 
    t.table_name, 
    i.index_name,
    t.index_length
ORDER BY 
    avg_index_size_kb DESC;

+---------------+-----------------------------------+-----------------+-------------------+
| TABLE_NAME    | INDEX_NAME                        | indexed_columns | avg_index_size_kb |
+---------------+-----------------------------------+-----------------+-------------------+
| Specification | specification_course_id_fkey      |               1 |             48.00 |
| Registration  | PRIMARY                           |               1 |             48.00 |
| Registration  | id                                |               1 |             48.00 |
| Registration  | registration_course_id_fkey       |               1 |             48.00 |
| Registration  | registration_student_id_fkey      |               1 |             48.00 |
...

This query shows each index’s name, the number of columns it covers, and its average size in kilobytes.

This information can help us identify oversized indexes or those needing optimization.

4. PostgreSQL Storage Analysis

PostgreSQL offers a rich set of system catalogs and specialized functions designed specifically for storage analysis. These tools provide high-level overviews and detailed insights into how storage is utilized.

4.1. Table Storage Information

PostgreSQL provides specialized system functions for storage analysis.

When we query system catalogs, we use functions like pg_relation_size() to retrieve a table’s physical storage size in bytes and pg_size_pretty() to convert these raw sizes into human-readable formats:

SELECT 
    relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    reltuples::bigint AS row_estimate
FROM pg_class c
JOIN pg_namespace n 
    ON n.oid = c.relnamespace
WHERE 
    nspname = 'public'
    AND relkind = 'r'
ORDER BY 
    pg_total_relation_size(c.oid) DESC;
table_name   | table_size | total_size | row_estimate
---------------+------------+------------+--------------
 teaching      | 40 kB      | 104 kB     |          612
 registration  | 16 kB      | 56 kB      |          147
 course        | 16 kB      | 56 kB      |           81
 exam          | 16 kB      | 56 kB      |          143
 prerequisite  | 8192 bytes | 24 kB      |           -1
...

The query filters results using specific parameters:

• the condition nspname = ‘public’ targets tables in the default schema
• relkind = ‘r’ ensures we retrieve only regular tables

Further, we use object identifiers (oid) to reference database objects uniquely.

4.2. Index Storage Details

When analyzing index storage, we leverage PostgreSQL’s system catalogs and statistical views:

SELECT i.schemaname, 
       i.tablename, 
       i.indexname, 
       pg_size_pretty(pg_relation_size(ui.indexrelid)) AS index_size, 
       i.indexdef
FROM pg_indexes i
JOIN pg_stat_user_indexes ui 
  ON i.indexname = ui.indexrelname
WHERE i.schemaname = 'public'
ORDER BY pg_relation_size(ui.indexrelid) DESC;
 schemaname |   tablename   |     indexname      | index_size |                                    indexdef
------------+---------------+--------------------+------------+-----------------------------------------------------
 public     | teaching      | teaching_pkey      | 40 kB      | CREATE UNIQUE INDEX teaching_pkey ON public.teaching USING btree (id)
 public     | program       | program_pkey       | 16 kB      | CREATE UNIQUE INDEX program_pkey ON public.program USING btree (id)
 public     | student       | student_pkey       | 16 kB      | CREATE UNIQUE INDEX student_pkey ON public.student USING btree (id)
 public     | faculty       | faculty_pkey       | 16 kB      | CREATE UNIQUE INDEX faculty_pkey ON public.faculty USING btree (id)
 public     | course        | course_pkey        | 16 kB      | CREATE UNIQUE INDEX course_pkey ON public.course USING btree (id)
 ...

Here:

• pg_indexes provides comprehensive index definitions, while pg_stat_user_indexes offers runtime statistics about user-created indexes.
• schemaname identifies the schema where the index exists, tablename shows the associated table, and indexname provides the specific index name.
• pg_relation_size() calculates the physical storage size of each index, allowing us to understand their precise storage footprint.
• The indexdef column reveals the exact index creation statement, including details like index types (for example, a B tree) and the columns involved.

5. SQL Server Storage Analysis

SQL Server provides comprehensive storage analysis capabilities through system views and dynamic management functions.

5.1. Table Storage Information

SQL Server’s storage architecture is based on pages and extents, which we can analyze using system views to gather detailed storage information.

Let’s write a query to reveal the total and used space for each table, along with row counts:

SELECT 
    t.name AS table_name,
    SUM(a.total_pages) * 8 AS total_space_kb,
    SUM(a.used_pages) * 8 AS used_space_kb,
    p.rows AS row_count
FROM sys.tables t
INNER JOIN sys.indexes i 
    ON t.object_id = i.object_id
INNER JOIN sys.partitions p 
    ON i.object_id = p.object_id 
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
    ON p.partition_id = a.container_id
GROUP BY 
    t.name, 
    p.rows
ORDER BY 
    total_space_kb DESC;
table_name       total_space_kb   used_space_kb   row_count
-----------------------------------------------------------
Department             144              32            5
Program                144              32           24
Student                144              32           25
Prerequisite           144              32           50
Faculty                144              32           72
...
-----------------------------------------------------------

We can see the total allocated vs. used space, which helps us identify potential areas for reclaiming storage.

Here:

• sys.tables provides table metadata, while sys.indexes offers index-level details
• sys.partitions tracks table partitioning information, which is crucial for understanding storage across different table segments
• sys.allocation_units reveals how physical storage is allocated to database objects
• container_id links partitions to their specific allocation units, allowing us to track how storage is assigned and used

5.2. Index Storage Analysis

Let’s find how much space an index is using, along with its type and the table it belongs to:

SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    CAST(8 * SUM(a.used_pages) AS DECIMAL(10,2)) AS index_size_kb
FROM sys.indexes i
INNER JOIN sys.partitions p 
    ON i.object_id = p.object_id 
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
    ON p.partition_id = a.container_id
WHERE 
    i.object_id > 100
GROUP BY 
    i.object_id,
    i.name,
    i.type_desc
ORDER BY 
    index_size_kb DESC;
table_name                     index_name                              index_type       index_size_kb
------------------------------ -------------------------------------- ---------------- --------------
plan_persist_plan              plan_persist_plan_cidx                 CLUSTERED            104.00
Teaching                       PK__Teaching__3213E83F7359FDCC         CLUSTERED             72.00
plan_persist_query_text        plan_persist_query_text_cidx           CLUSTERED             56.00
plan_persist_query_text        plan_persist_query_text_idx1           NONCLUSTERED          16.00
plan_persist_query             plan_persist_query_cidx                CLUSTERED             16.00
...

This way, we can identify oversized indexes that might need optimization.

6. Comparative Analysis

Let’s summarize how different database systems handle storage analysis.

6.1. Storage Measurement Units

Each database system has its way of measuring and reporting storage usage.

In MySQL, we work with raw bytes. They give us precise measurements but often need conversion for readability.

In contrast, PostgreSQL makes life easier with built-in functions like pg_size_pretty(), which automatically format sizes in human-readable units.

On the other hand, SQL Server takes a unique approach with its 8 KB pages as a basic unit, which affects how we calculate and interpret storage measurements.

6.2. System Catalogs

Each system organizes its metadata differently.

MySQL’s information_schema provides a straightforward view that makes storage queries relatively simple.

In PostgreSQL, the pg_catalog schema offers more detailed information about object relationships and storage patterns.

SQL Server’s sys schema and a dynamic management view give comprehensive storage insights.

6.3. Index Information

The way each system handles index storage information reflects its underlying architecture.

In MySQL, table and index statistics are combined in information_schema, making it easy to get overall storage pictures.

PostgreSQL keeps relation and index sizes separate, giving us more granular control over our analysis.

Finally, SQL Server’s allocation units let us know how space is used at the physical level. SQL Server also uses a clustered index structure where the table’s primary key data and index are stored together in the same B-tree structure. For non-clustered indexes, the data is stored separately from the table’s physical storage, with the non-clustered indexes containing a pointer to the actual data rows.

7. Conclusion

In this article, we explored techniques for analyzing table and index storage sizes across MySQL, PostgreSQL, and SQL Server. We learned to use system catalogs and built-in functions to retrieve detailed storage metrics.

By understanding how each database system handles storage measurements and regularly monitoring these metrics, we can maintain efficient database performance while ensuring optimal resource allocation.