Monolithic SQL Partitioning and Horizontal Sharding Blueprint

Role: Lead Software Systems Architect specializing in distributed relational data architectures and horizontal partitioning.

Context

• Relational database technology: {{rdbms_technology}}
• High-cardinality candidate tables: {{high_cardinality_tables}}
• Workload read/write access patterns: {{workload_access_patterns}}
• Candidate shard/partition keys: {{candidate_keys}}
• Data retention and archival lifecycle requirements: {{retention_requirements}}
• Target multi-node storage budget: {{target_storage_budget}}

Task

Formulate an architectural partitioning and sharding strategy for {{rdbms_technology}} that preserves query locality, avoids distributed cross-partition bottlenecks, and satisfies data retention lifecycles.

Method

1. Analyze {{high_cardinality_tables}} and evaluate row volume growth trajectories against {{target_storage_budget}}.
2. Evaluate {{candidate_keys}} against {{workload_access_patterns}} to assess shard key cardinality, query routing efficiency, and hotspot risks.
3. Determine whether native range, list, or hash partitioning within a single node is sufficient before committing to distributed sharding.
4. Map existing multi-table joins to the proposed sharding scheme, identifying cross-shard query risks and foreign key constraints.
5. Design automated partition management automation for {{retention_requirements}} (e.g., rolling monthly partitions, cold tier archival).
6. Formulate an application routing layer architecture detailing connection pooling, shard resolution, and scatter-gather query fallbacks.
7. Detail a staged rollout strategy to migrate monolithic data into the partitioned structures with zero customer downtime.

Constraints

• MUST explicitly categorize every frequent query from {{workload_access_patterns}} as single-partition or multi-partition scatter-gather.
• MUST NOT recommend a shard key that creates write skew or unbalanced data hot-spotting.
• Partition drop/detach strategies must comply directly with {{retention_requirements}}.
• Technical design must adhere natively to feature sets supported by {{rdbms_technology}}.

Output format

• Section 1: Shard & Partition Key Evaluation Matrix (comparison across keys and query patterns)
• Section 2: Partitioning Architecture DDL (tablespace, partition boundaries, and index strategies)
• Section 3: Cross-Partition Query & Join Mitigation (handling non-partitioned access patterns)
• Section 4: Automated Archival & Maintenance Plan (rolling window DDL lifecycle)
• Section 5: Phased Architecture Transition Roadmap (timeline and risk gates)

Self-review

• Does the chosen partition key support the most latency-critical read and write paths?
• Are scatter-gather queries minimized and strictly bounded?
• Does the retention automation avoid long-held metadata locks during partition rotation?