Have You Designed Query Performance—or Just Hoped the DB “Handles It”?¶
Type: DeepDive
Category: Performance
Audience: Engineers dealing with growing tables, slow dashboards, or unexplained latency
🔍 What This Perspective Covers¶
This is not just about “adding indexes.”
It’s about designing for how queries behave under growth and concurrency.
This touches:
- Index choice and placement
- Query patterns (joins, filters, pagination)
- Read amplification and disk cost
- Cache invalidation and fallback paths
⚠️ What Goes Wrong in Practice¶
- Latency spikes on pages nobody thought were critical
- Multiple indexes fighting over the same table
- Joins optimized for logic, not I/O locality
- Filters on low-cardinality fields that kill index usefulness
- No clear plan for data aging or pruning
✅ Healthier Performance Thinking¶
- Design queries from access pattern—not schema shape
- Monitor “query heat” over time, not just in load tests
- Write-specific indexes for read-heavy endpoints
- Embrace partial indexes or filtered indexes where applicable
- Plan index lifecycle: create, evolve, retire
🧠 Core Tradeoffs¶
You’re always balancing:
- Storage vs speed
- Index maintenance cost vs query benefit
- Latency variance vs code complexity
❓ FAQ¶
-
Q: What if I don’t know which queries matter yet?
A: Then build for observability first—optimize later. -
Q: Can’t the ORM optimize this for us?
A: It can write queries. It can’t make them good.