This blog was first authored in 2020. We’ve updated it in 2025 for clarity and relevance, reflecting current practices while honoring their original perspective.
PostgreSQL offers a rich set of indexing features, and while there’s no shortage of articles explaining how indexes work, one challenge often goes overlooked: maintenance. Over time, unused or duplicate indexes can pile up, slowing down inserts and updates, bloating storage, and making your environment harder to manage.
This post highlights practical queries you can run to check the health of your indexes. These are especially useful when cleaning up old schemas, tuning performance, or troubleshooting sluggish workloads.
Indexes are critical for query performance and for enforcing uniqueness. But every index adds overhead: each insert, update, or delete has to keep those indexes in sync. Left unchecked, unnecessary indexes can hurt rather than help. That’s why it’s important to periodically review them, remove duplicates, and track usage.
The queries below show how to:
Use this query to see all indexes on a given table, along with the schema-qualified table name.
|
1 |
db=# SELECT CONCAT(n.nspname,'.', c.relname) AS table,<br> i.relname AS index_name FROM pg_class c<br> JOIN pg_index x ON c.oid = x.indrelid<br> JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace<br> WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'pgbench_accounts';<br> table | index_name <br>-------------------------+------------------------<br> public.pgbench_accounts | pgbench_accounts_pkey<br> public.pgbench_accounts | pgbench_accounts_index<br>(2 rows) |
Unique indexes enforce constraints at the database level. The indisunique column in pg_index tells you if an index is unique.
|
1 |
SELECT i.relname AS index_name,<br> indisunique is_unique<br>FROM pg_class c<br>JOIN pg_index x ON c.oid = x.indrelid<br>JOIN pg_class i ON i.oid = x.indexrelid<br>LEFT JOIN pg_namespace n ON n.oid = c.relnamespace<br>WHERE c.relkind = ANY (ARRAY['r', 't'])<br>AND c.relname LIKE 'pgbench_accounts';<br> index_name | is_unique <br>------------------------+-----------<br> pgbench_accounts_pkey | t<br> pgbench_accounts_index | f<br>(2 rows) |
A quick way to see how much space an index is using:
|
1 |
SELECT pg_size_pretty(pg_relation_size('pgbench_accounts_index'));<br> pg_size_pretty <br>----------------<br> 132 MB<br>(1 row) |
This query shows the table size, its indexes, and the total storage consumed. It’s an easy way to spot when indexes are taking up more space than expected.
|
1 |
SELECT CONCAT(n.nspname,'.', c.relname) AS table,<br> i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,<br> pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,<br> pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c <br>JOIN pg_index x ON c.oid = x.indrelid<br>JOIN pg_class i ON i.oid = x.indexrelid<br>LEFT JOIN pg_namespace n ON n.oid = c.relnamespace<br>WHERE c.relkind = ANY (ARRAY['r', 't'])<br>AND n.oid NOT IN (99, 11, 12375);<br> table | index_name | table_size | index_size | total_size <br>-------------------------+------------------------+------------+------------+------------<br> public.pgbench_tellers | pgbench_tellers_pkey | 88 kB | 64 kB | 152 kB<br> public.pgbench_accounts | pgbench_accounts_pkey | 2561 MB | 428 MB | 3122 MB<br> public.pgbench_accounts | pgbench_accounts_index | 2561 MB | 132 MB | 3122 MB<br> public.pgbench_branches | pgbench_branches_pkey | 8192 bytes | 16 kB | 24 kB<br>(4 rows) |
Need to see how an index was created? Use pg_get_indexdef:
|
1 |
SELECT pg_get_indexdef(indexrelid) AS index_query<br>FROM pg_index WHERE indrelid = 'pgbench_accounts'::regclass;<br> index_query<br>----------------------------------------------------------------------------------------<br>CREATE UNIQUE INDEX pgbench_accounts_pkey ON public.pgbench_accounts USING btree (aid)<br>CREATE INDEX pgbench_accounts_index ON public.pgbench_accounts USING btree (bid)<br>CREATE INDEX pgbench_accounts_index_dup ON public.pgbench_accounts USING btree (bid)<br>(3 rows) |
If an index becomes corrupted or bloated, REINDEX CONCURRENTLY lets you rebuild it while keeping the table available.
|
1 |
REINDEX INDEX CONCURRENTLY idx;<br>REINDEX |
Not every index method supports every operator or data type. This query shows which data types are supported by B-tree.
|
1 |
SELECT amname,<br> opfname<br>FROM pg_opfamily,<br> pg_am<br>WHERE opfmethod = pg_am.oid<br>AND amname = 'btree';<br><br>amname | opfname<br>--------+--------------------<br>btree | array_ops<br>btree | bit_ops<br>btree | bool_ops<br>… |
Indexes with idx_scan counts close to zero may be unnecessary. Be careful before dropping them — some may enforce uniqueness even if rarely scanned.
|
1 |
SELECT s.relname AS table_name,<br> indexrelname AS index_name,<br> i.indisunique,<br> idx_scan AS index_scans<br>FROM pg_catalog.pg_stat_user_indexes s,<br> pg_index i<br>WHERE i.indexrelid = s.indexrelid;<br>table_name | index_name | indisunique | index_scans<br>------------------+-----------------------+-------------+-------------<br>pgbench_branches | pgbench_branches_pkey | t | 0<br>pgbench_tellers | pgbench_tellers_pkey | t | 0<br>pgbench_accounts | pgbench_accounts_pkey | t | 0<br>(3 rows)<br> |
Duplicate indexes waste space and hurt performance. This query helps you spot them.
|
1 |
SELECT indrelid::regclass table_name,<br> att.attname column_name,<br> amname index_method<br>FROM pg_index i,<br> pg_class c,<br> pg_opclass o,<br> pg_am a,<br> pg_attribute att<br>WHERE o.oid = ALL (indclass) <br>AND att.attnum = ANY(i.indkey)<br>AND a.oid = o.opcmethod<br>AND att.attrelid = c.oid<br>AND c.oid = i.indrelid<br>GROUP BY table_name, <br> att.attname,<br> indclass,<br> amname, indkey<br>HAVING count(*) > 1;<br>table_name | column_name | index_method<br>------------+-------------+--------------<br>foo | a | btree<br><br>(1 row) |
PostgreSQL’s catalog tables give you a powerful way to monitor, analyze, and manage indexes. With queries like these, you can stay ahead of bloat, enforce best practices, and keep performance steady as your workloads grow.
Maintaining PostgreSQL index queries is just one piece of running PostgreSQL at scale. The bigger challenge is making the right choices up front: how you deploy, whether you use open source or proprietary add-ons, and how you balance cost with enterprise-grade features.
If you’re weighing your options, don’t go in without the information you need. Learn from IT leaders who have already been through the process; download our Enterprise PostgreSQL Buyer’s Guide: 10 Hard-Earned Lessons from IT Leaders Who’ve Been There Before.
Resources
RELATED POSTS
