Indexing Strategy and Selection Questions

Covers index design principles and practical selection of indexes to accelerate queries while managing storage and write cost. Topics include index types such as B tree hash and bitmap indexes and full text and functional indexes; single column composite and covering indexes; clustered versus nonclustered index architectures and partial or filtered indexes. Candidates should reason about index selectivity and cardinality and how statistics and histograms influence optimizer choices. Also assess index maintenance overhead fragmentation and rebuild strategies and the trade off between faster reads and slower inserts updates and deletes. Practical skills include reading execution plans to identify missing or inefficient indexes proposing index consolidation or covering index designs testing and benchmarking index changes and understanding interactions between indexing partitioning and denormalization.

HardTechnical

43 practiced

As a senior data engineer, design an indexing governance policy for a large organization: include review process for adding indexes, metrics to require (usage, maintenance cost), CI checks, naming conventions, and an approval workflow. How would you enforce and measure compliance?

Sample Answer

Situation: At scale, uncontrolled indexing caused storage bloat, slow writes, and maintenance overhead across OLTP and analytical systems.

Policy overview (goal): Ensure indexes improve query performance enough to justify storage, write cost, maintenance, and operational complexity. Apply to all production schemas and data marts.

Review process for adding indexes:- Proposal ticket in tracking system including: motivation, example queries, expected QPS, schema, estimated index size, and rollback plan.- Run a staging validation: reproduce queries on snapshot of prod data and measure impact.- Required signatures: Requester (owner), DB custodian, affected service owner, and performance SME.

Required metrics (collected pre/post and in CI):- Usage: number of seeks/scans, hit rate, top queries served by index (per day/week).- Benefit: query latency and CPU reduction (ms and %), reduction in full table scans.- Maintenance cost: index build time, average write amplification (writes/sec increase), storage bytes.- Risk: estimated locking impact, replication lag delta.

CI checks:- Linting: enforce naming convention and forbidden patterns (e.g., unbounded composite keys).- Automated simulation: run proposed index DDL on a sampled dataset, execute representative query suite, produce metrics diff.- Safety gates: reject index DDL that increases avg write amplification > X% or storage > Y GB without higher approval.

Naming conventions:- Format: idx_{schema}_{table}__{cols}_{type}_{unique?} Example: idx_sales_orders__customer_id_created_at_bt (bt=b-tree)- Include creation ticket ID and owner in metadata/comments.

Approval workflow:1. Create ticket with proposal + cost estimate.2. Automated CI runs; attaches metrics report.3. Approval tier 1: DB custodian (for small-impact indexes).4. Approval tier 2: Architecture review board + service owner (for high-cost or cross-team).5. Schedule deployment window and rollback plan; post-deploy monitoring.

Enforcement & measurement:- Policy enforced via CI gates blocking PR merges that add indexes without ticket ID or failing safety checks.- Periodic audits: automated job scans schema and compares existing indexes vs. approved registry; flags unapproved indexes.- Compliance metrics exposed on dashboard: % approved indexes, orphan/unused indexes (no usage in 90 days), cumulative index storage, avg write amplification.- Quarterly index review meetings to drop unused indexes; require re-approval for re-creation.- Alerting: when an index causes replication lag or write latency beyond thresholds, auto-notify owners and create incident.

This approach balances developer agility with operational safety—automated checks and clear metrics keep decisions data-driven while approvals manage risk and cross-team impact.

MediumTechnical

40 practiced

Design an approach to detect and remove redundant indexes automatically. Describe the heuristics you would use (overlapping prefixes, same included columns), queries to find duplicates, safe removal checks, monitoring after removal, and CI gates to prevent reintroduction.

Sample Answer

Overview: automate detection + safe removal in a pipeline: (1) detect candidate redundant indexes via heuristics, (2) verify safety with usage & cost checks, (3) schedule staged removal with monitoring, (4) block CI reintroductions.

Heuristics:- Exact duplicates: same key columns order + same INCLUDEs.- Overlapping prefixes: index A columns = (a,b) and B = (a,b,c) — A may be redundant if A not used and B covers all queries.- Included columns equality: same keys but different INCLUDEs where one INCLUDE set ⊇ other.- Low usage & high maintenance: indexes with near-zero reads but high write cost (measured by r/w counters).

Queries to find candidates (Postgres-style):

sql

-- exact duplicate keys + include comparison (example)
SELECT i1.indexrelid::regclass AS idx1, i2.indexrelid::regclass AS idx2
FROM pg_index i1
JOIN pg_index i2 ON i1.indkey = i2.indkey AND i1.indexrelid < i2.indexrelid
WHERE i1.indisvalid AND i2.indisvalid;

sql

-- overlapping prefix heuristic
SELECT a.indexrelid::regclass AS smaller, b.indexrelid::regclass AS larger
FROM my_index_columns a
JOIN my_index_columns b ON b.table = a.table
WHERE a.columns = left(b.columns, length(a.columns)) AND a.columns != b.columns;

Safe-removal checks:- Verify index usage metrics over long window (30–90 days): scans, last_used, planner_usage.- Run EXPLAIN ANALYZE of sampled production queries pre/post in a staging clone to ensure no plan regressions and latency increase threshold (e.g., <5%).- Ensure no constraint depends on the index (unique/primary).- Schedule removal in low-traffic window and keep fast rollback plan (recreate DDL).

Monitoring after removal:- Short-term: track latency, error rates, query plans for top N queries; alert on threshold breaches.- Medium-term: monitor replica lag, write latency, CPU, and index rebuild times.- Auto-rollback trigger if SLA or key query p95 increases beyond set thresholds.

