SQLite ANALYZE ו-VACUUM: סטטיסטיקות ושחרור מקום

שתי משימות תחזוקה שונות

ANALYZE ו-VACUUM מוזכרים באותה נשימה, אבל הם מתקנים בעיות שונות.

• ANALYZE אוסף סטטיסטיקות על הנתונים שלכם כדי שמתכנן השאילתות יקבל החלטות חכמות יותר. הוא כותב לטבלה בשם sqlite_stat1 ולא נוגע בשורות עצמן.
• VACUUM בונה מחדש את הקובץ עצמו כדי לשחרר דפים שאינם בשימוש ולבטל פרגמנטציה באחסון. הוא לא משנה ישירות תוכניות שאילתה.

אם שאילתות בוחרות באינדקס הלא נכון, אתם צריכים ANALYZE. אם הקובץ גדול ממה שהוא אמור להיות אחרי הרבה מחיקות, אתם צריכים VACUUM. בלבול ביניהם מוביל להרבה זמן תחזוקה מבוזבז.

מה ANALYZE עושה בפועל

מתכנן השאילתות נאלץ לנחש. כשהוא רואה WHERE status = 'active', הוא צריך להעריך כמה שורות מתאימות, אחת? מיליון?, כדי להחליט אם להשתמש באינדקס או לסרוק את הטבלה. בלי סטטיסטיקות, הוא נופל חזרה להיוריסטיקות גסות.

ANALYZE עובר על כל אינדקס ורושם מידע מסכם על התפלגות הערכים:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

השורה ב-sqlite_stat1 אומרת למתכנן בערך כמה שורות יש באינדקס וכמה כפילויות יש למפתח טיפוסי. בפעם הבאה שתשאלו WHERE status = 'pending', הוא יודע ש-pending נדיר ופונה לאינדקס; עבור WHERE status = 'shipped', הוא עשוי להחליט שסריקה זולה יותר.

אפשר להריץ ניתוח על טבלה אחת או על אינדקס אחד במקום על כל מסד הנתונים:

ANALYZE orders;
ANALYZE idx_orders_status;

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

PRAGMA optimize: ברירת המחדל המודרנית

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

PRAGMA optimize;

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

היא זולה כששום דבר לא השתנה ויעילה כשמשהו כן. פנו קודם ל-optimize; פנו ל-ANALYZE הגולמי רק כשצריך לכפות רענון.

מה VACUUM עושה בפועל

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

1. מקום פנוי שמערכת ההפעלה לא רואה. קובץ ה-.db שלכם נשאר בגודל 2 GB למרות שרק 800 MB הם נתונים חיים.
2. פרגמנטציה. שורות של אותה טבלה מתפזרות על פני דפים לא סמוכים, וזה פוגע בביצועי הסריקה.

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

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entry ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

אחרי VACUUM, הקובץ בגודל שהיה לו אילו הכנסתם מאפס רק את 100 השורות ששרדו. כתופעת לוואי, כל ה-rowids נשארים זהים אבל הפריסה בדיסק שוב רציפה.

כמה דברים שכדאי לדעת לפני שמריצים אותו:

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

מתי באמת להריץ VACUUM

לרוב האפליקציות: אל תריצו, אלא אם משהו ספציפי השתנה.

סיבות טובות להריץ VACUUM:

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

סיבות גרועות:

• "ליתר ביטחון." הפקודה כותבת מחדש את כל הקובץ בכל פעם. אין שום דבר בטוח בלעשות את זה על מערכת חיה.
• אחרי כל מקבץ מחיקות. הדפים שהשתחררו היו משמשים שוב בכל מקרה.

auto_vacuum ו-VACUUM הדרגתי

אם אתם רוצים ש-SQLite תנהל דפים פנויים אוטומטית, מגדירים את auto_vacuum בזמן יצירת מסד הנתונים: אי אפשר לשנות את זה אחר כך בלי vacuum מלא:

PRAGMA auto_vacuum = INCREMENTAL;

שלושה מצבים:

• NONE (ברירת המחדל): דפים פנויים נשארים בקובץ ומשמשים שוב בהכנסות מאוחרות יותר.
• FULL: כל commit שמשחרר דפים גם מקצץ את הקובץ. נוח, אבל כל טרנזקציה משלמת את המחיר.
• INCREMENTAL: SQLite עוקבת אחרי דפים פנויים אבל משחררת אותם רק כשמבקשים:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

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

VACUUM INTO: ייצוא עותק קומפקטי

VACUUM INTO כותבת עותק חדש וקומפקטי לקובץ חדש בלי לגעת במקורי:

VACUUM INTO 'backup.db';

זה שימושי באמת:

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

קובץ היעד לא יכול להיות קיים. אם הוא קיים, תקבלו שגיאה.

מתכון תחזוקה מעשי

למסד נתונים טיפוסי של אפליקציה:

-- בכל חיבור ארוך טווח, לפני הסגירה:
PRAGMA optimize;

-- אחרי טעינה גדולה או שינוי סכמה:
ANALYZE;

-- אחרי שמחקתם הרבה נתונים ורוצים את המקום בדיסק בחזרה:
VACUUM;

-- לגיבויים:
VACUUM INTO '/backups/app-2026-04-23.db';

אם מסד הנתונים מונע בעיקר מכתיבות ומחיקות ונשאר מחובר 24/7, הגדירו auto_vacuum = INCREMENTAL בזמן היצירה והריצו PRAGMA incremental_vacuum(N) מדי פעם, למשל פעם ביום בשעות של תנועה נמוכה.

איך מאבחנים "למה הקובץ שלי כל כך גדול?"

שתי פקודות pragma מראות מה קורה:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = גודל הקובץ הנוכחי.
• freelist_count × page_size = בייטים שמבוזבזים על דפים שאינם בשימוש.

אם freelist_count הוא חלק גדול מ-page_count, VACUUM (או incremental_vacuum) יקטין את הקובץ באופן מורגש. אם הוא קטן, הקובץ כבר ארוז ביעילות ו-VACUUM לא יעזור.

מלכודות נפוצות

• הרצת VACUUM בתוך טרנזקציה. זה לא אפשרי. בצעו commit קודם.
• לשכוח ש-VACUUM צריך מקום פנוי בדיסק. מסד נתונים של 10 GB צריך עוד כ-10 GB פנויים כדי לבצע vacuum.
• הגדרת auto_vacuum אחרי שכבר יש נתונים. אין לזה שום השפעה עד ה-VACUUM המלא הבא. הגדירו את זה כשיוצרים את מסד הנתונים אם אתם רוצים את זה.
• הרצת ANALYZE וציפייה לקבצים קטנים יותר. זה התפקיד של VACUUM.
• הרצת VACUUM וציפייה לתוכניות שאילתה טובות יותר. זה התפקיד של ANALYZE.

שתי הפקודות משלימות זו את זו; אף אחת לא מחליפה את השנייה.

הבא בתור: טרנזקציות

פקודות תחזוקה כמו VACUUM מבליטות משהו שלקחנו כמובן מאליו: מודל הטרנזקציות של SQLite ומה הוא נועל, ומתי. הפרק הבא מתחיל שם: איך טרנזקציות עובדות, מה BEGIN / COMMIT / ROLLBACK באמת מבטיחים, ואיך להשתמש בהם כדי לשמור על עבודה של כמה פקודות כפעולה אטומית.