skip to content

How would you use a database's catalog metadata to answer operational questions such as which indexes are never used, how much space a table and its indexes occupy, and whether the optimizer's statistics are stale?

level: middleimportance: should knowfreq 40%

answer

  1. Physical facts = native catalog only
  2. idx scans cumulative since reset; per node
  3. Total size = heap + indexes + out-of-line
  4. Estimated rows vs real count = staleness
  5. Constraint-backing indexes are not droppable

basics

~20 s

Join the engine's native catalog: object tables for names and ownership, size functions or storage columns for bytes, index-usage counters for scans since reset, and statistics catalogs for row estimates, distinct values and the last analyze time. The standard views cannot answer any of these.

solid answer

~50 s

All three questions are physical, so they live in the **native** catalog, not `INFORMATION_SCHEMA`. - **Unused indexes**: engines keep cumulative per-index counters (scans, reads) in a statistics catalog. Join the index catalog to those counters, exclude constraint-backing indexes, and look for near-zero scans. Two caveats: counters reset on restart or manual reset, and they are per node — an index unused on the primary may serve a replica's reporting queries. - **Space**: object-size functions or storage columns give table bytes, index bytes and total including out-of-line storage. Rank by total to find what actually grew. - **Stale statistics**: the statistics catalog exposes estimated row counts, distinct values, null fraction, most-common values, and a last-analyzed timestamp. Compare estimated rows to an actual count, and compare last-analyzed to recent write volume. These queries belong in a runbook or dashboard, cached, not on a request path — they read wide catalogs and can be expensive on large schemas.

code

sql · 10 lines
sql
SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_relation_size(s.indexrelid) AS index_bytes
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique          -- never drop constraint-backing indexes
  AND NOT i.indisprimary
ORDER BY index_bytes DESC;

go deeper

for a junior

Know that the catalog holds more than names — sizes, index usage and statistics — and that you query it with SELECT.

for a middle

Name the three families of metadata (object, size, statistics), write the shape of an unused-index query, and state that this is engine-specific.

for a senior

Lead with the caveats: counter reset windows, replicas, constraint-backing indexes, allocation versus live data, and staleness as a cause of plan regressions.

for a principal

Position it as observability policy — which catalog signals are trended, what thresholds trigger action, and how index and statistics hygiene are governed rather than done ad hoc.

## Why these questions are native-only `INFORMATION_SCHEMA` describes the logical database. Bytes on disk, how often an index was scanned, and when statistics were last gathered are all physical facts with no standard vocabulary. Every one of these queries is engine-specific — the *shape* transfers, the SQL does not. ## Which indexes are unused Engines maintain cumulative counters per index: number of scans/seeks, tuples read, tuples fetched. A PostgreSQL-style engine exposes `pg_stat_user_indexes.idx_scan`; SQL Server has `sys.dm_db_index_usage_stats` with seek/scan/lookup/update counts; Oracle requires explicitly enabling index monitoring. The query is a join from the index catalog (to get name, table, definition, uniqueness) to the usage counters, filtered to near-zero scans. Three traps make naive answers wrong: 1. **Counters are cumulative since a reset point** — a server restart or an explicit statistics reset. An index that looks unused may simply be younger than your observation window. Record the reset timestamp and require weeks of uptime, ideally a full business cycle including month-end reporting. 2. **Per-node counters.** Read replicas keep their own. An index dropped because the primary never used it can wreck the replica that serves analytics. 3. **Not all indexes exist to be scanned.** Unique indexes enforce constraints, and an index may back a primary key or a foreign key check. Zero scans does not mean droppable. Exclude constraint-backing indexes explicitly. The payoff for removing genuinely dead indexes is real: every index costs write amplification on every insert/update, extra space, extra work for background cleanup, and longer maintenance windows. ## Space accounting Size questions need three numbers per table: the heap/main storage, the sum of its indexes, and any out-of-line storage for large values (TOAST-style overflow, LOB segments). PostgreSQL exposes `pg_total_relation_size` (everything), `pg_relation_size` (main fork only) and `pg_indexes_size`; SQL Server uses `sys.dm_db_partition_stats`; Oracle uses `DBA_SEGMENTS`. Two interpretation notes. First, size in the catalog is **allocated** space, not live data — a table that is 40% dead versions still reports the full allocation, which is exactly how you detect bloat by comparing allocation to expected row count times row width. Second, indexes frequently exceed the table itself; ranking by *total* size rather than heap size is what shows where the disk actually went. ## Statistics freshness The optimizer plans on estimates drawn from a statistics catalog: an estimated row count per relation, and per column the fraction of nulls, number of distinct values, most-common values and their frequencies, and a histogram. Engines also record when statistics were last gathered and how much has changed since. Stale statistics are a classic cause of sudden plan regressions: after a bulk load the optimizer still believes the table has 1,000 rows, picks a nested loop, and the query that used to take 50 ms now takes 20 minutes. Detect it two ways — compare the catalog's estimated row count against a real `COUNT(*)` on a suspect table, and compare the last-analyzed timestamp against the write counters (rows inserted/updated/deleted since then). A table whose row count has doubled since the last analyze is a candidate regardless of the timestamp. Also remember statistics describe *distribution*, not just volume. A column whose value skew changes — a new tenant with 80% of the rows — invalidates plans even when row counts look stable. ## Turning this into practice Build these as a small set of saved queries: - **Top 20 objects by total size**, trended weekly, so growth is visible before disk alerts fire. - **Candidate unused indexes**, filtered to constraint-free indexes on tables above a size threshold, with uptime/reset age printed alongside so nobody drops on a two-day sample. - **Statistics staleness**, listing tables where modifications since last analyze exceed a percentage of estimated rows. Run them from a monitoring job or a runbook, not from application request paths. On a database with many objects these queries scan wide catalogs and are not cheap; cache their results and refresh on the order of minutes to hours, because the underlying facts change slowly. Finally, treat catalog numbers as evidence, not verdicts. 'Estimated rows' is an estimate; 'index scans' is a counter with a window; 'size' is allocation. Each is a strong signal that should be confirmed before you drop an index or rebuild a table.

  • An index shows zero scans over 30 days. Is it safe to drop?
    Not automatically. Check that the counters were not reset by a restart inside that window, that no read replica uses the index for reporting, and that the index does not enforce a unique constraint or back a primary or foreign key. Also consider seasonal workloads such as month-end or annual reporting. A safer sequence is to make the index invisible or disabled where the engine supports that, observe, and only then drop.
  • A table's catalog size is 80 GB but you expect about 20 GB of live data. What does that suggest and how do you confirm it?
    It suggests bloat — space allocated to dead row versions and empty index pages that has not been reclaimed. Confirm by comparing estimated live rows times average row width against the allocated size, and by checking dead-tuple estimates and the last cleanup timestamp in the statistics catalog. If a long-running transaction is holding back the cleanup horizon, no amount of maintenance will shrink it until that transaction ends.

saying these in an interview costs you the question

  • Trying to get table size or index usage from INFORMATION_SCHEMA
  • Dropping a unique or primary-key index because its scan counter is zero
  • Ignoring that usage counters reset on restart and are per node
  • Treating catalog row estimates as exact counts
  • Running heavy catalog introspection queries on the application request path

context

resolving links…