Indexes and Query Patterns
Learn how indexes support read patterns, why they cost writes and storage, and how query-first thinking prevents slow systems.
What you will be able to do
An index is a prepared path to find data faster. Without the right indexes, even a correct data model can become too slow for production.
Indexes Start With Queries
Do not create indexes randomly. First list the screens, APIs, jobs, and reports that read data. Each important query pattern should have a deliberate access path.
| Query pattern | Possible access path | Trade-off |
|---|---|---|
| Get order by id | Primary key index | Fast and simple. |
| List orders by user and time | Composite index on userId, createdAt | Extra write and storage cost. |
| Search products by text | Search index | Separate freshness and sync complexity. |
| Show dashboard counts | Materialized summary | Must update summary correctly. |
| Fetch hot product page | Cache | Invalidation and stale data risk. |
Scenario: Notification Inbox
A notification inbox needs to list recent notifications, filter unread items, mark items as read, and search older notifications. Each access pattern asks for a different path.
| Feature | Query | Index/design |
|---|---|---|
| Recent inbox | userId ordered by createdAt desc | Composite index or partition by user. |
| Unread count | userId + read=false | Index or maintained counter. |
| Mark as read | notificationId and userId | Primary key plus ownership check. |
| Search old notifications | text query | Search index, not normal B-tree only. |
| Cleanup old items | createdAt older than retention | TTL/retention job or partitioned cleanup. |
Index Trade-Offs
Indexes are not free. Every extra index can make writes slower and storage larger. In distributed databases, poor partition/index choices can create hot spots.
| Benefit | Cost |
|---|---|
| Faster reads | Slower writes because index must update. |
| Better sorting/filtering | More storage. |
| Fewer full scans | More schema/index planning. |
| Predictable API latency | Index migration and backfill complexity. |
| Search-like behavior | Freshness and synchronization complexity if using separate search store. |
Design principle
A query pattern without an access path is a future incident. A new index without a query pattern is future waste.
Cloud Indexing Options
| Need | AWS | GCP | Azure |
|---|---|---|---|
| Relational indexes | RDS/Aurora indexes | Cloud SQL/Spanner indexes | Azure SQL indexes |
| Key-value access | DynamoDB primary key/GSI | Firestore indexes/Bigtable row keys | Cosmos DB indexing/partition key |
| Search | OpenSearch | Vertex AI Search/Elastic on GCP | Azure AI Search |
| Cache hot reads | ElastiCache | Memorystore | Azure Cache for Redis |
| Analytics scan | Athena/Redshift | BigQuery | Synapse/Fabric |
Index Review Checklist
- Name the exact query pattern.
- State expected read and write frequency.
- Check filter fields and sort order.
- Identify whether query is point lookup, range scan, search, or aggregation.
- Estimate index storage and write overhead.
- Plan migration/backfill for large existing data.
Beginner Mistakes
- Assuming an index on every field is a good idea.
- Ignoring sort order in list APIs.
- Using offset pagination for large changing lists without understanding the cost.
- Expecting a relational index to behave like a full-text search engine.
- Forgetting that index changes on large tables need careful rollout.
Guided Practice
Practice task
For a support ticket system, list three query patterns and choose an index or access path for each.
Sample Answer
| Query | Access path |
|---|---|
| Get ticket by ticketId | Primary key. |
| List open tickets by team ordered by priority and createdAt | Composite index on teamId, status, priority, createdAt. |
| List requester ticket history | Index on requesterId and createdAt. |
| Search ticket text | Search index. |
| Dashboard counts by status | Materialized summary or analytics store depending freshness needs. |
Before You Continue
- You should map query patterns to access paths.
- You should understand that indexes trade write cost for read speed.
- You should know when search, cache, or materialized views may be better than a normal index.
- You are ready to learn transactions and consistency.