Productivity Workspace Document Store Indexing Checklist

Role: Staff Database Reliability Engineer for High-Throughput Productivity Applications.

Context

• Workspace platform: {{workspace_platform}}
• Document database engine: {{database_engine}}
• Primary query patterns: {{primary_query_patterns}}
• Maximum acceptable p95 latency: {{target_p95_latency_ms}}
• Horizontal partitioning key: {{partition_strategy}}
• Dedicated index storage ceiling: {{max_storage_budget_gb}}

Task

Deliver an operational indexing and query optimization checklist for database engineers managing {{workspace_platform}}, ensuring index overhead stays within {{max_storage_budget_gb}} while meeting a {{target_p95_latency_ms}} latency target on {{database_engine}}.

Method

1. Analyze current execution plans (EXPLAIN / ANALYZE) for {{primary_query_patterns}} to identify full collection scans.
2. Audit existing secondary and compound indexes on {{database_engine}} to detect unused or duplicate indexes.
3. Evaluate index cardinality and prefix ordering to optimize compound index prefix reuse.
4. Measure the write-amplification impact of candidate indexes during peak collaborative editing spikes.
5. Validate partition distribution along {{partition_strategy}} to prevent query scatter-gather bottlenecks across shards.
6. Verify partial/sparse index feasibility for optional metadata fields to conserve memory.
7. Test memory residency of active working sets and cache hit ratios against {{max_storage_budget_gb}} limits.

Constraints

• MUST specify the metric or command used to confirm each checklist step.
• MUST NOT exceed {{max_storage_budget_gb}} across combined primary and secondary indexes.
• All index recommendations must directly support {{primary_query_patterns}}.
• Keep steps strictly operational and applicable to {{database_engine}}.

Output format

1. Performance Baseline Summary

• Structured table listing target latencies, shard key, and storage limits.

2. Query Plan & Scan Detection Checklist

• 3-4 markdown checklist items [ ] focused on index utilization and scan elimination.

3. Index Structure & Compaction Checklist

• 4-5 markdown checklist items [ ] focused on compound fields, partial indexes, and memory footprint.

4. Partitioning & Shard Alignment Checklist

• 3-4 markdown checklist items [ ] focused on {{partition_strategy}} routing and cross-shard queries.

Self-review

• Confirm that the latency target of {{target_p95_latency_ms}} is integrated into the validation criteria.
• Ensure each section includes markdown checkboxes [ ] with explicit measurement commands.
• Verify that write overhead and storage limits are addressed alongside read latency.