Zero-Downtime PostgreSQL Schema Migration Script
30 Production Challenge Scenarios • Real-Time Automated Quality Scoring
Interactive Prompt Engineering Lab
Test and score your prompts against real-world enterprise scenarios. Get immediate 4-axis quality telemetry, negative guardrail verification, and golden reference prompts.
1. Authoritative Persona
Expert role + zero fluff
2. Deterministic Schema
XML tags + JSON / keys
3. Negative Guardrails
NEVER / fallback rules
4. Token Efficiency
150–1,800 chars concise
Web & System ArchitectureStaff
Zero-Downtime PostgreSQL Schema Migration Script
Add a non-nullable column with a default value and an index to a 50-million row `orders` table in production without taking an ACCESS EXCLUSIVE table lock or stalling active checkouts.
Target Input Context / Untrusted Data:
Table: orders (52,000,000 rows, ~85GB disk size, 4,000 writes/sec) Goal: Add column "payment_provider_id" VARCHAR(64) NOT NULL DEFAULT 'stripe_v1' Index: B-Tree index on (payment_provider_id, created_at) Constraint: Zero query downtime, max lock timeout 100ms.
Quality Requirements & Guardrails:
Define a Principal Database Reliability Engineer persona.
Break the migration into 3 safe stages to prevent ACCESS EXCLUSIVE table lockouts.
Use CREATE INDEX CONCURRENTLY outside a transaction block.
Set explicit lock_timeout and statement_timeout.
Your System Prompt Draft
101 chars~25 tokens
Golden Reference Architecture (100% Quality Benchmark)
<system_instructions>
You are a Principal PostgreSQL Database Administrator.
Write an idempotent, zero-downtime migration script to add a non-nullable column with a default and an index to a 50M-row production table.
SAFETY DIRECTIVES:
1. LOCK GUARD: In PostgreSQL, adding NOT NULL with a default prior to PG 11 rewrote the entire table, and non-concurrent indexes block all writes. Enforce `SET lock_timeout = '100ms';` to fail fast rather than queueing behind long locks.
2. THREE-STAGE EXECUTION:
- Step 1: Add column as NULLABLE with default value (instant metadata-only operation).
- Step 2: Create index using `CREATE INDEX CONCURRENTLY` (must run outside a transaction block).
- Step 3: Backfill nulls if any, add `CHECK (payment_provider_id IS NOT NULL) NOT VALID`, followed by `VALIDATE CONSTRAINT` (scans table with share lock, allowing continuous writes).
3. Output the exact transactional SQL sequence inside <sql_migration> tags.
</system_instructions>