חזרה לפיד
articlesמקור: GitGuardian Blog18.6.2026

כיצד חמש אופטימיזציות ב-PostgreSQL האיצו את הדאשבורד שלנו

אמ;לק

GitGuardian מסייעת לצוותי אבטחה לזהות סודות שנחשפו דרך עשרות אינטגרציות שמתרכזות בדאשבורד אחד. עם גידול בנפח הנתונים, זמני הטעינה התארכו, ועל כן ביצענו אופטימיזציות ב-PostgreSQL כדי לשפר את הביצועים באופן משמעותי.

סיכום בעברית: מערכת VibeSec · מדיניות המקורות

נוסף ל-VibeSec:

כותרת מקורית: How Five PostgreSQL Optimizations Sped Up Our Dashboard

כיצד חמש אופטימיזציות ב-PostgreSQL האיצו את הדאשבורד שלנו

התוכן המלא בעברית

תרגום אוטומטי מהמקור; מונחים טכניים נשארו באנגלית. לנוסח המחייב — הכתבה המקורית.

לוח המחוונים שלנו פועל על בסיס מונוליט Django המגובה ב-PostgreSQL. התשובה הקנונית לבעיות ביצועים בחיפוש וסינון בהיקפים גדולים היא דה-נורמליזציה (denormalization) לתוך מאגר נתונים מותאם לשאילתות, ואנחנו אכן נעים בכיוון הזה עם ClickHouse. אך הקמת מסד נתונים משני היא השקעה לטווח ארוך. לפני שהתחייבנו לכך, רצינו לראות כמה רחוק נוכל לדחוף את PostgreSQL, ובתוך כך לספק הקלה מהירה יותר למשתמשים שלנו.

המדד שהחלטנו לבצע עבורו אופטימיזציה היה ערך ה-p95 המקסימלי בנקודות הקצה עבור לקוחות בתוכניות עסקיות. כדי להבין את החשיבות של התבוננות ב-p95, קחו בחשבון שערך ה-p50 שלנו בתחילת היוזמה עמד על 0.7 שניות, מה שנראה מספק יחסית. עם זאת, p50 אינו משקף את המקרים הנדירים שבהם לקוח משתמש במסננים הגורמים לביצועים חריגים, שלוקחים שניות ארוכות עד להחזרת תוצאה. ואכן, אותה נקודת קצה שהציגה p50 של 0.7 שניות בתחילת הפרויקט, הציגה p95 של 8 שניות.

הצבנו לעצמנו יעד לצמצם באופן דרסטי את המספר הזה לרמה שלא תעלה על 3 שניות. כפי שתראו בסיכום, למעשה עקפנו את היעד הזה והגענו ל-p95 של 1 שניות.

במאמר זה נסביר את הבעיות השונות שזיהינו שתרמו למצב זה, וכיצד פתרנו אותן.

מינוף יכולות ניטור (Observability)

הצעד הראשון לפני שנגענו במשהו היה הגדרת יכולות הניטור (observability) הדרושות כדי לאבחן את מקור הבעיות, וכן למדוד את ההשפעה של הפתרונות שניישם.

הגדרנו את המדדים הדרושים שאפשרו לנו לנטר בכל עת את ה-p95 עבור לקוחות משלמים בנקודת קצה נתונה, ולעקוב בזמן אמת אחר ההתקדמות שלנו לעבר רף 3 השניות.

הצלחנו למנף את ה-traces של OpenTelemetry כדי לבחון את בקשות ה-HTTP האיטיות ביותר מצד לקוחות משלמים, ובפרט את שאילתות ה-SQL האיטיות ביותר בתוך הבקשות הללו.

לבסוף, יכולנו להריץ מחדש באופן ידני את הבקשות הללו על עותק (replica), תוך שימוש בפקודות EXPLAIN ANALYZE כדי להבין מדוע הן היו איטיות.

עם הכלים הללו במקומם, נוכל לצלול לתיקונים המעשיים. ראשית: "Late Joining", שינוי קטן במבנה השאילתה עם השפעה משמעותית.

⁦Late Joining⁩

אחת המגבלות של PostgreSQL היא שהוא אינו מבטיח שפעולות JOIN על טבלאות לא מסוננות יידחו עד לאחר החלת תנאי ה-WHERE. לדוגמה, שקלו את השאילתה הבאה שסופרת משאבי משוב הקשורים לטבלת אירועים (incidents):

