Identify Large PostgreSQL Indexes for Optimization

Mar 17, 2024 / Oct 11, 2026 · 9 min read · indexes storage optimization performance pg_index pg_class pg_namespace pg_tablespace pg_stat_user_indexes ·

Share on:

Find the Largest PostgreSQL Indexes by Size

Which indexes are taking up the most disk in this database? pg_index joined to pg_class answers that in one query: every index with its table, schema, tablespace, size, and the command that would recreate it, sorted with the biggest first. It is the inventory step before any decision to drop, rebuild, or move an index.

Purpose and Overview

Indexes speed up reads, but each one costs disk space, buffer cache, and write work. PostgreSQL has to maintain every index on a table for each insert, update, and delete, whether or not any query ever uses it. On a database that has grown for years, a few large indexes often account for a big share of total storage, and some of them may no longer serve any query at all. Knowing which indexes are largest is where that review starts.

The system catalogs hold everything needed for the inventory. pg_index has one row per index and links each index to its table. pg_class describes both the table and the index as relations. pg_namespace gives the schema name, and pg_tablespace names the tablespace an index is stored in when it is not the database default. Size comes from the pg_relation_size() function, and the definition from pg_get_indexdef().

The query below returns, for each index: the schema, the table, the index name, the tablespace, the size in a readable form, the size in bytes for sorting and arithmetic, and the index definition. A second query adds the scan counter from pg_stat_user_indexes, which turns a list of big indexes into a list of big indexes that may not be pulling their weight.

Sample Code

 1SELECT
 2    n.nspname                                   AS schema_name,
 3    c.relname                                   AS table_name,
 4    i.relname                                   AS index_name,
 5    COALESCE(t.spcname, '(database default)')   AS tablespace,
 6    pg_size_pretty(pg_relation_size(i.oid))     AS index_size,
 7    pg_relation_size(i.oid)                     AS index_bytes,
 8    pg_get_indexdef(i.oid)                      AS index_definition
 9FROM pg_index x
10JOIN pg_class c      ON c.oid = x.indrelid
11JOIN pg_class i      ON i.oid = x.indexrelid
12JOIN pg_namespace n  ON n.oid = c.relnamespace
13LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
14WHERE c.relkind IN ('r', 'm', 'p')
15  AND i.relkind IN ('i', 'I')
16  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
17  AND n.nspname !~ '^pg_toast'
18ORDER BY pg_relation_size(i.oid) DESC
19LIMIT 50;

A second variant adds how often each index has been scanned, using the cumulative statistics view:

 1SELECT
 2    s.schemaname,
 3    s.relname                                       AS table_name,
 4    s.indexrelname                                  AS index_name,
 5    pg_size_pretty(pg_relation_size(s.indexrelid))  AS index_size,
 6    s.idx_scan                                      AS index_scans,
 7    x.indisunique                                   AS is_unique,
 8    x.indisprimary                                  AS is_primary_key
 9FROM pg_stat_user_indexes s
10JOIN pg_index x ON x.indexrelid = s.indexrelid
11ORDER BY pg_relation_size(s.indexrelid) DESC
12LIMIT 50;

Notes: The first query runs on PostgreSQL 11 and later as written; on PostgreSQL 10 the 'I' filter simply matches nothing. It reads only system catalogs, so it is cheap to run. Raise or remove the LIMIT to see every index, and add AND n.nspname = 'your_schema' to focus on one schema.

Code Breakdown

Each part of the first query does one job: link indexes to tables, filter to user objects, then size and sort.

pg_index: the link between index and table

pg_index holds one row per index. Its indexrelid column is the OID of the index's own pg_class row, and indrelid is the OID of the pg_class row for the table the index belongs to. That is why pg_class is joined twice: once as c for the table and once as i for the index. The same catalog also carries flags such as indisunique, indisprimary, and indisvalid, which the second variant uses.

Filtering on relkind

relkind is a one-letter code in pg_class for the type of relation. The table side keeps r (ordinary table), m (materialized view), and p (partitioned table). The index side keeps i (index) and I (partitioned index). An earlier version of this script described I as a "case-insensitive" match for i; it is not. I is a separate relation kind — the parent index on a partitioned table. The PostgreSQL documentation describes a partitioned table as a virtual table with no storage of its own; the storage belongs to its partitions. The index space you care about is therefore on each partition's own index, and each of those appears in the list as its own row.

Schema filter

The nspname filter drops the system catalogs, the SQL-standard information_schema, and the pg_toast schemas, so the result shows only indexes your application created. Remove it if you want to see how large the catalog indexes have become.

pg_relation_size and pg_size_pretty

pg_relation_size(i.oid) returns the disk space used by the main data fork of the index, in bytes. pg_size_pretty() turns bytes into a readable string with units such as kB, MB, and GB. The query returns both: the readable text for people, and the raw number for sorting, totals, and alerts.

Sorting by bytes, not by text

