MongoDB Unused Index and Memory Efficiency Auditor

In high-throughput MongoDB environments, secondary indexes accelerate query filters but introduce significant performance trade-offs. Each index must be synchronously updated during every document insert, update, and delete operation. Furthermore, indexes reside in the WiredTiger storage cache; unused or redundant indexes consume valuable RAM that should otherwise cache active working sets.

Over time, legacy indexes from deprecated features or experimental queries linger unnoticed, degrading write throughput and ballooning database backup sizes.

This blueprint establishes an automated index governance audit for MongoDB. Running weekly or on-demand, it executes the $indexStats aggregation pipeline across your target collection to inspect historical operational hits (accesses.ops). When secondary indexes with zero access hits are identified, it dispatches an actionable Slack notification detailing the index key pattern and the exact dropIndex() statement needed to reclaim memory.

How it works

1. Scheduled Index Audit: The weekly_index_audit trigger (io.kestra.plugin.core.trigger.Schedule) executes every Monday at 05:00 UTC or on-demand via the Kestra UI.
2. Index Stats Aggregation: The audit_unused_indexes task (io.kestra.plugin.mongodb.Aggregate) runs $indexStats, excluding the mandatory primary _id_ index and filtering for indexes where accesses.ops is zero.
3. Evaluation Gate: The evaluate_unused_indexes flowable task (io.kestra.plugin.core.flow.If) branches based on whether any unused indexes were discovered.
4. Slack Alert Notification: If unused indexes are found, notify_dba_slack (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) broadcasts an alert with index key details, tracking start date, and the suggested dropIndex command.
5. Optimal Confirmation: When all indexes exhibit active query hits, log_optimal_indexes records nominal status in execution logs.
6. Audit Manifest: The export_audit_manifest task records telemetry outputs for DBA compliance tracking.

What you get

• Automated discovery of dead secondary indexes wasting WiredTiger cache memory.
• Improved write latency across high-velocity transactional collections.
• Pre-formatted dropIndex commands directly in Slack for safe operator review.
• Continuous schema hygiene without manual database inspections.

Who it is for

• MongoDB Database Administrators managing multi-terabyte production clusters.
• Backend engineers reviewing collection indexing strategies and query performance.
• Platform architects reclaiming memory across cloud-hosted database instances.

Why orchestrate this with Kestra

Conducting regular index audits manually across hundreds of collections is rarely performed consistently. Kestra provides declarative, serverless orchestration: it connects directly via native MongoDB drivers, schedules regular inspections, conditionally alerts via Slack, securely handles credentials, and provides an auditable execution history.

Inputs

Name	Type	Default	Description
database	STRING	production	Target MongoDB database to audit.
collection	STRING	orders	Target collection to inspect for unused indexes.
max_allowed_accesses	INT	0	Maximum operational accesses below which an index is flagged as unused.
slack_channel	STRING	#dba-alerts	Slack channel destination for index governance alerts.

Expected outputs

• {{ outputs.audit_unused_indexes.rows }}: Array of unused index records containing name, key, accesses_count, and since.
• {{ outputs.audit_unused_indexes.size }}: Total count of secondary indexes flagged with zero accesses.
• {{ outputs.evaluate_unused_indexes }}: Result of conditional branch evaluation.
• {{ outputs.export_audit_manifest.value }}: Structured JSON telemetry manifest recording execution timestamp and status.

Prerequisites

• A MongoDB instance (version 3.2+ supports $indexStats).
• A database user with read access on the target collection to run aggregation pipelines.
• A Slack Incoming Webhook URL targeting your database alert channel.

Secrets

• MONGODB_URI: MongoDB connection URI (e.g. mongodb+srv://<user>:<password>@cluster.mongodb.net/?retryWrites=true&w=majority).
• SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint URL used for sending alert notifications.

Quick start

1. Configure MONGODB_URI and SLACK_WEBHOOK_URL in your Kestra namespace secrets.
2. Import this flow YAML into your Kestra instance.
3. Click Execute in the UI to run an initial index audit against your target collection.
4. Review execution outputs to inspect index usage counts and candidate drop commands.

Common pitfalls and troubleshooting

• Server Restart Reset: In MongoDB, $indexStats counters reset whenever mongod restarts. Run this audit on instances with significant uptime (e.g., at least 30 days) to prevent falsely flagging newly initialized indexes.
• Unique Constraint Indexes: Indexes enforcing uniqueness (unique: true) might receive few read hits but remain essential for data integrity constraints. Verify uniqueness requirements before executing dropIndex().
• Primary Index Exclusion: The pipeline explicitly filters out _id_, as the primary index cannot and should not be removed.

How to extend

• Add io.kestra.plugin.core.flow.Loop to loop through an array of collections and audit an entire database schema in one run.
• Email a weekly PDF or HTML index scorecard to engineering leads.
• Add automated ticket creation in Jira or Linear for approved index deprecation reviews.

Links

• Kestra MongoDB Plugin Documentation: https://kestra.io/plugins/plugin-mongodb
• Kestra Slack Plugin Documentation: https://kestra.io/plugins/plugin-notifications
• MongoDB $indexStats Documentation: https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/