DanLevy.net

חידון: Postgres מעמיק: חלק 2

האם אתה SQL פלדה? או SQL רך?

חלק 2 מתוך 2. חזרה לחלק 1

ברוכים הבאים לחלק 2 של חידון ה-Postgres המעמיק שלי!

חידון זה מכסה תערובת של תכונות PostgreSQL מוכרות ופחות מוכרות, לצד מלכודות נפוצות: JOIN, NULL, ANSI SQL, טרנזקציות, פנימיות, אינדקסים, דגימת נתונים… ועוד!

בהצלחה! 🍀

1. 

חימום: JOINים

איזה מהבאים אינו ❌ ביטוי JOIN חוקי ב-PostgreSQL?

ידעת על CROSS JOIN, נכון?

האם JOIN ALL גרם לך לחשוב על פעולה אחרת, UNION ALL?

התשובה כאן היא JOIN ALL ❌.

סוגי ה-JOIN הנכונים הם:

  • INNER JOIN (ברירת המחדל)
  • LEFT JOIN (או LEFT OUTER JOIN)
  • RIGHT JOIN (או RIGHT OUTER JOIN)
  • FULL JOIN (או FULL OUTER JOIN)
  • CROSS JOIN (מכפלה קרטזית, אלא אם סעיף WHERE מאוחר יותר מסנן אותה)

2. 

עמודות זהות (Identity)

מהו התחביר (תקן SQL) לעמודת IDENTITY עם הגדלה אוטומטית ב-PostgreSQL (v10+)?

למרות ש-SERIAL עדיין בשימוש נפוץ, הדרך התקנית של SQL היא:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • id INT IDENTITY(1,1) הוא תחביר SQL Server.
  • id INT AUTO_INCREMENT הוא תחביר MySQL.
  • id INTEGER AUTO_INCREMENT PRIMARY KEY הוא תחביר MySQL.

לכך יש מספר יתרונות על פני SERIAL:

  • תואם תקן SQL
  • טיפול טוב יותר בבעלות על רצפים (sequence ownership)
  • סמנטיקה ברורה יותר סביב הוספות ידניות
  • עובד טוב יותר עם שכפול (replication)

SERIAL הוא למעשה קיצור דרך ליצירת רצף והגדרת ערך ברירת מחדל, מה שעלול להוביל לבעיות בגיבויים ושחזורים.

3. 

ANSI SQL

איזה סעיף מתועד ב־PostgreSQL כהרחבה לתקן SQL?

PostgreSQL מתעדת את הסעיף RETURNING שלה כהרחבה לתקן SQL. גם ILIKE היא הרחבה של PostgreSQL.

אין לסווג את כל התמיכה במערכים או ב־JSON כלא תקנית: לתקן SQL יש יכולות בתחומים אלה, ול־PostgreSQL יש בנוסף טיפוסים ואופרטורים משלה, כגון jsonb.

יכולות שנוספו במהדורות שונות של תקן SQL כוללות:

  • WITH (ביטויי טבלה משותפים).
  • LIKE להתאמת תבניות.
  • צירופי LATERAL.
  • פונקציות חלון.

4. 

טרנזקציות

למעט ‘תלוי בתופעות לוואי’ מה קורה לאחר שגיאה?

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

כאשר מתרחשת שגיאה כאן:

  • כל הטרנזקציה כולה מתגלגלת אחורה
  • לא מתבצע commit לשינויים
  • הטרנזקציה מסומנת כנכשלה

כדי לבצע commit חלקי:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

5. 

פנימיות SQL

באיזה תנאי ON יכול PostgreSQL להשתמש עבור HASH JOIN?

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

חיבור Hash אפשרי עם תנאי שוויון (=) בסעיף ON.

  • = הוא צורת הפרדיקט המפתח עבור חיבורי Hash
  • > ו->= אינם פרדיקטים של חיבור Hash
  • HASH JOIN אינו תחביר תקין ב-PostgreSQL

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

6. 

אינדקסים

איזה אינדקס מתאים יותר לשאילתה זו?

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

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

הערה: באינדקסי B-tree, העמודה המובילה היא החשובה. מכיוון ששאילתה זו מסננת לפי grade_level ו-last_name, אינדקס שמתחיל ב-grade_level ואחריו last_name הוא מתאים.

ככל ש-SQL מצליח לבחור פחות שורות, הביצועים טובים יותר.

לדוגמה, אם יש לך טבלת task, ואתה יודע ש-status סלקטיבי יותר מ-created_at, ייתכן שתבחר באינדקס שמתחיל ב-status עבור שאילתה כמו status = 'done' AND created_at > '2024-01-01'. סדר התנאים בטקסט של WHERE אינו משנה; סדר העמודות באינדקס וצורת הפרדיקטים הם שקובעים.

ניתן להשתמש באינדקס B+Tree עבור:

  • העמודה המובילה, או קידומת שמאלית של עמודות.
  • אופרטורים כמו =, >, <, BETWEEN, ו-IN, בהתאם לצורת השאילתה.
  • התאמת קידומת, LIKE 'prefix%'.

תודה ל-u/mwdb2 על שהצביע על טעות קודמת בהסבר זה!

7. 

ציטוט 1

מה נכון עבור שמות עמודות תלויי רישיות?

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

