أعمدة PostgreSQL المولدة لتسريع مرشحات لوحة الإدارة

لماذا تصبح شاشات الإدارة بطيئة وفوضوية بسرعة

شاشات الإدارة عادةً تبدأ بسيطة: جدول، بعض الفلاتر، وربما فرز بـ "الأحدث أولاً". ثم يبدأ العمل الحقيقي. الدعم يطلب بحثًا يجري عن العملاء بالاسم أو البريد أو الهاتف. المبيعات تريد فرزًا حسب "آخر نشاط". المالية تريد "الرصيد المتأخر". كل طلب يضيف شروطًا، وصلات، وحسابات إضافية.

تتباطأ معظم قوائم الإدارة لنفس السبب: كل نقرة تغيّر الاستعلام. الفلترة والفرز قد تدفع قاعدة البيانات لمسح الكثير من الصفوف، خصوصًا عندما يضطر الاستعلام لحساب قيمة لكل صف قبل أن يقرر ما الذي يطابق.

نقطة التحول الشائعة هي عندما تمتلئ WHERE و ORDER BY بتعبيرات. بدلًا من الفلترة على عمود عادي، تُفلتر عن lower(email), date_trunc('day', last_seen_at), أو عبارة CASE التي تدمج حالات متعددة في "دلو" واحد. تلك التعبيرات ليست فقط أبطأ. إنها تجعل SQL أصعب قراءةً، أصعب في الفهرسة، وأسهل في أن تُخطئ.

SQL الفوضوي لقوائم الإدارة عادةً يجيء من أنماط متكررة قليلة:

• حقل "بحث" واحد يفحص عدة أعمدة بقواعد مختلفة
• الفرز بقيمة مشتقة (الاسم الكامل، درجة الأولوية، "آخر حدث ذي معنى")
• قواعد عمل مكررة عبر الشاشات (نشط مقابل غير نشط، مدفوع مقابل متأخر)
• لمسات "مساعدة" صغيرة (trim, lower, coalesce) متناثرة في كل مكان
• نفس القيمة المحسوبة تُستخدم في القائمة، الفلاتر، والفرز

غالبًا الفريق يحاول إخفاء ذلك في طبقة التطبيق: منشئو استعلامات ديناميكية، وصلات شرطية، أو حساب القيم مسبقًا في الكود. هذا قد ينجح، لكنه يفرق المنطق بين الواجهة وقاعدة البيانات، ما يجعل تتبع الاستعلامات البطيئة مرهقًا.

الهدف واضح: استعلامات سريعة تظل قابلة للقراءة. عندما تظهر قيمة محسوبة مرارًا عبر شاشات الإدارة، يمكن لأعمدة PostgreSQL المولدة أن تحتفظ بالقاعدة في مكان واحد مع ترك قاعدة البيانات تحسّن الأداء.

الأعمدة المولدة بلغة بسيطة

العمود المولد هو عمود جدول عادي تُحسب قيمته من أعمدة أخرى. أنت لا تكتب القيمة بنفسك. PostgreSQL يملأها باستخدام التعبير الذي تحدده.

في PostgreSQL، الأعمدة المولدة مخزّنة. PostgreSQL يحسب القيمة عند إدراج أو تحديث الصف، ثم يخزّنها على القرص مثل أي عمود آخر. هذا غالبًا ما يكون ما تريده لشاشات الإدارة: قراءات سريعة وإمكانية فهرسة القيمة المحسوبة.

هذا يختلف عن أداء نفس الحساب داخل كل استعلام. إذا استمرت في كتابة WHERE lower(email) = lower($1) أو الفرز بـ last_name || ', ' || first_name، فإنك تدفع الثمن مرارًا وستصبح SQL فوضوية. العمود المولد ينقل ذلك الحساب المتكرر إلى تعريف الجدول. تستصبح الاستعلامات أبسط، والنتيجة متسقة في كل مكان.

عندما تتغير بيانات المصدر، PostgreSQL يحدث القيمة المولدة تلقائيًا لذلك الصف. تطبيقك لا يحتاج إلى تذكُّر مزامنتها.

نموذج ذهني مفيد:

• عرّف الصيغة مرة واحدة.
• PostgreSQL يحسبها عند الكتابة.
• تقرأها الاستعلامات كعمود عادي.
• وبسبب أنها مخزنة، يمكنك فهرستها.

إذا غيرت الصيغة لاحقًا، ستحتاج لتغيير مخطط. خطط لذلك مثل أي ترحيل، لأن الصفوف الموجودة ستُحدّث لتطابق التعبير الجديد.

