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

نمذجة البيانات على نطاق واسع

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

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

نظرة عامة

الموضوعات

  • مقايضات التطبيع (Normalization)
  • إلغاء التطبيع (Denormalization)
  • التقسيم (Partitioning)

تعلّم أولًا

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

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

  1. Decide when to denormalize for read performance and how to keep copies consistent.
  2. Partition a large table by range or list and explain partition pruning.
  3. Use jsonb columns appropriately and index them.
  4. Plan data retention and archiving.

الدروس

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

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

Lab

MID-019-LAB1 · Partitioned orders

Outcomes: 1–4 · Time: ~2.5 h Partition the 10 M-row order table by month. Compare query time for a one-month report and for a customer lookup before and after, and compare the cost of removing one old month (detach versus DELETE). Add a materialized view for daily sales and refresh it concurrently. Acceptance: pruning visible in plans; old partitions detachable; comparison table of timings.

Exercises (assessment: modeling scenario)

IDOutcomeTask
MID-019-EX011For a slow report needing five joins, propose one denormalization and name how you keep it consistent.
MID-019-EX021Decide which of these are duplication and which are valid snapshots: product_name in order items, customer_email in orders, price_at_purchase.
MID-019-EX032Create a range-partitioned table with three monthly partitions and a default partition.
MID-019-EX042Write a query that prunes to one partition and one that cannot, and show both plans.
MID-019-EX053Store variable product attributes as jsonb, index them, and query by containment.
MID-019-EX063List three fields in a jsonb-heavy design that should become columns.
MID-019-EX074Write a retention plan for orders, audit log, and sessions with archive method for each.
أظهر الحلول
  • EX01 For example a daily_sales materialized view refreshed nightly or an order_total column maintained in the same transaction; state the allowed staleness.
  • EX02 price_at_purchase and arguably product_name are snapshots; customer_email in orders is a copy that should be justified (snapshot of the address used) or removed.
  • EX03 PARTITION BY RANGE (created_at), three FOR VALUES FROM ... TO ... partitions, and CREATE TABLE ... PARTITION OF orders DEFAULT.
  • EX04 WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01' scans one partition; WHERE customer_id = 7 scans all partitions (no key filter).
  • EX05 attrs jsonb, CREATE INDEX ... USING gin (attrs jsonb_path_ops), WHERE attrs @> '{"color":"red"}'.
  • EX06 Foreign keys, status fields used in filters and joins, amounts used in calculations.
  • EX07 For example orders: keep hot 2 years in the database, archive older partitions to files; audit log: monthly partitions kept 1 year; sessions: delete after 30 days. Values are examples; check reasoning.
  • The "partitioning small tables" and "jsonb as schema avoidance" mistakes are covered by EX06 and the quiz.

الاختبار

Outcomes: O1 normalization and denormalization · O2 partitioning · O3 jsonb · O4 retention.

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

  1. 1Denormalization is justified by:O1
  2. 2REFRESH MATERIALIZED VIEW CONCURRENTLY requires:O1
  3. 3price_at_purchase stored in an order item is:O1
  4. 4Partition pruning happens when the query:O2
  5. 5In a range-partitioned table, the primary key must:O2
  6. 6A good reason to partition by month:O2
  7. 7The index type usually used for jsonb containment:O3
  8. 8jsonb is a poor choice for:O3
  9. 9Best way to remove a year of old orders from a partitioned table:O4
  10. 10Archived data should be:O4

الجزء العملي

Modeling scenario: given a growing orders-and-events schema, decide what to denormalize, what to partition (and by what key), what goes in jsonb, and the retention plan. Pass: quiz 8/10, a scenario answer that justifies each decision, and the lab showing pruning.

أخطاء شائعة

Partitioning small tables; jsonb as a schema-avoidance strategy.

التقييم

Modeling scenario.

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

Sharding and hot partitions (SRC-007).

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