APromptAura

#sql

Top prompts tagged sql.

0
p/codingAauraNovice4 days agohas example⁂auto-checked

SQL migration scripts with a tested rollback path

Write a database migration for: change_description in database. I need both the forward migration and a working rollback, not just a comment saying 'reverse the above'. Format: 1. Forward migration SQL, in the smallest safe steps (see note on locking below). 2. Rollback SQL that actually restores the prior state, including data if the forward migration transforms or deletes anything (not just structural revert). If perfect rollback is impossible (e.g. a dropped column's data is gone), say so explicitly and propose a mitigation (e.g. rename instead of drop, keep for one release cycle). 3. State which statements take a table lock and roughly how that scales with table size, using database's actual locking behavior, not a generic claim. 4. Idempotency: write the migration so re-running it after a partial failure doesn't error out (use IF NOT EXISTS / IF EXISTS guards where the dialect supports them). 5. A one-line comment above each statement explaining its purpose, for the next engineer reading the migration file. Current schema: schema Change needed: change_description
ClaudeGPT#sql#migration#database#rollback
0
0
p/codingAauraNovice4 days agohas example⁂auto-checked

Zero-downtime database schema migration plan

I need to make this schema change: change_description on table table_name in database (production, actively serving traffic, row_count rows). Produce a plan that avoids locking the table for writes and avoids breaking currently-deployed application code during the rollout. Structure: 1. Break the change into safe, backward-compatible steps (e.g. add-nullable-column before backfill before add-constraint before drop-old-column), each deployable independently. 2. For each step, specify: the exact DDL, whether it takes a lock and for how long is acceptable, and what application code version must already be deployed before this step runs. 3. Backfill strategy: batched, with a way to pause/resume, and an estimate of how long it will take given the row count. 4. The order in which application code changes and migration steps must be deployed relative to each other (expand/contract pattern), stated explicitly as a sequence. 5. A rollback plan for each step that doesn't require restoring from backup. 6. What to monitor during the backfill (replication lag, lock wait time, error rate) to know if it's safe to keep going.
ClaudeGPT#sql#migration#database#production
0
0
p/codingAauraNovice4 days agohas example⁂auto-checked

Diagnose and rewrite a slow SQL query with execution-plan reasoning

This query is too slow. I'll give you the query, the schema (with indexes), and the EXPLAIN/EXPLAIN ANALYZE output. Do this: 1. Read the execution plan and identify the specific operation that dominates cost (seq scan, nested loop, sort spill, etc.), quoting the relevant line from the plan. 2. Explain in plain terms why the planner is choosing that path given the current indexes and statistics. 3. Propose the smallest change that would fix it, ranked: (a) query rewrite with no schema change, (b) new index, (c) schema change, in that order of preference, and say why you're not jumping straight to an index if a rewrite would do. 4. Show the rewritten query or CREATE INDEX statement. 5. Predict how the plan should change after your fix, and tell me what to check in the new EXPLAIN output to confirm it worked. 6. Flag if the fix could hurt a different query that shares the same table (e.g. a new index that slows down writes on a hot table). Database: {{database, e.g. Postgres 15}} Query: query Schema/indexes: schema EXPLAIN output: explain_output
ClaudeGPT#sql#performance#database
0