How to get table index size in PostgreSQL

Software Engineer

2024-07-10 23:01:49 +0700 · 1 min read

Indexes play a crucial role in enhancing the performance of database queries. They allow for faster data retrieval by creating a separate data structure pointing to the data stored in a table. However, indexes come with a cost in terms of disk space consumption. Understanding the size of indexes helps you evaluate their impact on storage requirements and overall database performance. In this blog post, we will explore how to get the table index size in PostgreSQL

1. Index size of all tables

To retrieve the index size of all tables in PostgreSQL

// language: sql
SELECT
    relname AS object_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

The sample result is

// language: bash
"object_name"    "table_size"    "index_size"   "total_size"
"users"          "8192 bytes"    "64 kB"        "80 kB"
"admin_users"    "8192 bytes"    "48 kB"        "64 kB"

2. Index type of each table

To retrieve each index type in the table "users" (you can try with other tables)

// language: sql
SELECT i.relname "Table Name",indexrelname "Index Name",
 pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
 pg_size_pretty(pg_indexes_size(relid)) as "Total Size of all Indexes",
 pg_size_pretty(pg_relation_size(relid)) as "Table Size",
 pg_size_pretty(pg_relation_size(indexrelid)) "Index Size"
FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid 
WHERE i.relname='users'

The sample result is

// language: bash
"Table Name"   "Index Name"                             "Total Size"    "Total Size of all Indexes"    "Table Size"    "Index Size"
"users"        "users_pkey"                             "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"
"users"        "index_users_on_email"                   "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"
"users"        "index_users_on_reset_password_token"    "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"
"users"        "index_users_on_confirmation_token"      "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"

Related blogs

Speed Up Independent Queries Using Rails load_async

When you're building a dashboard, it's common to fetch multiple, independent datasets. The page loading might be slow because it has to fetch all data to render a page. A common solution is using AJAX to load pieces of the dashboard, which is great, ...

Software Engineer

2025-10-16 23:07:15 +0700 · 7 mins read

Hello Golang: My First Steps to the Language

I’ve worked with Ruby in several projects, which is defined as "a programmer’s best friend" for its clean and English-like syntax. While my back-end experience is rooted in the Ruby on Rails framework, I prefer TypeScript for building CLI tools and s...

Software Engineer

2025-10-18 19:05:21 +0700 · 6 mins read