حالات استخدام مناسبة للحقول المحسوبة في الفلترة والفرز

تتألق الأعمدة المولدة عندما تكون القيمة مشتقة دائمًا من أعمدة أخرى وتقوم بالفلترة أو الفرز عليها كثيرًا. هي أقل فائدة للتقارير لمرة واحدة.

حقول بحث متوافقة مع ما يطلبه المستخدمون

بحث الإدارة نادرًا ما يكون "بحثًا نقيًا". الناس يتوقعون من مربع البحث التعامل مع نص فوضوي، حالات أحرف غير متسقة، ومسافات زائدة. إذا خزنت "مفتاح بحث" مولَّد مُطَبَّع مسبقًا، يبقى جملة الـ WHERE قابلة للقراءة وتتصرّف بنفس الطريقة عبر الشاشات.

مرشحات جيدة تشمل الاسم الكامل المدموج، نص محوَّر إلى أحرف صغيرة ومُقَطَّع، نسخة منظفة تقلّص المسافات، أو تسمية حالة مشتقة من حقول متعددة.

مثال: بدلًا من تكرار lower(trim(first_name || ' ' || last_name)) في كل استعلام، أنشئ full_name_key مرة واحدة وفلتر عليه.

مفاتيح فرز تتماشى مع كيفية فرز البشر

الفرز هو المكان الذي تعود فيه الحقول المحسوبة بأفضل مردود سريعًا، لأن الفرز قد يضطر PostgreSQL لتقييم تعابير لصفوف كثيرة.

مفاتيح الفرز الشائعة تشمل رتبة رقمية (خطة تُربط بـ 1،2،3)، طابع زمني "أحدث نشاط" واحد (مثل أكبر قيمة لِطابعَين)، أو رمز مملوء بالـ padding الذي يفرز صحيحًا كنص.

عندما يكون مفتاح الفرز عمودًا مفهرسًا عاديًا، يصبح ORDER BY أرخص بكثير.

أعلام مشتقة لمرشحات سريعة

يحب مستخدمو الإدارة صناديق الاختيار مثل "متأخر" أو "قيمة عالية". تعمل هذه جيدًا كأعمدة مولدة عندما تكون المنطق ثابتًا ويعتمد فقط على بيانات الصف.

مثال: إذا تحتاج قائمة العملاء إلى "لديه رسائل غير مقروءة" و"متأخر في الدفع"، فإن has_unread بووليان (من unread_count > 0) و is_overdue (من due_date < now() و paid_at is null) يجعل فلاتر الواجهة تتحول إلى شروط بسيطة.

الاختيار بين الأعمدة المولدة، الفهارس، وخيارات أخرى

تحتاج شاشات الإدارة لثلاثة أشياء: فلترة سريعة، فرز سريع، وSQL يمكنك قراءته بعد أشهر. القرار الحقيقي هو أين ينبغي أن يعيش الحساب: في الجدول، في فهرس تعبيري، في view، أم في كود التطبيق.

الأعمدة المولدة تناسب عندما تريد للقيمة أن تتصرّف كعمود حقيقي: سهل الرجوع إليه، مرئي في SELECT، ومن الصعب نسيانه عند إضافة فلاتر جديدة. كما أنها تتوافق بشكل طبيعي مع الفهارس العادية.

فهرس التعبير قد يكون أسرع للإضافة لأنه لا يغير تعريف الجدول. إذا كنت تهتم بالسرعة ولا تمانع SQL أقل قابلية للقراءة، فغالبًا فهرس التعبير يكفي. العيب هو قابلية القراءة، واعتمادك على مطابقة المخطط لتعبيرك تمامًا.

الـ Views مفيدة عندما تريد "شكل" بيانات مشتركًا، خصوصًا إذا كانت قائمة الإدارة تنضم لعدة جداول. لكن الـ Views المعقدة قد تخفي أعمال مكلفة وتضيف مكانًا ثانٍ للتصحيح.

التريجرز يمكن أن تبقي عمودًا عاديًا متزامنًا، لكنها تضيف أجزاء متحركة. يمكن أن تجعل التحديثات بالجملة أبطأ وسهلة الإغفال أثناء التحقيق.

أحيانًا الخيار الأفضل هو عمود عادي تملأه التطبيق. إذا كان المستخدمون يمكنهم تعديله، أو إذا كانت الصيغة تتغير كثيرًا باعتماد قرارات تجارية، فإن إبقائه صريحًا أوضح.

طريقة سريعة للاختيار:

