System Design AI

Database Indexing: Follow-Up Questions Interviewers Ask, With Answers

Interviewers ask follow-up questions about indexing because a simple mention often hides many decisions: which columns, which type of index, and how the index will evolve as traffic grows. They want to see whether you understand the performance-cost trade-offs, not just that you can name B-tree or hash indexes.

A good answer demonstrates that you can quantify the benefit, anticipate maintenance overhead, and choose the right index for the right query pattern. Below are the nine typical probes, ordered from the most common to the hardest, each with a concise answer you could give in an interview.

Core Indexing Concepts

1. Which columns would you index for a user-profile lookup service?

I would create a primary key on user_id because every request fetches a single user record. Additionally, I would add a secondary B-tree index on email since we often need to locate a user by email for password-reset flows. In our prototype, a query that scans the table without an index took about 120 ms, while the indexed lookup drops to under 5 ms, a 20× improvement.

2. Why choose a B-tree index over a hash index for that query pattern?

B-tree indexes support range scans, which we need for queries like “find all users created after a given date.” A hash index would only help exact matches and would not allow ordering or pagination. In practice, the B-tree also gives predictable performance across a wide key distribution, whereas hash collisions could degrade to linear scans.

3. How does index selectivity affect performance?

Selectivity is the fraction of rows a predicate returns. High selectivity (e.g., 0.1 % of rows) means the index filters out most data, so the database can use the index efficiently. Low selectivity (e.g., 50 % of rows) forces the optimizer to fall back to a full table scan because reading the index plus the rows costs more than scanning the table once. For our email index, the selectivity is roughly 0.001 % because emails are unique, making the index very effective.

4. What is the impact of write-heavy workloads on indexing strategy?

Every write incurs extra work to keep indexes up to date. In a write-heavy scenario (e.g., 10 k inserts per second), each additional secondary index adds latency proportional to the index size. To mitigate this, I would limit indexes to those required for read paths, batch writes where possible, and move read traffic to replicas so the writer spends its capacity on writes and index maintenance.

5. How would you handle index maintenance for a table that grows to billions of rows?

I would enable automatic partitioning (e.g., by month) so each partition has its own index, keeping index size manageable. Periodic index rebuilds or REINDEX operations can be scheduled during low-traffic windows to defragment the B-tree and reclaim space. In our simulation, partitioning reduced index size per partition from 200 GB to under 20 GB, cutting index-scan latency by roughly 70 %.

6. When would you consider a covering index, and what are its trade-offs?

A covering index includes all columns needed by a query, allowing the database to satisfy the request from the index alone. For a frequent query that selects user_id, email, and last_login, a covering index on (email, last_login) eliminates the need to read the base table, cutting I/O by up to 80 % in our benchmark. The trade-off is larger index size and slower writes, so I only add covering indexes for hot read paths.

7. How do you decide between a single-column index and a composite index?

I examine query patterns. If most queries filter on email and sort by last_login, a composite index (email, last_login) serves both the filter and the order, avoiding a separate sort operation. However, if we also need to filter on status independently, a single-column index on status remains useful. In our case, the composite index reduced query time from 12 ms to 4 ms for the combined filter-sort pattern.

8. What are the implications of using a global secondary index in a distributed database like DynamoDB?

A global secondary index (GSI) replicates data across partitions, providing low-latency lookups on non-key attributes. However, GSIs consume additional write capacity units; each write to the base table also writes to the GSI. In our DynamoDB table handling 5 k writes per second, enabling a GSI on email required provisioning roughly 1.5× the base write capacity. Monitoring hot partitions is also critical because uneven key distribution can cause throttling.

9. How would you future-proof your indexing strategy for evolving query patterns?

I would adopt a modular approach: start with essential indexes, instrument query latency, and use a query-log analysis tool to detect new hot paths. When a new pattern emerges, I add a targeted index and measure impact before committing. Additionally, I keep index definitions in version-controlled migration scripts, allowing rollback if an index proves unnecessary. This iterative process lets the schema evolve without over-indexing.

Senior vs. Staff Answers on Indexing

A senior-level answer shows solid knowledge of index types, selectivity, and basic maintenance. It quantifies benefits with concrete numbers and mentions trade-offs like write overhead. A staff-level answer goes further: it anticipates long-term operational concerns, discusses partitioning, global secondary indexes, and presents a systematic process for monitoring and evolving the index strategy. Staff candidates also tie indexing decisions to overall system architecture, such as how the choice influences scaling, cost, and consistency guarantees.

How to practice this

  1. Review the worked design for a relational database in the System Design AI library; note the diagram flow and the notes attached to each component.
  2. On the canvas, sketch a typical user-profile service and add the primary key, secondary, and covering indexes you would choose.
  3. Use the AI coach to run through the nine follow-up questions, compare its feedback to your answers, and iterate until the explanations feel natural.

Questions people ask

What is the difference between a primary key index and a secondary index?

A primary key index uniquely identifies each row and is usually clustered, meaning the table data is stored in index order. A secondary index is an additional structure that points to rows based on non-key columns; it can be non-clustered and may require extra lookups.

Can I have too many indexes on a table?

Yes. Each index adds write latency and storage overhead. If write traffic is high, excessive indexes can degrade performance. Aim for indexes that directly support frequent read queries.

How do I choose between a B-tree and a GiST/GIN index in PostgreSQL?

B-tree is optimal for equality and range queries on scalar data. GiST/GIN are suited for full-text search, arrays, or geometric data. Choose based on the data type and query pattern.

What monitoring metrics should I watch for index health?

Track index size growth, index scan vs. table scan ratios, query latency, and write latency spikes. Tools like Amazon CloudWatch or PostgreSQL’s pg_stat_user_indexes provide these insights.