HardExperience level: 8-15 years1 min read

How do you keep queries fast on a table with billions of rows?

Reviewed by Gurusankar M.

Asked inAmazonGoogleMicrosoft
#partitioning#archiving#large table#scalability#sharding
Report issue

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.

On this page

Concept Made Simple

Coffee Chat Question

“How do you keep queries fast on a table with billions of rows?”

Remember It Faster

Mind Map Answer

Partitioning
prune to relevant partition (date/hash)
Archiving
move/drop cold data; DROP PARTITION is fast
Indexes
tuned to dominant queries
Sharding
scale-out when one node isn't enough

Think Beyond the Expected

What If?

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:

  • partition pruning
  • range vs hash partitioning
  • DROP PARTITION for retention
  • index for access pattern
  • shard as last resort

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

  1. Range vs hash vs list partitioning — when each?
  2. What is partition pruning and how does the planner use it?
  3. When do you move from partitioning to sharding?

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.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.