Partitioning Strategy Decision Prompt

This prompt is a decision-support tool for data architects and database engineers facing a concrete table design problem: selecting a partitioning strategy before creating a new large table or refactoring an existing one. The job-to-be-done is not to learn about partitioning in the abstract, but to force the model to reason from your specific access patterns, data volumes, and query profiles. The ideal user has already profiled the dominant queries, measured approximate data volumes and growth rates, and identified the critical write and read paths. Without this context, the prompt will produce textbook definitions instead of an actionable recommendation.

Use this prompt when the wrong partition key would cause measurable production harm—hot spots that saturate a single partition, scan amplification that turns targeted queries into full-table scans, or partition sprawl that makes maintenance windows unmanageable. It is appropriate for tables expected to exceed 100 GB, for time-series data with high ingest rates, for multi-tenant tables where tenant isolation matters, and for any table where query pruning is essential to meet latency SLAs. The prompt works best with concrete inputs: a representative sample of SELECT, INSERT, UPDATE, and DELETE patterns, the expected row width, the target database engine and version, and any regulatory or compliance constraints on data placement.

Do not use this prompt for small lookup tables under 10 GB where partitioning overhead exceeds any benefit, for systems where the database engine handles partitioning transparently without DDL control (such as some managed data warehouses with automatic clustering), or when you have not yet profiled the dominant query patterns. Using it prematurely—before you know whether queries filter by timestamp, tenant ID, or a composite key—will produce a generic recommendation that may be actively harmful. Also avoid this prompt when the real problem is indexing, vacuuming, or statistics freshness rather than partitioning strategy. If you cannot provide concrete access patterns, start with the Query Pattern Analysis Prompt for Schema Design first, then return here with the results.

The following prompt template is designed to be copied directly into your AI workspace. It forces the model to reason about your specific access patterns, data volume, and query profiles before recommending a partitioning strategy. The template uses square-bracket placeholders that you must replace with concrete details from your own environment. Do not leave any placeholder unresolved, or the model will generate a generic, untrustworthy recommendation.

textCopy
You are a data architect specializing in table partitioning for analytical and transactional databases. Your task is to recommend a partitioning strategy for the table described below. You must reason from the provided access patterns, data volume, and query profiles. Do not recommend a strategy without justifying it against the specific workload.

## INPUT
Table Schema (DDL):
[TABLE_DDL]

Current Row Count: [ROW_COUNT]
Estimated Daily Ingestion Rate: [DAILY_INGESTION_RATE]

Primary Access Patterns (list each query with its WHERE clause predicates, frequency, and latency target):
[ACCESS_PATTERNS]

Data Retention Requirements:
[RETENTION_REQUIREMENTS]

Database System and Version:
[DATABASE_SYSTEM]

## CONSTRAINTS
- The table is [READ_HEAVY / WRITE_HEAVY / BALANCED].
- Maximum acceptable partition count: [MAX_PARTITIONS].
- Maintenance window: [MAINTENANCE_WINDOW].
- Existing indexes: [EXISTING_INDEXES].

## OUTPUT_SCHEMA
Return a JSON object with the following structure:
{
  "recommended_strategy": "RANGE | LIST | HASH | COMPOSITE | NONE",
  "partition_key": "column_name or expression",
  "partition_granularity": "DAILY | MONTHLY | YEARLY | CUSTOM",
  "rationale": [
    "Reason 1 tied to a specific access pattern or constraint.",
    "Reason 2 tied to data volume or retention.",
    "Reason 3 tied to maintenance or operational concern."
  ],
  "partition_management_approach": "Description of how partitions will be created, detached, or dropped over time.",
  "expected_pruning_effectiveness": "HIGH | MEDIUM | LOW",
  "pruning_justification": "Which queries will benefit from partition pruning and why.",
  "risks_and_anti_patterns": [
    "Risk 1: e.g., partition skew if a specific value dominates.",
    "Risk 2: e.g., excessive partition count leading to planning overhead."
  ],
  "alternative_strategies_considered": [
    {
      "strategy": "Alternative strategy name",
      "why_rejected": "Specific reason tied to the provided workload."
    }
  ],
  "next_steps": [
    "Actionable step 1: e.g., test with a representative query workload.",
    "Actionable step 2: e.g., monitor partition sizes for 7 days."
  ]
}

## EVALUATION CRITERIA
Before returning your response, verify:
1. The partition key appears in WHERE clauses of the most frequent or expensive queries.
2. The granularity aligns with retention requirements (e.g., monthly partitions for 12-month retention).
3. The strategy does not create more partitions than the system can manage efficiently.
4. You have identified at least one concrete risk or anti-pattern.
5. You have explained why at least one plausible alternative was rejected.

After pasting the template, replace every square-bracket placeholder with values from your actual environment. The [ACCESS_PATTERNS] placeholder is the most critical: provide real query shapes with their WHERE clause predicates, not abstract descriptions. If you don't know the exact queries, extract them from your query logs or application code before running this prompt. The output schema is deliberately strict so you can parse the JSON response directly in your automation pipeline. If the model returns a strategy without tying each rationale bullet to a specific access pattern or constraint, treat the output as low-confidence and re-prompt with more explicit instructions.

The partitioning strategy prompt is designed to be integrated into a data architecture review pipeline, not used as a one-off chat interaction. Because partitioning decisions directly affect production query performance, storage cost, and operational complexity for years, the harness must enforce structured validation before any recommendation reaches a human decision maker. The typical integration point is a pull request workflow for schema changes, a data platform self-service portal, or an architecture review board queue. In all cases, the prompt output should be treated as a draft recommendation that requires explicit human approval before implementation.

Wire the prompt into an application by first collecting the required inputs: the table DDL, a representative sample of query patterns with EXPLAIN output, estimated data volume and growth rate, and any operational constraints like maintenance windows or cloud provider limitations. The application layer should assemble these into the [INPUT] placeholder and call the model with a low temperature (0.1–0.3) to maximize deterministic, repeatable recommendations. On receiving the response, run a structured validation pass that checks: (1) the output contains a concrete partition key recommendation, not a generic list of options; (2) the rationale references the provided access patterns, not textbook rules; (3) partition skew warnings are present when the proposed key has low cardinality or uneven distribution; and (4) the partition management approach (e.g., time-based rollover, range splitting) is specified. If validation fails, retry once with the validation errors appended to the prompt as [CONSTRAINTS] feedback. If the second attempt also fails, escalate to a human reviewer with the partial output and failure reasons logged.

For high-stakes environments—production databases over 100 GB, tables serving customer-facing queries, or regulated data stores—add a human approval gate after validation passes. The harness should render the recommendation into a structured decision record (similar to an ADR) that includes the prompt's output, the input context, and a checklist for the reviewer: confirm the access pattern analysis is accurate, verify the partition key won't cause hot spots under peak load, and approve or reject the management strategy. Log every recommendation, validation result, and approval decision for auditability. Avoid wiring this prompt directly into automated schema migration tools; partitioning changes are too risky for fully automated execution without human judgment on the operational impact.