• تريد استعلامات قابلة للقراءة وصيغة ثابتة تعتمد فقط على بيانات الصف؟ استخدم عمودًا مولدًا.
• تريد سرعة لفلتر محدد ولا تمانع SQL فوضوية؟ استخدم فهرس تعبير.
• تحتاج شكلًا منضمًا يُعاد استخدامه في أماكن كثيرة؟ فكر في View.
• تحتاج منطق عابر للجداول أو تأثيرات جانبية؟ فضّل منطق التطبيق أولًا، والتريجرز كحل أخير.

خطوة بخطوة: أضف عمودًا مولدًا واستخدمه في استعلام

ابدأ باستعلام قائمة إدارة بطيء تشعر به في الواجهة. دوّن الفلاتر والفرزات التي يستخدمها الشاشة أكثر. حسّن هذا الاستعلام المفرد أولًا.

اختر الحقل المحسوب الذي يزيل العمل المتكرر، وسَمِّه بوضوح بصيغة snake_case حتى يستطيع الآخرون تخمين محتواه دون قراءة التعبير مرة أخرى.

1) أضف العمود المولد (STORED)

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

تحقق على صفوف حقيقية قبل إضافة الفهارس:

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

إذا كانت النتيجة خاطئة، أصلح التعبير الآن. STORED يعني أن PostgreSQL سيحافظ على تحديثه عند كل إدراج وتحديث.

2) أضف الفهرس الذي يطابق شاشة الإدارة

إذا كانت شاشتك تفلتر بحسب الحالة وتفرز بالاسم، فهكذا فهرس:

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) حدّث استعلام الإدارة لاستخدام العمود الجديد

قبل، قد كان لديك ORDER BY فوضوي. بعد، يصبح واضحًا:

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

استخدم الأعمدة المولدة للأجزاء التي يفلترها ويُفرزها الناس كل يوم، وليس للشاشات النادرة.

أنماط الفهرسة التي تطابق شاشات الإدارة الحقيقية

تكرر شاشات الإدارة سلوكيات قليلة: فلترة بعدد صغير من الحقول، فرز بعمود واحد، وتجزئة الصفحات. الإعداد الأفضل نادرًا ما يكون "فهرس كل شيء". هو "فهرس شكل الاستعلامات الأكثر شيوعًا".

قاعدة عملية: ضع أعمدة الفلتر الأكثر شيوعًا أولًا، ومفتاح الفرز الأكثر شيوعًا في النهاية. إذا كنت متعدد المستأجرين، workspace_id غالبًا يأتي أولًا: (workspace_id, status, created_at).

بحث النص مشكلة منفصلة. كثير من صناديق البحث تختتم بـ ILIKE '%term%'، وهذا صعب تسريعه بفهارس btree الأساسية. نمط مفيد هو البحث في عمود مساعد مُطَبَّع بدل النص الخام (أحرف صغيرة، مشذّب، ربما مدموج). إذا كان واجهتك تسمح بالبحث بالبادئة (term%)، ففهرس btree على ذلك العمود المُطَبَّع قد يساعد. إذا كان يجب أن يكون بحث احتواء (%term%)، فكّر بتقييد واجهة المستخدم أو تقليل مدى البحث.

وتحقّق من الانتقائية قبل إضافة الفهارس. إذا كانت 95% من الصفوف لها نفس القيمة (مثل status = 'active')، ففهرسة ذلك العمود وحده لن تساعد كثيرًا. اقترنه بعمود أكثر تمييزًا، أو استخدم فهرس جزئي للحالات الأقل شيوعًا.

مثال واقعي: قائمة عملاء إدارية تبقى سريعة

تخيل صفحة إدارة عملاء نموذجية: مربع بحث، بعض الفلاتر (غير نشط، نطاق الرصيد)، وعمود "آخر ظهور" قابل للفرز. مع الوقت تتحول إلى SQL صعبة القراءة: LOWER(), TRIM(), COALESCE(), حسابات زمنية، وكتل CASE مكررة عبر الشاشات.

طريقة لجعلها سريعة وقابلة للقراءة هي دفع تلك التعبيرات المتكررة إلى أعمدة مولدة.

الجدول والأعمدة المولدة

افترض جدول customers مع name, email, last_seen, و balance. أضف ثلاثة حقول محسوبة:

