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.

EasyTechnical

47 practiced

Describe a partial (filtered) index and give a practical BI example where you would use a filtered index on a large transactions table to accelerate common queries while reducing index size and maintenance costs.

Sample Answer

A partial (filtered) index is an index built over only the subset of table rows that satisfy a WHERE condition. It saves space and maintenance work because the database stores and updates fewer index entries while still giving fast lookups for queries that match the filter.

Practical BI example:Suppose you have a large transactions table (hundreds of millions of rows) with columns: id, customer_id, amount, status, created_at. Most dashboards and KPIs query recent, settled transactions only (status = 'settled' and created_at >= current_date - interval '90 days').

Create a partial index in PostgreSQL to accelerate those queries:

sql

CREATE INDEX idx_txn_settled_recent_customer
ON transactions (customer_id, created_at)
WHERE status = 'settled' AND created_at >= now() - interval '90 days';

Why this helps:- Queries that filter on status = 'settled' and recent date can use the index, making dashboard aggregations and drill-downs much faster.- Index size and write overhead are much smaller than a full-table index because only recent settled rows are indexed.- Maintenance cost (vacuuming/reindexing) is reduced.

Considerations:- Ensure query predicates match the partial index WHERE clause exactly (or are logically implied).- Keep the filter aligned with business usage window (e.g., 90 days); adjust as access patterns change.- For very dynamic time windows, consider rolling indexes or partitioning combined with partial indexes.

MediumTechnical

44 practiced

When would a bitmap index be beneficial in a data warehouse environment? Contrast its behavior with a B-tree index for a low-cardinality column such as 'gender' or 'region' and discuss update concurrency and storage trade-offs.

HardSystem Design

50 practiced

Given an OLTP system that occasionally serves analytical reports, decide whether to index foreign key columns to speed joins for reporting versus relying on nightly ETL into a reporting replica. Build a simple cost model comparing additional write cost per transaction (in microseconds) to expected read latency savings and recommend a strategy.

Sample Answer

Requirements & constraints- OLTP primary handles transactional workload; occasional analytical joins on FK-heavy tables for ad-hoc reports.- Goal: decide between (A) adding indexes on foreign-key (FK) columns in OLTP or (B) relying on nightly ETL into a reporting replica for joins.- Metrics: additional write cost per transaction (μs), read latency savings for report queries (ms), freshness tolerance, operational complexity.

Simple cost modelDefine:- W = transactions/sec on OLTP- Δw = extra write cost per transaction if FK indexed (μs)- R = report queries/day- S = average read latency saved per report if using indexed OLTP vs no-index (ms)- F = freshness window tolerated (hours)- C_maint = operational cost (qualitative)

Compute daily totals:- Daily write overhead (A): OW = W * Δw * 86,400 μs -> convert to seconds or ms as needed- Daily read savings (A vs no-index): RS = R * S msBreak-even condition: OW (ms/day) <= RS (ms/day)

Example (realistic assumptions)- W = 500 tx/s, Δw = 50 μs → OW = 500 * 50 * 86,400 = 2,160,000,000 μs/day = 2,160,000 ms/day- R = 10,000 reports/day, S = 300 ms saved → RS = 3,000,000 ms/dayResult: OW (2,160,000 ms) < RS (3,000,000 ms) → indexing wins on raw latency trade-off.

Qualitative factors to include- Freshness: If reports need near-real-time (<1 hr), indexing OLTP favors lower staleness; if nightly is acceptable, ETL is fine.- Write amplification & contention: Indexes increase IO, possible blocking, higher tail latencies; heavy write workloads may degrade OLTP SLA.- Operational complexity: ETL adds pipelines, schema mapping, data validation, and storage/compute for replica; but isolates reporting load.- Query patterns: If reports mostly join small dimension tables to large facts, indexing FK on fact gives large win. If analytical joins across many large tables, a columnar replica (ETL) likely superior.- Cost/scale: At high W (millions tx/s) small Δw multiplies; consider partial indexes, covering indexes, or read replicas with real-time CDC.

Recommendation (BI perspective)- If freshness requirement is lenient (nightly or hourly) and you have many/complex analytical queries, prefer nightly ETL to a reporting replica (better read performance, simpler dashboard SLAs, offloads OLTP).- If reports require near-real-time and expected report volume produces read savings > write overhead (use the model with your actual W,R,Δw,S), add targeted FK indexes (or partial/covering indexes) on reporting-critical joins, monitor write latency and tail percentiles, and consider hybrid: maintain ETL for heavy historical analytics and keep selective indexes for near-real-time dashboards.

EasyTechnical

38 practiced

List the common index types a BI analyst should know (B-tree, hash, bitmap, full-text, functional/expression) and provide a one-sentence description plus a typical BI use-case for each (e.g., date range filters, equality joins, low-cardinality filters, product-descriptions search, case-insensitive joins).

EasyTechnical

48 practiced

Explain the difference between clustered and nonclustered indexes and describe three situations a Business Intelligence Analyst should prefer a clustered index versus a nonclustered index when modeling reporting schemas and OLAP queries. Include implications for storage, range scans, and sort operations.

Sample Answer

Clustered vs nonclustered index — short definition:- Clustered index: physically orders table rows by the index key (table is stored in that order). One clustered index per table.- Nonclustered index: separate structure containing key values and pointers (row locators) to the actual rows; can have many per table.

Key implications:- Storage: clustered index stores data inline (no extra pointer), so the table is the index; nonclustered stores duplicates of key columns + pointer → extra storage and maintenance cost.- Range scans: clustered indexes excel at range scans because adjacent key values are stored contiguously on disk (fast sequential I/O). Nonclustered range scans are slower if they require jumping to many row locators.- Sort operations: queries needing ordered output on clustered key often avoid explicit sort (ORDER BY covered by index). Nonclustered can also avoid sort if it exactly matches the index, but may require lookups or additional sorting.

Three BI-focused situations preferring clustered index:1) Time-series fact tables (timestamp/date as clustered key): BI queries often filter or aggregate over time ranges (last 7 days, monthly). Clustered ordering yields efficient range scans, compact storage for sequential writes, and avoids sorts for time-ordered reports.2) Large append-only event/fact tables where most queries select contiguous blocks (e.g., customer_id + event_time with clustering on customer_id then time): reduces I/O for per-customer trends and improves cache locality.3) When supporting ORDER BY or GROUP BY on a frequently used dimension key in key dashboards (e.g., product_id then sales_date): clustered index can eliminate costly sort operations and speed dashboards with heavy aggregations.

When to prefer nonclustered index:- Dimension tables or lookup queries needing many different access patterns (search by name, code): nonclustered indexes let you create multiple access paths with less impact on physical row order.- Wide tables where clustering by a nonselective column would bloat storage and increase maintenance; putting a narrow key as nonclustered index reduces storage overhead.

Best practice for BI: cluster on the most common range-filtered or ordering key (often date/time or a natural partitioning key); add nonclustered covering indexes for high-cardinality selective filters used by dashboards to avoid lookups.

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.