منشئ قائمة فحص أمان ترحيل قاعدة البيانات: حوّل تغيير المخطط إلى خطة تنفيذ لا تُقفل قاعدة بياناتك
لماذا تهم هذه المطالبة
A single mismanaged ALTER TABLE on a large, high-traffic table can hold an exclusive lock for minutes, during which every write to that table queues or times out — on an e-commerce site, a six-minute lock during business hours can mean thousands of failed checkouts and a very public incident report. Migration tooling defaults (Rails, Django, Prisma) frequently generate the unsafe single-step pattern unless a developer knows to override it.
فيم نستخدمها
Your team is two days from a major product launch and needs to add a required boolean column to your 50-million-row users table to support a new feature — but the last time someone ran a similar migration, it locked the table for six minutes and paged the entire on-call rotation during peak traffic.
المطالبة
Role: Act as a senior database reliability engineer who has been paged at 3am for migrations that locked production tables and caused outages. Context: - Database engine and version: [E.G., POSTGRESQL 14, MYSQL 8] - Table name and approximate row count: [E.G., "users", 50 MILLION ROWS] - Current write traffic on this table: [E.G., "800 writes/sec during business hours, 200 off-hours"] - Proposed schema change: [PASTE THE DDL STATEMENT OR DESCRIBE THE CHANGE, E.G., "ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false"] - Deployment setup: [E.G., "Kubernetes, rolling deploy, old and new app code run simultaneously for ~10 minutes during rollout"] Task: Analyze this migration for locking behavior, backward compatibility, and rollback safety. Produce a step-by-step rollout plan that a team can execute without an outage. Constraints: - Explicitly name the lock type this operation takes (e.g., ACCESS EXCLUSIVE, SHARE, metadata-only) and estimate how long it will hold that lock given the stated row count and traffic - Assume old and new application code run simultaneously during rollout — flag anything that would break the old code path before it's fully retired - Never recommend a single all-at-once migration for any table over 1 million rows — break it into backward-compatible stages instead - Flag any irreversible operation (dropped columns, renamed columns, changed types) and require a two-step expand-and-contract pattern instead - Do not assume the team has a maintenance window — the plan must work against a live, serving database Output Format: 1. Risk Classification: Safe / Caution / Dangerous, with the one-sentence reason 2. Locking Analysis: exact lock type, estimated hold duration at the stated traffic level, what queries would block during that window 3. Backward-Compatibility Check: will currently-running old code break against the new schema mid-rollout? Yes/no with explanation 4. Step-by-Step Rollout Plan: each step must be independently revertable, numbered, with what to verify before moving to the next step 5. Rollback Plan: the exact reversal for each step above, in case something goes wrong mid-migration
النتيجة
1. Risk Classification: Caution
Adding a NOT NULL column with a static default is safe on PostgreSQL 11+ (the default is stored as metadata, not rewritten row-by-row), but a naive migration tool or an ORM auto-migration can still generate the pre-11 pattern (full table rewrite) without realizing it — that's the real risk here, not the column addition itself.
2. Locking Analysis: If executed as a single ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false; on PostgreSQL 14+, this takes a brief ACCESS EXCLUSIVE lock only long enough to update the table's metadata — typically under 50ms even on a 50-million-row table, since the default value doesn't require rewriting existing rows. At 800 writes/sec, that's a negligible queuing window. However, if your ORM's migration tooling generates a SET NOT NULL as a separate step from adding the column, that specific step DOES require a full table scan to verify no existing rows are null, holding a SHARE UPDATE EXCLUSIVE lock for an estimated 4-7 minutes at this row count — long enough to cause write timeouts under sustained load.
3. Backward-Compatibility Check: No breakage expected. Old application code that doesn't know about the email_verified column will continue to function normally — it simply won't read or write that field. New code reading the column during the ~10-minute rollout window will get the default value (false) for all pre-existing rows, which is the intended behavior here.
4. Step-by-Step Rollout Plan:
Step 1: Add the column as nullable first: ALTER TABLE users ADD COLUMN email_verified BOOLEAN; — this is instant regardless of table size. Verify: check pg_stat_activity for lock waits during and after execution; confirm zero.
Step 2: Backfill existing rows in small batches (e.g., 10,000 rows per transaction) during off-peak hours: UPDATE users SET email_verified = false WHERE email_verified IS NULL AND id BETWEEN [batch_start] AND [batch_end]; — this avoids a single long-running transaction. Verify: monitor replication lag and query latency between batches; pause if either spikes.
Step 3: Once all rows are backfilled, add the NOT NULL constraint using ALTER TABLE users ADD CONSTRAINT email_verified_not_null CHECK (email_verified IS NOT NULL) NOT VALID; followed by VALIDATE CONSTRAINT email_verified_not_null; — the VALIDATE step scans the table but does NOT block concurrent writes, unlike a direct SET NOT NULL. Verify: confirm the constraint validates without errors.
Step 4: Deploy application code that reads/writes the new column. Verify: monitor error rates and the column's null-rate in production for 24 hours before considering the migration complete.
5. Rollback Plan:
Step 4 rollback: redeploy the previous application version; the column being present but unused is harmless.
Step 3 rollback: ALTER TABLE users DROP CONSTRAINT email_verified_not_null; — instant, no lock concerns.
Step 2 rollback: no action needed; backfilled data can remain even if the migration is aborted.
Step 1 rollback: ALTER TABLE users DROP COLUMN email_verified; — safe as long as no application code depends on the column yet.
تشترك معظم كوارث ترحيل قواعد البيانات في سبب واحد: يقوم مطور بتنفيذ تغيير في المخطط يبدو بسيطاً بمعزل عن غيره — إضافة عمود، إعادة تسمية حقل، إضافة قيد — دون فهم نوع القفل الذي تأخذه هذه العملية المحددة على إصدار قاعدة بياناته المحدد. يفرض هذا الموجه هذا التحليل صراحةً، قبل تشغيل أي سطر من كود الترحيل.
القيد الذي يتطلب من النموذج تسمية نوع القفل الدقيق (ACCESS EXCLUSIVE أو SHARE UPDATE EXCLUSIVE أو ميتاداتا فقط) هو جوهر ما يجعل هذا مفيداً بدلاً من عام. أنواع الأقفال المختلفة لها نطاقات تأثير مختلفة تماماً.
يوجد فحص التوافق مع الإصدارات القديمة لأن عمليات النشر المتدرج تعني أن كود التطبيق القديم والجديد يعملان جنباً إلى جنب لفترة من الوقت — غالباً دقائق، وأحياناً أكثر. يمكن لترحيل آمن تماماً للكود الجديد أن يكسر بصمت مسار الكود القديم الذي لا يزال يخدم حركة المرور الحية.
نمط “التوسيع ثم التقليص” الإلزامي للتغييرات التي لا رجعة فيها — عدم إعادة تسمية أو حذف عمود في خطوة واحدة أبداً — هو قيد متعمد ضد الراحة.
تنسيق الخطوة بخطوة مع التحقق المستقل في كل مرحلة مهم لأن عمليات الترحيل نادراً ما تفشل فوراً وبشكل واضح.