This is the line that matters most, and the one the original version of this script got wrong. It ended with ORDER BY index_size ASC, where index_size was the pg_size_pretty() output. PostgreSQL's ORDER BY treats a plain name that matches an output column as that output column, and pg_size_pretty() returns text. So the sort was alphabetical: "9 MB" sorted after "10 GB", and the list did not run from smallest to largest at all. Sorting on pg_relation_size(i.oid) itself sorts on the number, and DESC puts the largest indexes first.

pg_get_indexdef

pg_get_indexdef(i.oid) rebuilds the CREATE INDEX command for the index. The documentation is precise that this is a decompiled reconstruction, not the original text of the command, but it shows the columns, the index method, and any WHERE clause — enough to judge whether two indexes overlap.

The tablespace column

reltablespace is zero when the index lives in the database's default tablespace. The LEFT JOIN keeps those rows, and COALESCE labels them (database default) instead of leaving the column empty.

Key Index Size Factors

Size alone does not make an index a problem. These factors decide what a large number means.

Table size and column width

An index on a wide text column, or a multicolumn index, grows with the table. A large index on a large, busy table is often exactly right. Compare the index size to the table's own size before drawing conclusions.

Bloat

PostgreSQL's REINDEX documentation describes an index as bloated when it "contains many empty or nearly-empty pages", which can happen to B-tree indexes under certain uncommon access patterns. A bloated index is larger than its data needs. REINDEX writes a fresh copy without the dead pages; from PostgreSQL 12, REINDEX CONCURRENTLY can do that without locking out writes.

Usage

The idx_scan column in pg_stat_user_indexes counts the index scans started on each index since statistics were last reset. A large index with very few scans is the classic candidate for removal, because it costs write work and cache space for little benefit.

Constraints

Unique indexes and primary keys enforce rules, not just speed. They stay even with zero scans. The second variant shows is_unique and is_primary_key so these are easy to exclude.

Practical Applications

The query is most useful at a few recurring points in a database's life.

Storage capacity review

When disk usage climbs, run the first query and add up index_bytes for the top results. If indexes are a large share of the database, index cleanup may recover more space than archiving table data.

Finding indexes to drop

Run the second variant on the primary and look for large, non-unique indexes with low index_scans. A common health-check pattern is the same — non-unique indexes with fewer than 50 scans on tables larger than a few pages, largest first. Before dropping, check any read replicas too: a query that runs only on a standby does not show up in the primary's counters.

Planning a rebuild

For a large index that is clearly used, compare its size with the table and with similar indexes elsewhere. If it looks far larger than its data, schedule a REINDEX CONCURRENTLY in a quiet period and compare index_bytes before and after.

Tablespace placement

On systems that use several tablespaces, the tablespace column shows which large indexes share a disk with their tables. That is the starting list for moving hot indexes to faster storage.

Version Compatibility

The catalog columns used here have been stable for many releases. Partitioned tables (relkind = 'p') arrived with declarative partitioning in PostgreSQL 10. Indexes on partitioned tables arrived in PostgreSQL 11, which is where the partitioned-index kind I comes from; on PostgreSQL 10 that part of the filter matches nothing and the query still runs.

PostgreSQL 12 added REINDEX CONCURRENTLY and began including partitioned indexes in the pg_indexes system view. pg_relation_size(), pg_size_pretty(), and pg_get_indexdef() are available across all supported versions. Recent releases also add a last_idx_scan timestamp to pg_stat_user_indexes, which shows when an index was last used rather than only how often.

Best Practices

Sort by bytes, never by the pretty string — pg_size_pretty() returns text, so always order by the numeric size function.
Pair size with usage — a big index that is scanned constantly is earning its keep; the question is always size against idx_scan.
Know when statistics were reset — scan counts only cover the time since the last reset, and PostgreSQL resets all counters after a crash, an immediate shutdown, or a start from a base backup, so a recent reset can make a busy index look unused.
Check replicas before dropping — confirm an index is unused on every server where the schema is queried, not just the primary.
Drop safely — a plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table; DROP INDEX CONCURRENTLY drops it without locking out reads and writes.
Track it over time — save index_bytes weekly; steady growth faster than the table is an early sign of bloat.

References

PostgreSQL: pg_index — Catalog columns linking each index to its table, including indexrelid, indrelid, indisunique, and indisprimary.
PostgreSQL: pg_class — Relation catalog with the relkind codes (i index, I partitioned index, p partitioned table) and reltablespace.
PostgreSQL: Database Object Management Functions — Reference for pg_relation_size(), pg_total_relation_size(), and pg_size_pretty(), which returns text.
PostgreSQL: SELECT — How ORDER BY resolves a name that matches an output column, the cause of the text-sort defect.
PostgreSQL: REINDEX — What index bloat is and how REINDEX rebuilds an index.
Citus Data: A health check playbook for your Postgres database — Unused-index query joining pg_stat_user_indexes to pg_index, sorted by index size.
pganalyze: Unused Indexes check — Why unused indexes slow writes, why primary keys and unique indexes are kept, and dropping with DROP INDEX CONCURRENTLY.