Would you partition this table by user_id?
Whether the candidate treats partitioning as a cardinality budget rather than as a rule, and — more importantly — whether they ask anything before answering.
The situation behind the question
Interviewers ask this because it happened to them.
An events table is queried mostly by date range and occasionally for a single user. An engineer proposes partitioning by user_id, reasoning that queries filter on it. The table will grow to hold every user the product has ever had.
A strong answer
Flags
Green flags
- Asks what the query pattern is and what the cardinality is before giving an answer. The questions are the answer.
- Reasons about partition count, file count and metadata cost, not just about pruning.
- Distinguishes partitioning from clustering and knows which one handles a selective high-cardinality predicate.
- Notices that the decision is made once at write time and is expensive to reverse, so it deserves more care than a query rewrite.
Red flags
- "Partition by every commonly queried column." Each partition column multiplies the partition count, and a partition set large enough to be expensive to list costs more than the pruning saves.
- Answers yes or no immediately without asking about cardinality, query mix or volume.
- Believes more partitions always means more pruning, with no notion of a metadata cost.
- Cannot describe what would have to change for the opposite answer to be right.
Follow-ups
Where the conversation goes if the first answer holds up.
- You inherit the table already partitioned by
user_id. What is the migration, and how do you run it without a period where consumers read a half-written table? - The query pattern is genuinely per-user and latency-sensitive. Is an analytical table the right home for it at all?
- How would you detect that a partition key has become a problem, before somebody complains that queries are slow?