⁦SELECT i.*, COUNT(f.id) AS feedbacks_count FROM incident i LEFT JOIN incident_feedback f ON f.issue_id = i.id WHERE i.account_id = 42 AND i.severity < 10 GROUP BY i.id ORDER BY i.id DESC LIMIT 50;⁩

היינו מצפים מ-PostgreSQL לאחזר את המשוב על התקריות רק לאחר שהאירועים סוננו לפי הקריטריונים account_id = 42 ו-severity < 10. בפועל, אין ערובה שזהו אופן הפעולה ש-PostgreSQL יבחר בו. תוכנית השאילתה עשויה להחליט לבצע את ה-JOIN לפני הסינון, מה שעלול להיות יקר למדי בהתאם לגודל הטבלה המצורפת.

עם זאת, ישנה שיטה להתמודד עם מגבלה זו:

• בצעו שאילתה ראשונה שמבצעת JOIN רק על שדות הסינון.

• לאחר קבלת סט השורות, בצעו שאילתות נפרדות עבור שדות התצוגה בטבלאות הרלוונטיות, תוך סינון לפי מפתחות זרים התואמים לשורות משלב 1.

בדוגמה שלנו, זה מתורגם לשאילתה ראשונה שמיישמת בצורה קפדנית את המסננים כדי לקבל עמוד של מזהי תקריות (incident IDs):

⁦SELECT i.id FROM incident i WHERE i.account_id = 42 AND i.severity < 10 ORDER BY i.id DESC LIMIT 50;⁩

...שאחריה תבוא שאילתה שנייה שמאחזרת את כל השדות הנדרשים להצגת העמוד הזה. במקרה של המשוב:

⁦SELECT f.incident_id, COUNT(*) AS feedbacks_count FROM incident_feedback f WHERE f.incident_id IN (1001, 1002, 1003, ...) GROUP BY f.incident_id;⁩

למזלנו, ה-Django ORM הופך את האופטימיזציה הזו לקלה מאוד: JOINs רגילים מבוצעים באמצעות פונקציה הנקראת select_related, בעוד שהתבנית שהוסברה לעיל מיושמת באמצעות prefetch_related. לכן, זה היה רק עניין של החלפת השיטה שבה נעשה שימוש, ולאחר מכן ביצוע רענון לכלי העבודה שלנו סביב התצוגות כדי להפוך את התבנית הזו לבחירה ברורה יותר עבור מפתחים בארגון.

ספירה אסינכרונית

בעת סינון משאב, בנוסף לעמוד התוצאות, לוח המחוונים שלנו מציג את המספר הכולל של הפריטים עבור המסננים הנתונים. הדרך שבה יישמנו זאת בתחילה הייתה על ידי החזרת מאפיין ספירה (count) ב-payload של ה-API, לצד הפריטים עצמם. יישום כזה עוקב באופן טבעי אחר השיטה של Django המבוססת על ההפשטה Paginator.

באופן טבעי, ככל שהנתונים גדלים, שאילתת ה-SQL להפקת הספירה צריכה לסרוק מספר הולך וגדל של שורות, בעוד שהשאילתות להפקת עמוד פריטים בודד נהנות מגודל עמוד מוגבל. יכולנו לצפות בתופעה זו ב-traces שלנו, עם דוגמאות רבות לכך ששאילתת הספירה צורכת זמן רב משמעותית מהשאילתות שמאחזרות את הפריטים עצמם.

דוגמה ל-trace של בקשה איטית באופן חריג לפני שהטמענו ספירה אסינכרונית: ה-trace העליון הוא שאילתת SQL COUNT, בעוד שהשאילתה שאחריה היא שאילתת SELECT המאחזרת עמוד אחד של תוצאות.

דרך לפתור בעיה זו מבלי להתפשר יותר מדי על ⁦UX⁩ היא לפצל את הבקשות ⁦HTTP⁩ ל-2 בקשות מקבילות:

המשתמש יכול להסתכל על התוצאות המוצגות ברגע שבקשה 1 מסתיימת, בעוד שהספירה הכוללת מגיעה באופן אסינכרוני מאוחר יותר.

ספירה אסינכרונית: השאילתה הראשונה מאחזרת עמוד תוצאות במהירות האפשרית, בעוד ששאילתה שנייה תובעת את הספירה הכוללת עבור המסננים הנתונים.

⁦Premium Replication⁩

גורם אחד שמשפיע מאוד על ביצועי השאילתות הוא ה-cache של מסד הנתונים. ההבדל בביצועים בין שאילתה המשתמשת ב-cache קר לעומת cache חם יכול להיות דרמטי. בכמה דוגמאות, מדדנו ביצועים מהירים פי 20 במצב של cache חם.