• search_key: نص مُطَبَّع للبحث البسيط
• is_inactive: بووليان يمكنك الفلترة عليه دون تكرار منطق التاريخ
• balance_bucket: تسمية لتقسيم سريع

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen < (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance < 0 THEN 'negative'
        WHEN balance < 100 THEN '0-99'
        WHEN balance < 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

الآن استعلام الإدارة يقرأ مثل الواجهة.

فلترة وفرز قابلة للقراءة

"العملاء غير النشطين، أحدث نشاط أولًا" يصبح:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

وبحث أساسي يصبح:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

المكسب الحقيقي هو التناسق. نفس الحقول تغذي شاشات متعددة بدون إعادة كتابة المنطق:

• مربع بحث العملاء يستخدم search_key
• تبويب "العملاء غير النشطين" يستخدم is_inactive
• رقاقات فلترة الرصيد تستخدم balance_bucket

الأخطاء الشائعة والفخاخ

يبدو أن الأعمدة المولدة فوز بسيط: ضع العمليات في الجدول واحتفظ باستعلاماتك نظيفة. لكنها تساعد فقط عندما تطابق كيفية فلترة الشاشة وفرزها، وعندما تضيف الفهرس الصحيح.

الأخطاء الأكثر شيوعًا:

• افتراض أنها تسرّع الأمور دون فهرسة. القيمة المحسوبة تحتاج فهرسًا للفلترة أو الفرز السريع على نطاق واسع.
• حشر الكثير من المنطق في حقل واحد. إذا أصبح العمود المولد "برنامجًا صغيرًا"، سيفقد الناس الثقة به. اجعله قصيرًا وسمِّه بوضوح.
• استخدام دوال غير غير متغيرة. PostgreSQL يتطلب أن يكون التعبير للعمود المولد المخزن غير متغير. أشياء مثل now() و random() تكسر التوقعات وغالبًا لا تُسمح.
• تجاهل تكلفة الكتابة. يجب على الإدراجات والتحديثات الحفاظ على القيمة المحسوبة. قراءات أسرع لا تستحق إذا تباطأت الاستيرادات والتكاملات كثيرًا.
• خلق مكررات شبه متطابقة. قيِّم نمطًا واحدًا أو اثنين (مثل مفتاح موحّد) بدل تراكم خمسة أعمدة متشابهة.

إذا كانت قائمة الإدارة تقوم ببحث احتواء (مثل ILIKE '%ann%')، فعمود مولد بمفرده لن ينقذه. قد تحتاج نهج بحث مختلف. لكن لعمل الخيّاط اليومي "فلترة وفرز"، الأعمدة المولدة مع الفهرس المناسب عادةً تجعل الأداء أكثر توقعًا.

قائمة تحقق سريعة قبل الإطلاق

قبل نشر التغييرات لقائمة الإدارة، تأكد أن القيمة المحسوبة، الاستعلام، والفهرس متطابقة.

• الصيغة ثابتة وسهلة الشرح بجملة واحدة.
• استعلامك يستخدم العمود المولد فعليًا في WHERE و/أو ORDER BY.
• الفهرس يطابق الاستخدام الحقيقي، ليس اختبارًا لمرة واحدة.
• قارنت النتائج مع المنطق القديم لحالات الحافة (NULLs، سلاسل فارغة، مسافات غريبة، حالة مختلطة).
• اختبرت أداء الكتابة إذا كان الجدول مشغولًا (استيرادات، تحديثات خلفية، تكاملات).

خطوات تالية: طبق هذا على شاشات الإدارة لديك

اختر نقطة بداية صغيرة وذات تأثير كبير: 2-3 شاشات يفتحها الناس طوال اليوم (الطلبات، العملاء، التذاكر). اكتُب ما يبدو بطيئًا (فلتر نطاق تاريخ، فرز بـ "آخر نشاط"، بحث بالاسم المدموج، فلترة بتسمية الحالة). ثم قِس مجموعة قصيرة من الحقول المحسوبة القابلة لإعادة الاستخدام.

خطة نشر سهلة القياس وسهلة التراجع:

• أضِف العمود(ات) المولدة بأسماء واضحة.
• شغّل القديم والجديد جنبًا إلى جنب لفترة قصيرة إذا كنت تستبدل منطقًا موجودًا.
• أضِف الفهرس الذي يطابق الفلتر والفرز الأساسي للشاشة.
• غيّر استعلام الشاشة لاستخدام العمود الجديد.
• قِس قبل وبعد (زمن الاستعلام وعدد الصفوف الممسوحة)، ثم أزل الحل المؤقت القديم.

إذا كنت تبني أدوات إدارية داخل AppMaster (appmaster.io)، تناسب هذه الحقول المحسوبة نموذج بيانات مشتركًا: قاعدة البيانات تحمل القاعدة، وفلاتر واجهتك يمكن أن تشير إلى اسم حقل واضح بدل تكرار التعبيرات عبر الشاشات.