Compare Primary Keys, Primary Indexes, and Secondary Indexes
Quick Overview
Distinguish primary-key constraints from primary and secondary indexes, and assess storage cost, write amplification, selectivity, and composite-index order.
Compare Primary Keys, Primary Indexes, and Secondary Indexes
Company: Squarepoint
Role: Software Engineer
Category: Software Engineering Fundamentals
Difficulty: medium
Interview Round: Technical Screen
Explain the difference between a primary key, a primary index, and a secondary index. What are the disadvantages of adding indexes, and how would you decide whether an index is useful for a workload?
### Constraints & Assumptions
Index terminology and physical storage differ by database engine. State the engine assumptions instead of treating a primary key as universally synonymous with clustered storage.
### Clarifying Questions to Ask
- Which database engine and storage organization are in use?
- Are the important operations point lookups, range scans, joins, or frequent writes?
```hint Separate a constraint from an access path
Uniqueness and row identity are logical rules; an index is a structure used to find rows efficiently.
```
### What a Strong Answer Covers
- Primary-key uniqueness/non-null identity and its enforcement.
- Engine-dependent primary or clustered indexing versus additional access paths.
- Write amplification, storage, maintenance, and low-selectivity trade-offs.
### Follow-up Questions
- Why can a table scan be cheaper than using a secondary index?
- How does a composite index's column order affect range queries?
Overview: Distinguish primary-key constraints from primary and secondary indexes, and assess storage cost, write amplification, selectivity, and composite-index order.
Explain the difference between a primary key, a primary index, and a secondary index. What are the disadvantages of adding indexes, and how would you decide whether an index is useful for a workload?
Constraints & Assumptions
Index terminology and physical storage differ by database engine. State the engine assumptions instead of treating a primary key as universally synonymous with clustered storage.
Clarifying Questions to Ask Guidance
Which database engine and storage organization are in use?
Are the important operations point lookups, range scans, joins, or frequent writes?
What a Strong Answer Covers Guidance
Primary-key uniqueness/non-null identity and its enforcement.
Engine-dependent primary or clustered indexing versus additional access paths.
Write amplification, storage, maintenance, and low-selectivity trade-offs.
Follow-up Questions Guidance
Why can a table scan be cheaper than using a secondary index?
How does a composite index's column order affect range queries?