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.
Why PostgreSQL index maintenance matters
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:
- List indexes on a table
- Check if they are unique
- Measure their size
- Compare table and index storage
- Rebuild bloated indexes
- Spot unused or duplicate indexes
1. List indexes on a table
Use this query to see all indexes on a given table, along with the schema-qualified table name.
|
1 2 3 4 5 6 7 8 9 10 |
db=# SELECT CONCAT(n.nspname,'.', c.relname) AS table, i.relname AS index_name FROM pg_class c JOIN pg_index x ON c.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'pgbench_accounts'; table | index_name -------------------------+------------------------ public.pgbench_accounts | pgbench_accounts_pkey public.pgbench_accounts | pgbench_accounts_index (2 rows) |
2. Check index uniqueness
Unique indexes enforce constraints at the database level. The indisunique column in pg_index tells you if an index is unique.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT i.relname AS index_name, indisunique is_unique FROM pg_class c JOIN pg_index x ON c.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname LIKE 'pgbench_accounts'; index_name | is_unique ------------------------+----------- pgbench_accounts_pkey | t pgbench_accounts_index | f (2 rows) |
3. Find the size of an index
A quick way to see how much space an index is using:
|
1 2 3 4 5 |
SELECT pg_size_pretty(pg_relation_size('pgbench_accounts_index')); pg_size_pretty ---------------- 132 MB (1 row) |
4. Compare table size with indexes
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT CONCAT(n.nspname,'.', c.relname) AS table, i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size, pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size, pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c JOIN pg_index x ON c.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = ANY (ARRAY['r', 't']) AND n.oid NOT IN (99, 11, 12375); table | index_name | table_size | index_size | total_size -------------------------+------------------------+------------+------------+------------ public.pgbench_tellers | pgbench_tellers_pkey | 88 kB | 64 kB | 152 kB public.pgbench_accounts | pgbench_accounts_pkey | 2561 MB | 428 MB | 3122 MB public.pgbench_accounts | pgbench_accounts_index | 2561 MB | 132 MB | 3122 MB public.pgbench_branches | pgbench_branches_pkey | 8192 bytes | 16 kB | 24 kB (4 rows) |
- pg_relation_size: returns size of a table or index
- pg_total_relation_size: includes table + indexes
5. Show the index creation query
Need to see how an index was created? Use pg_get_indexdef:
|
1 2 3 4 5 6 7 8 |
SELECT pg_get_indexdef(indexrelid) AS index_query FROM pg_index WHERE indrelid = 'pgbench_accounts'::regclass; index_query ---------------------------------------------------------------------------------------- CREATE UNIQUE INDEX pgbench_accounts_pkey ON public.pgbench_accounts USING btree (aid) CREATE INDEX pgbench_accounts_index ON public.pgbench_accounts USING btree (bid) CREATE INDEX pgbench_accounts_index_dup ON public.pgbench_accounts USING btree (bid) (3 rows) |
6. Rebuild bloated indexes without downtime
If an index becomes corrupted or bloated, REINDEX CONCURRENTLY lets you rebuild it while keeping the table available.
|
1 2 |
REINDEX INDEX CONCURRENTLY idx; REINDEX |
7. Check supported data types for index methods
Not every index method supports every operator or data type. This query shows which data types are supported by B-tree.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT amname, opfname FROM pg_opfamily, pg_am WHERE opfmethod = pg_am.oid AND amname = 'btree'; amname | opfname --------+-------------------- btree | array_ops btree | bit_ops btree | bool_ops … |
8. Find unused indexes
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 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s.relname AS table_name, indexrelname AS index_name, i.indisunique, idx_scan AS index_scans FROM pg_catalog.pg_stat_user_indexes s, pg_index i WHERE i.indexrelid = s.indexrelid; table_name | index_name | indisunique | index_scans ------------------+-----------------------+-------------+------------- pgbench_branches | pgbench_branches_pkey | t | 0 pgbench_tellers | pgbench_tellers_pkey | t | 0 pgbench_accounts | pgbench_accounts_pkey | t | 0 (3 rows) |
9. Detect duplicate indexes
Duplicate indexes waste space and hurt performance. This query helps you spot them.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT indrelid::regclass table_name, att.attname column_name, amname index_method FROM pg_index i, pg_class c, pg_opclass o, pg_am a, pg_attribute att WHERE o.oid = ALL (indclass) AND att.attnum = ANY(i.indkey) AND a.oid = o.opcmethod AND att.attrelid = c.oid AND c.oid = i.indrelid GROUP BY table_name, att.attname, indclass, amname, indkey HAVING count(*) > 1; table_name | column_name | index_method ------------+-------------+-------------- foo | a | btree (1 row) |
Conclusion
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.
About the Author
Ibrar AhmedJoined Percona in the month of July 2018. Before joining Percona, Ibrar worked as a Senior Database Architect at EnterpriseDB for 10 Years. Ibrar has 18 years of software development experience. Ibrar authored multiple books on PostgreSQL.
