انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
MID-020

ترحيلات قواعد البيانات

  • 6 درس
  • 2.5 ساعة تدريب (تقدير)
ابدأ الوحدة
في هذه الصفحة 8 موضوعات

نص الدروس والتمارين باللغة الإنجليزية، بينما تبقى واجهة الموقع وعناوين الدروس بالعربية.

نظرة عامة

الموضوعات

  • التوسيع ثم الانكماش (Expand-contract)
  • تغييرات المخطط دون توقف
  • استراتيجيات التراجع

تعلّم أولًا

2.5 ساعة تدريب (تقدير)

مخرجات التعلم

  1. Apply expand-contract to rename or restructure columns without downtime.
  2. Identify migrations that take heavy locks and use safe alternatives (CREATE INDEX CONCURRENTLY, lock timeouts).
  3. Backfill large tables in batches.
  4. Plan rollback for each migration step.

الدروس

  1. 01لماذا تسبب الترحيلات انقطاعات
  2. 02التوسيع والانكماش
  3. 03DDL آمن من ناحية الأقفال
  4. 04ملء البيانات القديمة على دفعات
  5. 05استراتيجيات التراجع
  6. 06عرض عملي: إعادة تسمية عمود أثناء حركة مرور حية

المختبر والتمارين

Lab

MID-020-LAB1 · Zero-downtime rename

Outcomes: 1–4 · Time: ~3 h Rename customer.name to full_name across several releases of Shop while a load test runs. Use Flyway for each step, a batched backfill, lock_timeout, and a written rollback for each release. Acceptance: zero failed requests during the whole migration; a rollback note per step.

Exercises (assessment: migration plan review)

IDOutcomeTask
MID-020-EX011Write an expand-contract plan to split address into street and city.
MID-020-EX021Explain what each running app version needs from the schema at each step of your plan.
MID-020-EX032Classify these as safe or risky and give the safe form: add index, add NOT NULL column, add foreign key, change column type.
MID-020-EX042Show a migration that sets lock_timeout and creates an index concurrently; explain why it cannot run in a transaction.
MID-020-EX053Write a resumable batched backfill for 50 M rows and explain its batch size and pauses.
MID-020-EX064Fill a rollback table for each step of EX01.
MID-020-EX074Review a one-step migration plan (rename + drop + add NOT NULL) and list its failure modes.
أظهر الحلول
  • EX01 Release 1 add nullable street, city; 2 dual-write; 3 backfill by parsing; 4 read new; 5 stop writing address; 6 drop address.
  • EX02 Old app: needs the old columns until it is gone; new app: tolerates missing data in the new columns until backfill finishes.
  • EX03 Index: use CONCURRENTLY. NOT NULL column: add nullable, backfill, then constraint (or constant default). Foreign key: NOT VALID then VALIDATE. Type change: new column plus backfill plus swap, not an in-place rewrite.
  • EX04 CREATE INDEX CONCURRENTLY waits for existing transactions and cannot be wrapped in a transaction block; configure Flyway to run that script outside a transaction.
  • EX05 Loop by id range of 5 000 rows, each in its own transaction, WHERE new IS NULL, a short sleep, and a stored progress marker; check replication lag.
  • EX06 Expand steps: redeploy the prior app, leave columns; backfill: rerun or clear; switch reads: redeploy old; contract: restore from backup (point of no return).
  • EX07 Old instances fail on rename or drop; NOT NULL fails on existing nulls or blocks while scanning; no rollback; table lock queue pile-up.
  • The "volatile default" mistake is discussed in EX03 and the quiz.

الاختبار

Outcomes: O1 expand-contract · O2 lock-safe DDL · O3 batched backfills · O4 rollback planning.

اختبار ذاتي لك: لا توجد درجات، ولا يُحفظ أو يُرسل أي شيء. اختر إجابة ثم تحقق منها، ويمكنك المحاولة مرة أخرى كما تشاء.

  1. 1Safe way to rename a column with old and new app versions running:O1
  2. 2The contract step means:O1
  3. 3Why can the app and schema change not be deployed at the same instant?O1
  4. 4A waiting ALTER TABLE can cause an outage because it:O2
  5. 5To create an index without blocking writes:O2
  6. 6ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT is used to:O2
  7. 7lock_timeout makes a migration:O2
  8. 8Backfill a huge table:O3
  9. 9A good backfill condition is:O3
  10. 10The point of no return in expand-contract is usually:O4

الجزء العملي

Migration plan review: find the risky steps in a given plan and rewrite them as safe, reversible steps with rollbacks. Pass: quiz 8/10, all risky steps fixed, and the lab with zero failed requests.

أخطاء شائعة

Rename in one step; adding a NOT NULL column with a volatile default on a huge table.

التقييم

Migration plan review.

الصلة بالمسار الوظيفي

Large migrations (SRB-017).

اكتب للبحث في المستويات والمراحل والوحدات والدروس.