Database Saturation Test Scenario Prompt

Use this prompt when you need to design a precise, multi-axis database saturation test before running load generation tools like pgbench, sysbench, or k6. The job-to-be-done is translating your specific database architecture, schema characteristics, traffic patterns, and infrastructure constraints into a structured, executable test scenario. This is not a generic load test prompt; it is for database reliability engineers, performance engineers, and platform teams who need to find the real saturation ceiling of their data layer by exercising CPU, I/O, locks, and replication lag simultaneously. You should use this prompt when you already have monitoring in place for these key metrics and need a scenario that produces a definitive saturation point, not just a simple throughput number.

The ideal user has a deep understanding of their database's internal topology and can provide detailed context. Required inputs include the database engine and version, a representative schema with data types and index definitions, expected read/write ratios for critical transactions, and infrastructure constraints like CPU cores, memory, and disk type. The prompt works best when you can describe specific query patterns (e.g., 'an UPDATE to a single row by primary key that also touches three non-covering indexes') rather than vague categories. Do not use this prompt if you lack monitoring for lock contention or replication lag, as the output scenario will reference these metrics as primary saturation indicators and you will be unable to validate the results.

This prompt is not a substitute for a full performance test plan and should not be used for simple smoke tests or basic throughput benchmarking. It is explicitly designed for saturation point discovery, meaning the scenario it produces is intended to push the database to its breaking point in a controlled manner. Avoid using this prompt if your goal is to validate normal operating capacity with a comfortable headroom; for that, use a capacity planning load model prompt instead. The output will include aggressive connection pool targets and query patterns designed to create contention, which can be disruptive to shared environments. Always execute the resulting scenario in an isolated performance environment that mirrors production hardware, never against a live production database.

Before using this prompt, ensure you have gathered your database's key metrics baselines under idle and normal load. The prompt will ask you to specify saturation indicators for CPU, I/O, locks, and replication lag, and you will need to know your alerting thresholds for each. A common failure mode is providing unrealistic or missing constraints, which leads to a scenario that saturates a resource you cannot monitor or hits a cloud provider's hard limit that you forgot to mention. After generating the scenario, your next step is to review the query templates for index utilization assumptions and validate that the proposed connection pool sizing does not exceed your driver or proxy limits. Then, wire the scenario into your load tool of choice and execute it with your monitoring dashboards ready.

This prompt template is designed to produce a structured, executable test scenario for identifying the saturation point of a database system. It forces the model to consider the full stack—from connection pooling and query patterns to OS-level I/O and replication lag—by requiring you to fill in specific architectural details. The output is a JSON document that can be directly consumed by a test harness, making it suitable for automated CI/CD pipelines or manual performance engineering workflows.

textCopy
You are a senior database performance engineer. Your task is to design a precise, repeatable database saturation test scenario.

Use the following inputs to construct the scenario:
- SYSTEM_ARCHITECTURE: [SYSTEM_ARCHITECTURE_DESCRIPTION]
- DATABASE_TECHNOLOGY: [DATABASE_TYPE_AND_VERSION]
- SLA_TARGETS: [LATENCY_AND_THROUGHPUT_SLAS]
- INFRASTRUCTURE_SPECS: [CPU_MEMORY_IO_AND_NETWORK_SPECS]
- KNOWN_BOTTLENECKS: [EXISTING_PERFORMANCE_CONCERNS]
- MONITORING_TOOLS: [AVAILABLE_METRICS_AND_LOG_SOURCES]

Generate a test scenario that strictly adheres to this JSON schema:
{
  "scenario_name": "string",
  "objective": "string (a single, measurable saturation goal)",
  "workload_model": {
    "read_write_mix_ratio": "string (e.g., '80:20')",
    "query_patterns": [
      {
        "query_template": "string (parameterized SQL/query)",
        "calls_per_second_target": "number",
        "average_result_set_size": "string (e.g., '100 rows')"
      }
    ],
    "connection_pool_config": {
      "min_connections": "number",
      "max_connections": "number",
      "ramp_up_period_seconds": "number"
    }
  },
  "saturation_indicators": {
    "primary_metric": "string (e.g., 'CPU utilization > 85%')",
    "secondary_metrics": ["string (e.g., 'write IOPS at provisioned limit')"],
    "replication_lag_threshold_ms": "number",
    "lock_contention_threshold": "string (e.g., 'lock wait time > 100ms')"
  },
  "test_phases": [
    {
      "phase_name": "string",
      "duration_minutes": "number",
      "target_concurrent_users": "number",
      "description": "string"
    }
  ],
  "assumptions_and_risks": {
    "index_utilization_assumptions": ["string"],
    "vacuum_compaction_impact_notes": "string",
    "data_volume_estimate": "string (e.g., '500GB')",
    "missing_observability_risks": ["string"]
  },
  "success_criteria": {
    "saturation_point_identified": "boolean",
    "degradation_is_graceful": "boolean",
    "no_data_corruption": "boolean"
  }
}

CONSTRAINTS:
- The read/write mix must be justified by the SLA_TARGETS.
- Every query pattern must be traceable to a specific user journey or backend process.
- Saturation indicators must be directly observable with the provided MONITORING_TOOLS.
- Explicitly state what you assume about index usage and the impact of missing or stale database statistics (e.g., vacuum/compaction).
- If the KNOWN_BOTTLENECKS input is empty, state that the scenario is a broad search for the first bottleneck.

OUTPUT: Only the valid JSON object. No other text.

To adapt this template, replace every [PLACEHOLDER] with concrete values from your system's runbook and architecture diagrams. The SYSTEM_ARCHITECTURE_DESCRIPTION should be a dense summary of how your application connects to the database, including any caching layers. The MONITORING_TOOLS field is critical; the model's ability to define useful saturation_indicators depends entirely on the granularity of the metrics you list. After generating the scenario, validate the JSON against the provided schema before passing it to your load testing tool (e.g., k6, Locust, or a custom harness). For high-risk production databases, always have a senior engineer review the assumptions_and_risks section to ensure the test won't cause an unexpected outage.

This prompt produces a scenario document, not executable code. You will need to translate the output into your load testing tool of choice. Wire the prompt into a pre-test workflow that runs before each saturation test cycle. Feed the output into a human review step where a database engineer confirms the query mix, connection targets, and saturation indicators match the current system state.

After review, parameterize your load generator with the read/write ratios, query templates, and ramp schedule from the scenario. During test execution, collect the saturation indicators the prompt specified and compare against the predicted saturation sequence. Log any deviation between predicted and actual saturation order for post-test analysis. If the model proposes a saturation indicator you cannot measure, flag it before test execution and either add the metric or remove the indicator from the scenario.

Store each generated scenario alongside test results for auditability and regression comparison. When the database schema, indexing strategy, or hardware profile changes, re-run the prompt with updated context to generate a new baseline scenario. Never reuse a stale scenario without re-validation against the current system state.