מלבד ניתוחים המוגשים על ידי Snowflake, כל תעבורת הייצור שלנו הוגשה על ידי מופע ה-PostgreSQL הראשי שלנו, מה שאומר שה-cache של PostgreSQL היה משותף לכל התכונות ולכל הלקוחות (משתמשים בשכבה החינמית לצד לקוחות משלמים).

כדי לשמור קיבולת cache גדולה יותר ללקוחות משלמים המשתמשים בתצוגות הלוח המחוונים החמות ביותר שלנו, יצרנו העתק (replica) של מסד הנתונים כולו, המשמש אך ורק לקריאה על ידי לקוחות משלמים ועבור נקודות הקצה הפעילות ביותר.

בנוסף לאספקת חוויה חלקה יותר ללקוחות, העתק זה מספק בידוד טוב יותר מפני "שכנים רועשים". ואכן, תצוגות לוח המחוונים העיקריות שלנו אדישות כעת לקפיצות פוטנציאליות בניצול ה-CPU של מסד הנתונים, שעלולות להגיע מכל מקום אחר באפליקציה.

בצד שמאל: תהליך כבד מפעיל עומס CPU על מופע מסד הנתונים הראשי שלנו. בצד ימין, העתק ה-Premium שמשמש את הלקוחות העסקיים לגישה לתצוגות החמות של הלוח אינו מושפע מהעומס הזה.

אופטימיזציות לחיפוש בטקסט מלא

תצוגת רישום האירועים שלנו תומכת בחיפוש טקסט מלא (full-text search) על פני הרבה מאוד מאפיינים. בבחינת ה-traces, ראינו שמחרוזת החיפוש הייתה אחד הגורמים המשמעותיים ביותר לבקשות איטיות (בקשות שנמשכו יותר מ-3 שניות).

חיפוש הטקסט שלנו מופעל על ידי התוסף pg_trgm, שמפרק מחרוזות לכל הטריגרמות שלהן ומוסיף אותן לאינדקס מול השורות המכילות את הטריגרמה התואמת. כאשר מחפשים טקסט באחת מהעמודות המאונדקסות, שאילתת החיפוש מפורקת שוב לטריגרמות, אשר נשאלות לאחר מכן באמצעות האינדקס.

מכיוון שאנו תומכים בחיפוש אחר תכונות שונות הקשורות לאירוע, נתקלנו בשתי בעיות:

• מבחינת מוצר, ה-UX לא היה אופטימלי, מכיוון שלקוחות התקשו להבין איזה מאפיין של פריט נתון גרם לו להופיע בתוצאות החיפוש.

• מבחינה טכנית, השאילתה הבסיסית הייתה הצהרת UNION שאיחדה תוצאות של שאילתות שונות שסיננו טבלאות שונות.

כדי לטפל בשתי הבעיות הללו, שיפרנו את חווית החיפוש כך שבמקום להחזיר תוצאות ישירות, חיפוש הטקסט המלא מתבצע בנפרד על המאפיינים הנתמכים, ואז מאפשר למשתמש לבחור מאפיין אחד לסינון.

• משתמשים יודעים בדיוק על מה הם מסננים

• ניתן לחלק חיפושים בכמה תכונות נפרדות למספר בקשות, שכל אחת מהן מהירות יותר, וניתן לבקש אותן במקביל.

• הקצה הקדמי יכול להשתמש במטמון מקומי כדי להימנע מלבקש מחדש את התכונות התואמות עבור שאילתה נתונה.

• ברגע שמשתמשים בוחרים את המסנן המיועד, זה כבר לא חיפוש טקסט מלא, שכן התוצאות נשלפות על סמך המאפיין הספציפי שנבחר.

דנורמליזציה

הזווית האחרונה שחקרנו הייתה אופטימיזציה של סינון בשדות ספציפיים שבהם הבחנו בביצועים חריגים ב-traces.

על ידי ניתוח תוכניות השאילתה, גילינו שכאשר מסננים וממיינים תוצאות מעומדות (paginated), PostgreSQL חייב לבחור בין שתי אסטרטגיות:

• סריקת הטבלה הראשית לפי סדר המיון, החלת סינון שורה-אחר-שורה, ועצירה ברגע שנמצאו N תוצאות.