ב-PostgreSQL:

  • מרכאות כפולות (\") מיועדות למזהים (שמות טבלאות, שמות עמודות)
  • מרכאות בודדות (') מיועדות לליטרלים של מחרוזות

שיטות עבודה מומלצות:

  • הימנע משילוב רישיות או רווחים בשמות
  • השתמש ב-snake_case עבור מזהים
  • השתמש במזהים מצוטטים רק כשבהחלט הכרחי

8. 

ציטוט 2

איך מציינים שם עמודה המכיל נקודה?

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

PostgreSQL משתמש במרכאות כפולות לציטוט מזהים:

  • מרכאות כפולות "..." עבור מזהים (שמות עמודות, שמות טבלאות)
  • מרכאות בודדות '...' עבור מחרוזות מילוליות
  • הצורה first.name מתפרשת כ-table_name.column_name

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

כדי להפחית הפתעות, עדיף להשתמש ב-snake_case ולהימנע משימוש בתווים מיוחדים.

9. 

דגימת נתונים

מהו התחביר התקף לדגימה אקראית של כ־10% מהסטודנטים?

השאלה הזו מסובכת! היא כוללת בערך 6 מלכודות, חלקן:

  1. תיאור ברמה גבוהה + שברי תחביר. בודקת איך אתה מסתגל ומרכיב מידע חלקי.
  2. דורשת היכרות עם תחביר TABLESAMPLE, מכיוון שאף אחת מהאפשרויות של TABLESAMPLE לא משתמשת בתחביר הנכון! (מרושע, אני יודע! תמשיך, אני מאמין בך!!! ❤️)
  3. ROW_NUMBER() OVER (ORDER BY RAND()) ו-ORDER BY RANDOM() > 10 הם עוד הסחות דעת. הם נראים כאילו הם יכולים להיות נכונים, אבל הם לא. RAND() לא קיים. ו-RANDOM() > 10 הוא ביטוי בוליאני שתמיד יהיה false כי RANDOM() תמיד בטווח 0.0-1.0. בעיה נוספת: ORDER BY RANDOM() > 10 ימיין לפי ערך בוליאני, לא לפי מספר אקראי. אבל ה-> 10 הזה בהחלט הרגיש כמו אחוז, נכון?
  4. BERNOULLI תקין, אבל SAMPLETABLE לא.
  5. WHERE RANDOM() >= 0.1 היא מלכודת! RANDOM() מחזירה ערך בטווח 0.0 <= x < 1.0, אז >= 0.1 תחזיר בערך 90% מהשורות, לא 10%.

לגבי שיטת TABLESAMPLE, PostgreSQL תומך בשתי שיטות דגימה:

  • BERNOULLI: לכל שורה הסתברות שווה
  • SYSTEM: דגימה ברמת בלוק (מהירה יותר אבל פחות אקראית)

למידע נוסף על דגימה, עיין במאמר זה מ-Render.

דוגמה:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);


-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

כדאי גם לציין, ש-TABLESAMPLE דווח כלא אקראי במיוחד. אם אתה צריך דגימה יותר אקראית בגודל קבוע, שקול להשתמש ב-ORDER BY RANDOM() LIMIT 10. אבל שים לב שזה מחזיר 10 שורות, לא 10%, ויכול להיות איטי בטבלאות גדולות.

ישנן גם הרחבות צד שלישי המספקות שיטות דגימה אקראיות טובות יותר, אבל זה מחוץ לתחום של זה! 😅

10. 

אינדקסים

מה הלחש הזה עושה?

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

הטריק הזה נקרא Partial Index.

הם שימושיים להקטנת השימוש בדיסק ולהאצת שאילתות כאשר רק תת-קבוצה של שורות נשאלת בדרך כלל.

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

תזכורת: פוסטגרס לא תומך ב’רמזי שאילתות’ כמו RDBMSs אחרים. מתכנן השאילתות יחליט מתי להשתמש באינדקס(ים) המתאימים.

11. 

התנהגות NULL

כמה שורות זה יחזיר?

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

אוקיי, זה היה קצת מסובך. המלכוד הוא ש-x = NULL לעולם לא יתאים לאף שורה, והוא תמיד יחזיר שורה אחת עם ספירה של 0.

מכיוון ש-NULL הוא ערך לא ידוע, אי אפשר להשוות אותו לשום דבר, אפילו לא לעצמו.

הדרך הנכונה לבדוק ערכי NULL היא באמצעות IS NULL.

דוגמה:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)


postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

12. 

ביצועים

מה עושה EXPLAIN ANALYZE עבור הצהרות UPDATE?

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE מבצע בפועל את השאילתה! היזהר בעת ניתוח פעולת כתיבה, במיוחד הצהרות UPDATE, DELETE ו-INSERT. ייתכן שתצטרך להריץ פעולות ניקוי לאחר כל EXPLAIN ANALYZE כדי לקבל תוצאות ניתנות לשחזור.

שיטות מומלצות:

  • השתמש ב-EXPLAIN (ללא ANALYZE) עבור UPDATE/DELETE/INSERT
  • עטף שינויים בטרנזקציות:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

כל הכבוד! התעמקת בכמה תחומים של PostgreSQL! 🐘

אני מקווה שלמדת משהו חדש, או לפחות קיבלת ציון להתפאר בו! 🏆

רוצה עוד ריגושים בחיים? בדוק את אוסף החידונים שלי לכיף אינסופי*!

Loading comments...