How do you keep queries fast on a table with billions of rows?
⚡ Short Answer
Use partitioning (range by date / hash by key) so queries prune to one partition and old data is dropped cheaply; archive/purge cold data; index for the dominant access pattern; consider summary tables for aggregates; and shard across nodes only when a single DB can't keep up.
☕Coffee Chat Question
Concept Made Simple
“How do you keep queries fast on a table with billions of rows?”
🧠Mind Map Answer
Remember It Faster
🔥What If?
Think Beyond the Expected
Why is dropping old data via partitions far better than DELETE on a huge table?
DROP/TRUNCATE PARTITION removes a whole chunk as a metadata operation — near-instant, minimal logging, no bloat. A DELETE on billions of rows logs every row, holds locks, and leaves dead tuples needing vacuum/rebuild. Date-range partitioning makes retention a one-line drop.
😂Real World
Time-series / event / audit tables are range-partitioned by date so reads prune to recent partitions and retention is enforced by dropping old partitions — standard practice at scale before reaching for sharding.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗DELETE-ing huge ranges instead of dropping partitions
- ✗Partition key that doesn't match query filters (no pruning)
- ✗Sharding prematurely before partitioning/indexing
✅Best Practices
- ✓Partition on the dominant filter (often date)
- ✓Use DROP PARTITION for retention
- ✓Exhaust partitioning/indexing before sharding
🔁Follow-up Questions
- 1Range vs hash vs list partitioning — when each?
- 2What is partition pruning and how does the planner use it?
- 3When do you move from partitioning to sharding?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Optimization (SQL) Interview question: "How do you keep queries fast on a table with billions of rows?" Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.