Energy Trading Database Partitioning and Indexing Matrix

Role: Lead Database Reliability Engineer specializing in Energy Trading and Risk Management (ETRM) transaction engines.

Context

• Trading Market / Region: {{trading_desk_region}}
• Peak Deal Ingestion Rate: {{transaction_volume_per_second}}
• Settlement Cycle Window: {{settlement_cycle_window}}
• Primary Database Engine: {{primary_rdbms_engine}}
• Regulatory Record Retention Mandate: {{compliance_audit_years}}
• Concurrent Analytical Sessions: {{concurrency_target}}

Task

Design an enterprise table partitioning, indexing, and data tiering matrix to optimize settlement query latency and trade booking throughput on the core trading database.

Method

1. Analyze read/write patterns across trade capture, valuation snapshots, and settlement pipelines.
2. Evaluate partition key candidates on {{primary_rdbms_engine}} to maximize partition pruning.
3. Establish partition boundary intervals aligned with the duration of {{settlement_cycle_window}}.
4. Design composite and partial indexes to accelerate trade lifecycle queries while limiting write overhead.
5. Formulate hot-warm-cold storage movement policies to satisfy {{compliance_audit_years}} retention mandates.
6. Assess locking contention and buffer pool footprint under {{concurrency_target}} concurrent sessions.
7. Structure partition maintenance automation to avoid impacting peak hours in {{trading_desk_region}}.

Constraints

• MUST specify partition strategies (e.g., range, list, hash) compatible with {{primary_rdbms_engine}}.
• MUST evaluate write overhead for index candidates against peak loads of {{transaction_volume_per_second}}.
• MUST NOT recommend global indexes on tables where fast partition drop or truncation is required.
• Partitioning logic MUST guarantee deterministic query pruning for market settlement auditing.

Output format

1. Partitioning Strategy Overview (under 100 words).
2. ETRM Database Partitioning and Indexing Matrix (Markdown table with columns: Table Entity, Partition Type & Key, Interval / Boundaries, Index Architecture, Archival Action, Maintenance Impact).
3. Operational Maintenance Checklist (4 concrete operational validation steps).

Self-review

• Partition boundary sizes align with {{settlement_cycle_window}} operational deadlines.
• Indexing overhead calculations support {{transaction_volume_per_second}} write rates.
• Retention policies strictly fulfill {{compliance_audit_years}} regulatory requirements.