How to check PostgreSQL index size

An index is a separate PostgreSQL relation that consumes storage and must be maintained as indexed data changes. Measuring index bytes helps explain a table's total footprint and identifies candidates for deeper workload review.

A large index can still be essential. Size says nothing by itself about query latency, selectivity, cache behavior, constraint ownership, or the business importance of the queries it supports.

SELECT
  n.nspname AS schema_name,
  c.relname AS table_name,
  pg_table_size(c.oid) AS table_bytes,
  pg_indexes_size(c.oid) AS index_bytes,
  pg_total_relation_size(c.oid) AS total_bytes,
  pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname !~ '^pg_toast'
ORDER BY index_bytes DESC
LIMIT 25;

pg_indexes_size sums the indexes attached to the table. pg_table_size excludes indexes but includes the table's TOAST storage and auxiliary forks. pg_total_relation_size combines both.

SELECT
  n.nspname AS schema_name,
  t.relname AS table_name,
  i.relname AS index_name,
  pg_relation_size(i.oid) AS index_bytes,
  pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM pg_index AS x
JOIN pg_class AS i ON i.oid = x.indexrelid
JOIN pg_class AS t ON t.oid = x.indrelid
JOIN pg_namespace AS n ON n.oid = t.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname !~ '^pg_toast'
ORDER BY index_bytes DESC
LIMIT 25;

pg_stat_user_indexes adds access counters. Include the database statistics reset timestamp so the result carries at least one boundary for the observation period.

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.indexrelname AS index_name,
  s.idx_scan,
  pg_relation_size(s.indexrelid) AS index_bytes,
  d.stats_reset
FROM pg_stat_user_indexes AS s
CROSS JOIN (
  SELECT stats_reset
  FROM pg_stat_database
  WHERE datname = current_database()
) AS d
ORDER BY index_bytes DESC
LIMIT 50;

This query identifies primary, unique, and exclusion properties and any constraint tied directly to the index. It is discovery evidence, not a complete dependency or rollout review.

SELECT
  n.nspname AS schema_name,
  t.relname AS table_name,
  i.relname AS index_name,
  x.indisprimary AS is_primary,
  x.indisunique AS is_unique,
  x.indisexclusion AS is_exclusion,
  con.conname AS constraint_name,
  con.contype AS constraint_type
FROM pg_index AS x
JOIN pg_class AS i ON i.oid = x.indexrelid
JOIN pg_class AS t ON t.oid = x.indrelid
JOIN pg_namespace AS n ON n.oid = t.relnamespace
LEFT JOIN pg_constraint AS con ON con.conindid = x.indexrelid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname !~ '^pg_toast'
ORDER BY n.nspname, t.relname, i.relname
LIMIT 100;

On current PostgreSQL, one executor scan can perform multiple index searches, so idx_scan should not be presented as an exact query count. Statistics also have visibility, update-lag, transaction snapshot, reset, and shutdown caveats.

• Observe a representative workload period that includes rare, scheduled, and seasonal queries.
• Review constraints, dependencies, query plans, replicas, and application code paths.
• Estimate the write and storage tradeoff, then prepare a rollback and validation plan.
• Account for production locking. PostgreSQL documents DROP INDEX CONCURRENTLY as a way to avoid blocking concurrent reads and writes, but it has restrictions and still requires deliberate operational review.

The PostgreSQL health check combines bounded table and index footprint with available cumulative scan counters and labels the result as an observation. Connected database telemetry can collect supported size and usage statistics over time, which gives a better review window than one pasted result.

DatabaseMeter does not treat a large index or a zero counter as automatic proof that an index should be dropped. Use the query analyzer to add bounded workload evidence without uploading SQL text.