Large Volume Database Seeding Prompt for Performance Testing

This prompt is for performance engineers and DBAs who need to generate production-scale test datasets quickly. Instead of writing manual seed scripts or running slow data generators, you provide a DDL schema, target row counts, and distribution rules. The model returns a validated, batch-oriented INSERT script with index-aware ordering, row count verification queries, and timing estimates. Use this when you need a repeatable seed script that exercises the database under realistic volume, distribution, and indexing conditions.

The ideal user has a target schema ready and knows the approximate row counts and data distributions needed to simulate production load. You should have a clear understanding of which tables are the primary performance targets, what indexing strategy is in place, and whether you need uniform, normal, or skewed value distributions. The prompt works best for single-table or simple parent-child seeding where the primary goal is volume and distribution realism rather than complex referential integrity across dozens of interrelated tables. Provide the DDL as part of [CONTEXT], specify row counts in [CONSTRAINTS], and define distribution rules for indexed columns so the model can order inserts to minimize page splits and index rebuild overhead during seeding.

Do not use this prompt for small functional test datasets (under 10,000 rows) where manual seed scripts or ORM factories are more appropriate. Avoid it when referential integrity across many tables is the primary concern—those scenarios need a multi-table seeding prompt that coordinates foreign key resolution across parent-child relationships. Also avoid this prompt when you need production-like data diversity with realistic value patterns, names, addresses, or business-specific formats; this prompt focuses on volume and distribution, not semantic realism. For those cases, use the realistic user profile generation or schema-compliant record generation prompts instead. After generating the seed script, always run the included verification queries to confirm row counts and distribution targets before using the dataset in performance tests.

This prompt template is designed to be pasted directly into your AI harness or orchestration layer. It instructs the model to produce batch INSERT scripts for performance testing, replacing square-bracket placeholders with your specific schema, volume targets, and distribution rules. The output is intended to be executable SQL, not a description of SQL.

textCopy
You are a database performance test data generator. Your output must be executable SQL only. Do not include explanations, comments, or markdown fences unless explicitly requested.

Generate a batch INSERT script for the following database schema:

[SCHEMA_DDL]

Target row counts:
- Total rows to generate: [TOTAL_ROW_COUNT]
- Batch size per INSERT statement: [BATCH_SIZE]

Value distribution rules:
- [COLUMN_NAME_1]: [DISTRIBUTION_RULE_1] (e.g., uniform random between 1 and 100000, normal distribution with mean 500 and stddev 150, sequential from 1, random UUID v4)
- [COLUMN_NAME_2]: [DISTRIBUTION_RULE_2]
- [COLUMN_NAME_3]: [DISTRIBUTION_RULE_3]

Referential integrity constraints:
- [FOREIGN_KEY_COLUMN] must reference valid primary keys from [REFERENCED_TABLE] within the range [MIN_PK, MAX_PK]
- [CASCADE_CONDITIONS or 'None']

Index awareness:
- The following columns have indexes: [INDEXED_COLUMNS]
- Order INSERT statements by [ORDERING_COLUMN] to minimize page splits

Output format:
- Use multi-row INSERT syntax: INSERT INTO [TABLE_NAME] ([COLUMN_LIST]) VALUES (row1), (row2), ...;
- Each statement must contain exactly [BATCH_SIZE] rows except the final batch
- Include a comment header with estimated row count and generation timestamp

After generating the INSERT script, append these verification queries:
1. SELECT COUNT(*) FROM [TABLE_NAME]; -- Expected: [TOTAL_ROW_COUNT]
2. SELECT MIN([ORDERING_COLUMN]), MAX([ORDERING_COLUMN]), COUNT(DISTINCT [ORDERING_COLUMN]) FROM [TABLE_NAME];
3. [ADDITIONAL_VERIFICATION_QUERY or 'None']

Constraints:
- [CONSTRAINTS] (e.g., 'No NULLs in NOT NULL columns', 'Unique values in [UNIQUE_COLUMNS]', 'Timestamps within [START_DATE] to [END_DATE]')

Risk level: [RISK_LEVEL] (Low/Medium/High). If High, include a rollback script and data validation warnings.

To adapt this template, replace every square-bracket placeholder with concrete values from your schema and performance test plan. The [SCHEMA_DDL] should be a complete CREATE TABLE statement including column types, defaults, and constraints. For [DISTRIBUTION_RULE], specify the statistical distribution, range, and any correlation between columns. If your test requires skewed data to simulate real-world access patterns, describe the skew explicitly (e.g., '80% of foreign key references should target the most recent 20% of primary keys'). The [RISK_LEVEL] field controls whether the prompt includes safety checks: High-risk environments (production-like schemas, regulated data structures) should trigger rollback script generation and explicit validation warnings. Before executing the generated SQL, always run the verification queries against a non-production environment and validate row counts, distribution shapes, and referential integrity. For teams using this in CI/CD pipelines, wrap the prompt in a harness that captures the generated SQL, runs it against a test instance, executes the verification queries, and fails the pipeline if counts or constraints don't match.

This prompt is not a one-shot generator; it is a component in a performance data pipeline. The primary integration point is a script or service that accepts the target row count, schema DDL, and distribution parameters, then calls the LLM to produce a batch INSERT script. Because the output is executable SQL, the harness must treat it as untrusted code: validate syntax, estimate execution time, and never auto-execute against a production database. The recommended flow is: generate → validate → dry-run estimate → execute in isolated perf environment → verify row counts and index health.

Validation and retry logic is critical. After generation, pipe the output through a SQL syntax validator (e.g., a parse-only connection to a test database or a SQL linter like sqlfluff). Check that the row count matches the requested [ROW_COUNT] by counting INSERT statements or VALUES tuples. If validation fails, feed the error message back into the prompt as a correction request: 'The previous output failed validation with error: [ERROR]. Regenerate the INSERT script fixing this error while preserving the target row count and distribution.' Set a maximum of 3 retry attempts before failing the job and alerting an engineer. Log every generation attempt, validation result, and retry decision for traceability.

Execution harness design should separate generation from execution. Use a dedicated performance database or schema, never a shared dev environment. Before execution, run EXPLAIN on a sample of the generated INSERTs to estimate cost. Wrap execution in a timing harness that logs: start time, end time, rows inserted, transactions per second, and any constraint violations. If the prompt includes index-aware ordering (e.g., clustering key order), verify that the generated script respects that ordering by checking the first N rows against the index definition. For very large seeds (>10M rows), split the output into batch files of 100K-1M rows each and execute them sequentially with checkpoint logging.

Model choice and cost considerations matter at scale. For simple schemas with standard distributions, a smaller, faster model (e.g., GPT-4o-mini, Claude Haiku) often suffices. For complex schemas with many interleaved constraints, custom distribution functions, or domain-specific value patterns, use a more capable model and consider caching the prompt prefix containing the DDL and distribution rules. Track token usage per generation run and set a cost threshold alert. If seeding the same schema repeatedly, cache the generated script and only regenerate when the schema or distribution parameters change.

Observability and verification close the loop. After execution, run row count verification queries (SELECT COUNT(*)) and spot-check value distributions with PERCENTILE or histogram queries. Compare actual insertion timing against the prompt's estimated timing to calibrate future estimates. Log all metrics—generation time, validation passes/failures, execution time, row counts, and distribution samples—to your performance testing dashboard. If the seed data will be reused across test runs, snapshot the database and version the seed script alongside the schema definition in your test data repository.