• שימוש באינדקס הסינון כדי למצוא את כל השורות התואמות מראש, ביצוע JOIN שלהן חזרה לטבלה הראשית, מיון כל סט התוצאות, והחזרת N האלמנטים הראשונים.

אסטרטגיה 1 יעילה כאשר שורות תואמות נפוצות בטבלה: ⁦PostgreSQL⁩ מוצא ⁦N⁩ תוצאות במהירות. אבל כאשר המסנן סלקטיבי, הוא עשוי לסרוק את כל הטבלה לפני שימצא מספיק התאמות. אסטרטגיה 2 מטפלת היטב במסננים סלקטיביים, אך מתדרדרת כאשר שורות רבות מתאימות.

⁦PostgreSQL⁩ בוחר ביניהם באמצעות סטטיסטיקה לכל טבלה, שהיא הסתברותית. במקרים מסוימים הוא בוחר באסטרטגיה שגויה, מה שמוביל לשאילתות איטיות באופן עקבי עבור מסננים מסוימים.

התיקון האמין הוא אינדקס מורכב המכסה הן את קריטריוני הסינון והן את קריטריוני המיון, מה שמאפשר למתכנן השאילתות למצוא שורות תואמות ולעבור עליהן לפי הסדר ללא צורך במיון נפרד. הקאץ' הוא שאינדקסים מורכבים חייבים להיות על טבלה אחת, לכן כאשר שדה הסינון שייך לטבלה קשורה, יש לבצע דה-נורמליזציה שלו לטבלה הראשית או לטבלת דה-נורמליזציה ייעודית (בחרנו באחרונה).

לדוגמה, שקול את השאילתה הזו, המסננת אירועים לפי תקפות הסודות שלהם:

⁦SELECT * FROM incident i JOIN secret s ON i.secret_id = s.id WHERE i.account_id = 42 AND s.validity = 'valid' ORDER BY i.date DESC LIMIT 50;⁩

על ידי ביטול נורמליזציה של העמודה secret.validity לצד תאריך האירוע, נוכל כעת ליצור את האינדקס המשולב הבא:

⁦(account_id, validity, issue_date) ^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^ matches filters | matches ordering⁩

באמצעות אינדקס זה, PostgreSQL מסוגל להתאים ישירות את החלק של האינדקס התואם למסננים, ולאחר מכן לסרוק את האינדקס לפי סדר המיון, ולהפסיק ברגע שיש לו 50 ערכים.

שדרוג ל-PostgreSQL 18

שיפורים ברמת ה-PostgreSQL היו גם הם חלק מהפרויקט. במקביל לשינויי היישום והשאילתות המתוארים לעיל, שדרגנו את סביבת הייצור שלנו ל-PostgreSQL 18. זה העניק לנו "זכיות" מצטברות בחינם בחלק מעומסי העבודה, ולא פחות חשוב, הציב אותנו על בסיס חזק יותר לעבודת ביצועים עתידית.

נקודת המפתח היא שהשדרוג היווה מאיץ, לא פתרון קסם: הרווחים הגדולים ביותר עדיין הגיעו מהשינויים הממוקדים שעשינו בעיצוב השאילתות, אסטרטגיית הספירה, התנהגות החיפוש ובידוד ה-cache. עם זאת, הרצה על PostgreSQL 18 עזרה לנו לייצב את הרווחים הללו ולהפחית את הסיכון לרגרסיה לאורך זמן.

התוצאות

סקיצה טובה עדיף על נאום ארוך. 👇

• מספר העקבות בתצוגת לוח המחוונים החמה ביותר שלנו שגדולים או שווה ל-3 שניות.

• ה-⁦p95⁩ של נקודת קצה זו עבור הלקוחות המשלמים שלנו.

בתרשים הראשון, ניתן להבחין בקבוצות של 5 "קוצים" המופרדים על ידי קטעים שטוחים יותר, התואמים לימי חול וסופי שבוע. הגרף השני מוחלק על פני תקופה של 7 ימים כדי להקל על קריאת המגמות. בשני התרשימים, הערות אנכיות תואמות את תאריכי הפריסה של חלקים שונים מהאופטימיזציות שנחשפו במאמר זה.

הערך האחרון של p95 מימין בתרשים השני הוא 1 שניות, מתחת ליעד ה-3 שניות שלנו במידה ניכרת.

מַסְקָנָה

#database-optimization#postgresql#postgres#performance-tuning#dev-experience
קרא במקור

קריאה וכלים קשורים

אבטחת Supabase / PostgreSQLמונחי אבטחה והסברים בעברית