Renewable Farm Cluster Index Defragmentation Script

Role: Lead Database Administrator specializing in mission-critical renewable energy telemetry clusters.

Context

• Asset management entity: {{asset_operator}}
• Database engine and version: {{db_engine_version}}
• Primary telemetry table: {{primary_telemetry_table}}
• Maintenance window duration: {{maintenance_window_duration}} minutes
• Replication lag threshold: {{max_replication_lag_seconds}} seconds
• Lock wait timeout: {{lock_timeout_ms}} ms

Task

Write an automated maintenance shell and SQL script for {{asset_operator}} that safely assesses index fragmentation on {{primary_telemetry_table}}, rebuilds bloated indexes concurrently, and prevents replication lag spikes on {{db_engine_version}}.

Method

1. Query database catalog metadata to evaluate index bloat and fragmentation levels on {{primary_telemetry_table}}.
2. Filter for indexes exhibiting fragmentation greater than 30% or physical bloat above 500MB.
3. Set session-level lock timeouts to {{lock_timeout_ms}} ms to eliminate table lock starvation.
4. Check streaming replication lag across read replicas, pausing operations if lag exceeds {{max_replication_lag_seconds}} seconds.
5. Execute online concurrent index rebuild commands (REINDEX CONCURRENTLY or equivalent).
6. Track execution progress against the {{maintenance_window_duration}} minute time limit, terminating cleanly before window expiry.
7. Log fragmented vs post-rebuild sizes and send an operational summary to standard output.

Constraints

• MUST utilize concurrent/online index rebuilding commands that do not acquire exclusive write locks.
• MUST abort the maintenance step immediately if replication lag exceeds {{max_replication_lag_seconds}} seconds.
• Code MUST run idempotently without leaving orphan temporary indexes.
• Shell script must adhere to strict mode (set -euo pipefail).

Output format

Provide the deliverable in the following order:

• Block 1: Pre-flight catalog query to identify candidate indexes
• Block 2: Replication health-check helper script
• Block 3: Core online defragmentation Bash/SQL wrapper script
• Block 4: Logging and verification query Total output length must not exceed 130 lines of code.

Self-review

1. Verify that lock timeout {{lock_timeout_ms}} ms is applied before any DDL.
2. Ensure the script pauses or exits if elapsed time nears {{maintenance_window_duration}} minutes.
3. Validate syntax specifically against {{db_engine_version}} requirements.