What is a database index and when should you add one?
Reported in Personio European engineering loops. SQL performance question covering B-trees, composite indexes, and trade-offs.
Interview scenario
Often asked in Personio loops at European offices (London, Berlin, Amsterdam, Paris, Stockholm, Dublin, and remote EU). Prepare a clear spoken answer plus key trade-offs.
Model answer
Try answering aloud first
Cover trade-offs, structure, and a concrete example before revealing the baseline response.
How to frame this at Personio: Connect your answer to measurable impact, clarity of thought, and trade-offs the team cares about. Below is a strong baseline response you can adapt with your own project examples.
An index is a separate data structure (usually B-tree or B+ tree) enabling fast lookup by key without full table scan. CREATE INDEX on columns used in WHERE, JOIN, ORDER BY.
Composite index column order matters—index (status, created_at) helps WHERE status = ? ORDER BY created_at but may not help filtering only on created_at (left-prefix rule).
Trade-offs: indexes accelerate reads but slow writes (maintain structure) and consume disk. Over-indexing hurts insert throughput.
Advanced: covering indexes include all selected columns (index-only scan), partial indexes for hot subsets, and EXPLAIN ANALYZE to verify planner choices. Mention when hash vs btree indexes apply.
Discussion
Comments (0)
Share how this question came up in your loop, or add tips for others preparing.
Log in to comment on this question.