Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Index types: B-tree (default)—good for equality and range; Hash—equality only; Bitmap—low cardinality, data warehouses; GIN/GiST—full-text, JSON. Benefits: faster lookups, reduced full scans, faster joins, enforcement of uniqueness (unique index). Drawbacks: storage overhead,...
Index types: B-tree (default)—good for equality and range; Hash—equality only; Bitmap—low cardinality, data warehouses; GIN/GiST—full-text, JSON. Benefits: faster lookups, reduced full scans, faster joins, enforcement of uniqueness (unique index). Drawbacks: storage overhead, write slowdown. Best practice: create indexes on foreign keys and frequently filtered columns; use covering indexes (include columns in index) to avoid table lookups; analyze execution plans before/after. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.
Red Flag: Generic textbook answers. Pro-Move: 'At scale we measured X, implemented Y, achieved Z%—validated and iterated.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.