0%
Reading Settings
Font Size
18px
Line Height
1.5
Letter Spacing
0.01em
Font Family
Table of contents

How to get table index size in PostgreSQL
Software Engineer
Software Engineer

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

Jekyll Asset Caching Strategy for AWS S3 + CloudFront Deployment
Deploying a static Jekyll site to AWS can be fast, but without the right caching strategy, users might see stale content or you might waste bandwidth re-downloading unchanged assets.This blog explains a dual-cache policy that combines long-term cachi...
Software Engineer
Software Engineer


Quick Test Your IP Knowledge
You don’t need to be a software engineer to have heard of IP addresses. They pop up everywhere in our digital lives, from setting up Wi-Fi to troubleshooting connection issues. In this post, I’ve collected some common questions so you can quickly che...
Software Engineer
Software Engineer
