Smart Meter Time-Series Table Partitioning Script

Role: Principal Database Architect specializing in utility smart grid telemetry and time-series data modeling.

Context

• Utility operator: {{utility_name}}
• Database engine: {{rdbms_flavor}}
• Target telemetry table: {{telemetry_table}}
• Partitioning interval: {{partition_cadence}}
• Data retention limit: {{retention_months}} months
• Storage compression algorithm: {{compression_algorithm}}

Task

Author a production-ready SQL maintenance script that establishes automated range partitioning, index strategy, and rolling data retention for smart meter telemetry ingested by {{utility_name}}.

Method

1. Define the base partitioned table DDL for {{telemetry_table}} with optimal data types for timestamp, meter_id, kW, and reactive power metrics.
2. Create primary key and composite indexing schemes aligned with typical interval consumption queries.
3. Implement dynamic range partitioning according to {{partition_cadence}}.
4. Write a stored procedure to automatically pre-create upcoming partition tables ahead of ingestion.
5. Write a rolling retention cleanup function to drop or archive partitions older than {{retention_months}} months.
6. Incorporate table-level compression directives using {{compression_algorithm}} on detached historical partitions.
7. Include transactional safety checks and idempotent execution wrappers.

Constraints

• MUST output fully executable, dialect-specific code for {{rdbms_flavor}}.
• MUST NOT lock parent tables during partition creation or drop phases.
• All database objects must follow clean snake_case naming conventions.
• Stored procedures must include embedded error handling and logging statements.

Output format

Provide the script structured in these exact blocks:

• Section 1: Schema and Partitioned Table DDL (with indexes)
• Section 2: Automated Partition Provisioning Stored Procedure
• Section 3: Retention and Archival Cleanup Procedure
• Section 4: Scheduled Cron/Job Invocation Example
• Section 5: Rollback Script Total output length should be between 70 and 120 lines of commented SQL.

Self-review

1. Confirm that interval bounds match {{partition_cadence}} without timestamp gaps.
2. Verify that drop logic strictly enforces {{retention_months}} retention.
3. Ensure syntax is 100% compliant with {{rdbms_flavor}}.