CI gates to prevent reintroduction:- Enforce index DDL review in PRs with automated linter: detect re-adding known-removed indexes or creating duplicates/overlaps and block merge.- Add a test that runs schema-diff against canonical schema and queries the index catalog for forbidden patterns.- Add cost estimation check in CI: new index must show expected benefit on sampled query workload or have approved JIRA.

Implementation notes:- Start conservative: flag -> staging test -> manual approval -> automated rollout.- Maintain an index metadata registry (reason, owner, created_at, removal date) so ownership/intent is recorded.

MediumTechnical

45 practiced

You observe index-only scans are not occurring though a covering index exists. List reasons why index-only scans might not be used (visibility map, deleted tuples and VACUUM, versioning) and the actions to enable index-only scans in Postgres.

Sample Answer

Short answer: index-only scans require (1) a truly covering index (all needed columns present), and (2) the heap tuple visibility bits must be set in the visibility map so Postgres can trust that no heap fetch is needed. If index-only scans are not happening, common causes are: stale/missing visibility map bits (due to insufficient VACUUM), recently updated/deleted tuples still requiring heap checks (MVCC/versioning), planner cost choices, or the index not actually covering all projected expressions (including predicates, computed cols, or required TOASTed data).

Reasons and actions

- Visibility map bits not set - Why: VACUUM sets the visibility map for pages with only visible tuples. Without them Postgres must check heap. - Action: run VACUUM (or rely on autovacuum) on the table; for immediate effect use VACUUM VERBOSE to confirm pages marked. Ensure autovacuum is tuned (frequency/scale) for the workload.

- Deleted/updated tuples and MVCC/versioning - Why: Recent updates create new versions; until a tuple is frozen/cleaned the heap must be checked. - Action: VACUUM to remove/reconcile dead tuples; consider VACUUM FREEZE for very old tables; increase autovacuum aggressiveness on high-update tables; design for HOT updates when possible to avoid extra index entries.

- Index is not truly covering - Why: Query needs a column or expression not present in the index (or needs non-storable functions/TOASTed values), forcing heap fetch. - Action: Create a covering index (include needed columns via INCLUDE(...)) or adjust the query to only use indexed columns/expressions.

- Planner chooses seqscan due to cost estimates - Why: outdated statistics or misestimated selectivity/costs. - Action: run ANALYZE; check pg_stat_all_tables and EXPLAIN ANALYZE; adjust work_mem, random_page_cost, or statistics targets if necessary.

- TOASTed/large values and non-storable constructs - Why: TOASTed columns may not be retrievable from index; certain expressions/functions prevent index-only usage. - Action: store frequently queried fields un-toasted if small; include computed expressions in index; rewrite query.

Checks and tools- EXPLAIN (ANALYZE, VERBOSE) to see whether index-only was considered and why not.- VACUUM VERBOSE and pgstattuple extension to inspect visibility map / dead tuple stats.- Monitor autovacuum and tune autovacuum_vacuum_scale_factor / threshold for hot tables.

Summary: ensure a true covering index, keep visibility map bits up to date via VACUUM/autovacuum (or VACUUM FREEZE when appropriate), maintain small TOASTable columns or INCLUDE them, and keep statistics tuned so the planner picks index-only scans.

MediumTechnical

50 practiced

For a table with a computed column (e.g., lower(email) or hashed value), explain benefits of functional (expression) indexes. Provide sample SQL to create an index on lower(email) in Postgres and describe when such an index eliminates the need for expression evaluation at runtime.

Sample Answer

Approach: Explain why functional (expression) indexes are useful for queries that filter or join on computed values (e.g., case-insensitive email lookup), show the Postgres DDL, and describe when the planner uses the index so the database doesn't need to re-evaluate the expression at runtime.

Sample SQL (Postgres):

sql

-- Create an expression (functional) index for case-insensitive email lookups
CREATE INDEX idx_users_lower_email
  ON users (lower(email));

-- Example query that will use the index:
SELECT id, email
FROM users
WHERE lower(email) = 'alice@example.com';

Why this helps- Matches queries that use the exact same expression (lower(email)). The index stores the evaluated expression, so Postgres can search the index instead of computing lower(email) for every row.- Speeds up WHERE, JOIN, ORDER BY, and GROUP BY when they refer to the expression.- Reduces CPU work (no per-row function calls) and I/O when the index supports an index-only scan.

When the index eliminates runtime expression evaluation- The query must use the identical expression text (including function and argument order): WHERE lower(email) = 'x' matches an index on lower(email). If you use an equivalent but syntactically different expression, the index won't be used.- The function should be immutable or at least not volatile; Postgres requires predictable results — functions marked IMMUTABLE or STABLE are appropriate. If locale/collation affects lower(), be aware of behavior differences.- If the planner can use an index-only scan (index contains all needed columns and tuple visibility is known), Postgres can avoid fetching heap rows entirely — no expression re-evaluation and very low I/O.- If the index supports the scan (e.g., B-tree for equality/range), the planner will rely on it instead of sequentially evaluating the expression across rows.

Edge alternatives and best practices- Use citext type for case-insensitive text comparisons (avoids expression on every query).- Persist computed values as a generated column (CREATE TABLE ... GENERATED ALWAYS AS (lower(email)) STORED) and index that column if you prefer materialization.- Ensure statistics and ANALYZE are up-to-date so the planner picks the index appropriately.

Performance considerations- Expression indexes cost extra storage and write overhead on INSERT/UPDATE (the expression must be computed on write).- They cut query CPU for frequently-used computed predicates and can enable index-only scans for very fast lookups.

MediumTechnical

42 practiced

Explain prefix and hash indexes for very long string keys (e.g., UUIDs or long URLs). When is it appropriate to index a hash of a column rather than the full column, and what are the potential pitfalls (collisions, inability to support range scans)?

Unlock Full Question Bank

Get access to hundreds of Indexing Strategy and Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.