ניהול שמירת נתונים באמצעות TTL

בדף הזה מוסבר איך להשתמש בערך הזמן לחיים (TTL) בטבלאות Spanner במסדי נתונים עם ניב GoogleSQL ובמסדי נתונים עם ניב PostgreSQL. מידע נוסף זמין במאמר מידע על TTL.

לפני שמתחילים

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

הפעלת גיבוי ושחזור מערכת מנקודה מסוימת בזמן (PITR)

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

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

ניקוי נתונים ישנים

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

אימות התנאים

בטבלאות GoogleSQL, אם רוצים לאמת את הנתונים שהמדיניות בנושא מחיקת נתונים משפיעה עליהם לפני שמפעילים את ה-TTL, אפשר לשלוח שאילתה לטבלה באמצעות אותם תנאים. לדוגמה:

GoogleSQL 

  SELECT COUNT(*)
  FROM CalculatedRoutes
  WHERE TIMESTAMP_ADD(CreatedAt, INTERVAL 30 DAY) < CURRENT_TIMESTAMP();

ההרשאות הנדרשות

כדי לשנות את הסכימה של מסד הנתונים, צריכה להיות לכם הרשאה spanner.databases.updateDdl. פרטים נוספים זמינים במאמר בנושא בקרת גישה ב-Spanner.

יצירה של מדיניות למחיקת שורות

GoogleSQL

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

כדי להוסיף מדיניות בזמן יצירת הטבלה:

CREATE TABLE MyTable(
Key INT64,
CreatedAt TIMESTAMP,
) PRIMARY KEY (Key),
ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));

מחליפים את מה שכתוב בשדות הבאים:

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

  • ‫num_days הוא מספר הימים שעברו מאז חותמת הזמן ב-timestamp_column שבה השורה מסומנת למחיקה. הערך חייב להיות מספר שלם לא שלילי, ויחידת המידה היחידה שנתמכת היא DAY.

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

ALTER TABLE Albums
ADD ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));

PostgreSQL

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

כדי להוסיף מדיניות בזמן יצירת הטבלה:

CREATE TABLE mytable (
  key bigint NOT NULL,
  timestamp_column_name TIMESTAMPTZ,
  PRIMARY KEY(key)
) TTL INTERVAL interval_specvar> ON timestamp_column_name;

מחליפים את מה שכתוב בשדות הבאים:

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

  • ‫interval_spec הוא מספר הימים שעברו מאז חותמת הזמן ב-timestamp_column_name שבה השורה מסומנת למחיקה. הערך צריך להיות מספר שלם לא שלילי, והוא צריך להיות שווה למספר שלם של ימים. לדוגמה, '3 days' מותר, אבל '3 days - 2 minutes' מחזיר שגיאה.

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

כדי להוסיף מדיניות לטבלה קיימת:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

ALTER TABLE albums
ADD TTL INTERVAL '5 days' ON timestampcolumn;

הגבלות

יש הגבלות על כללי מדיניות למחיקת שורות.

‫TTL בטבלאות שמקושרות באמצעות מפתח זר

אי אפשר ליצור מדיניות בנושא מחיקת נתונים של שורות:

  • בטבלה שהופנתה על ידי מפתח זר שלא כולל את אילוץ ON DELETE CASCADE.
  • בטבלת ההורה של טבלה שההפניה אליה מתבצעת באמצעות מפתח זר שלא כולל את פעולת ההפניה ON DELETE CASCADE.

בדוגמה הבאה, אי אפשר להוסיף המדיניות בנושא מחיקת נתונים לטבלה Customers, כי יש הפניה אליה באמצעות מפתח זר בטבלה Orders, שאין לה את האילוץ ON DELETE CASCADE. יכול להיות שמחיקת לקוחות תגרום להפרה של אילוץ המפתח הזר הזה. בנוסף, אי אפשר להוסיף המדיניות בנושא מחיקת נתונים לטבלה Districts. מחיקת שורה מהטבלה Districts עלולה לגרום למחיקות מדורגות בטבלת הצאצא Customers, מה שעלול להוביל להפרה של אילוץ המפתח הזר בטבלה Orders.

GoogleSQL 

CREATE TABLE Districts (
  DistrictID INT64
) PRIMARY KEY (DistrictID);

CREATE TABLE Customers (
  DistrictID INT64,
  CustomerID INT64,
  CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID, CustomerID),
INTERLEAVE IN PARENT Districts ON DELETE CASCADE;

CREATE TABLE Orders (
  OrderID INT64,
  DistrictID INT64,
  CustomerID INT64,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (DistrictID, CustomerID) REFERENCES Customers (DistrictID, CustomerID)
) PRIMARY KEY (OrderID);

PostgreSQL 

CREATE TABLE districts (
  districtid   bigint NOT NULL,
  PRIMARY KEY(districtid)
);

CREATE TABLE customers (
  districtid   bigint NOT NULL,
  customerid   bigint NOT NULL,
  createdat  timestamptz,
  PRIMARY KEY(districtid, customerid)
) INTERLEAVE IN PARENT districts ON DELETE CASCADE;

CREATE TABLE orders (
  orderid bigint NOT NULL,
  districtid   bigint,
  customerid bigint,
  PRIMARY KEY(orderid),
  CONSTRAINT fk_customerorder FOREIGN KEY (districtid, customerid) REFERENCES customers (districtid, customerid)
);

אפשר ליצור מדיניות למחיקת שורות בטבלה שמפנה לאילוץ של מפתח זר שמשתמש ב-ON DELETE CASCADE. בדוגמה הבאה אפשר ליצור המדיניות בנושא מחיקת נתונים בטבלה Customers, שאליה מתבצעת הפניה על ידי אילוץ המפתח הזר CustomerOrder, שמוגדר בטבלה Orders. כש-TTL מוחק שורות ב-Customers, המחיקה מתבצעת גם בשורות תואמות שנמצאות בטבלה Orders.

GoogleSQL 

 CREATE TABLE Districts (
  DistrictID INT64,
  CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID),
ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 1 DAY));

CREATE TABLE Customers (
  DistrictID INT64,
  CustomerID INT64,
  CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID, CustomerID),
INTERLEAVE IN PARENT Districts ON DELETE CASCADE,
ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 1 DAY));

CREATE TABLE Orders (
  OrderID INT64,
  DistrictID INT64,
  CustomerID INT64,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (DistrictID, CustomerID) REFERENCES Customers (DistrictID, CustomerID) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL 

CREATE TABLE districts (
  districtid   bigint NOT NULL,
  createdat  timestamptz,
  PRIMARY KEY(districtid)
) TTL INTERVAL '1 day' ON createdat;

CREATE TABLE customers (
  districtid   bigint NOT NULL,
  customerid   bigint NOT NULL,
  createdat  timestamptz,
  PRIMARY KEY(districtid, customerid)
) INTERLEAVE IN PARENT districts ON DELETE CASCADE
TTL INTERVAL '1 day' ON createdat;

CREATE TABLE orders (
  orderid bigint NOT NULL,
  districtid bigint,
  customerid bigint,
  PRIMARY KEY(orderid),
  CONSTRAINT fk_customerorder FOREIGN KEY (districtid, customerid) REFERENCES customers (districtid, customerid) ON DELETE CASCADE
);

באופן דומה, אפשר ליצור מדיניות למחיקת שורות בטבלת הורה שאליה מתבצעת הפניה באמצעות ON DELETE CASCADE אילוץ של מפתח זר.

TTL בעמודות עם ערכי ברירת מחדל

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

בדוגמה הבאה, ערך ברירת המחדל של העמודה CreatedAt בטבלה Customers הוא חותמת הזמן שבה השורה נוצרה.

GoogleSQL 

CREATE TABLE Customers (
  CustomerID INT64,
  CreatedAt TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (CustomerID);

מידע נוסף זמין במאמר בנושא DEFAULT (expression).

PostgreSQL 

CREATE TABLE customers (
  customerid bigint NOT NULL,
  createdat timestamptz DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(customerid)
  );

מידע נוסף מופיע במאמר בנושא CREATE TABLE.

ערך ה-TTL בעמודות שנוצרו

בכללי מדיניות למחיקת שורות אפשר להשתמש בעמודות שנוצרו כדי להגדיר כללים מתוחכמים יותר. לדוגמה, אפשר להגדיר מדיניות למחיקת שורות בחותמת הזמן greatest (GoogleSQL או PostgreSQL) של כמה עמודות, או למפות ערך אחר לחותמת זמן.

GoogleSQL

בטבלה הבאה שנקראת Orders מוצגים נתוני הזמנות מכירה. הבעלים של הטבלה רוצה להגדיר המדיניות בנושא מחיקת נתונים של שורות, שלפיה הזמנות שבוטלו יימחקו אחרי 30 יום, והזמנות שלא בוטלו יימחקו אחרי 180 יום.

ב-Spanner TTL אפשר להגדיר רק מדיניות אחת למחיקת שורות לכל טבלה. כדי להציג את שני הקריטריונים בעמודה אחת, אפשר להשתמש בעמודה שנוצרה עם הצהרת IF:

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  OrderStatus STRING(30) NOT NULL,
  LastModifiedDate TIMESTAMP NOT NULL,
  ExpiredDate TIMESTAMP AS (IF(OrderStatus = 'Cancelled',
    TIMESTAMP_ADD(LastModifiedDate, INTERVAL 30 DAY),
    TIMESTAMP_ADD(LastModifiedDate, INTERVAL 180 DAY))) STORED,
) PRIMARY KEY(OrderId),
ROW DELETION POLICY (OLDER_THAN(ExpiredDate, INTERVAL 0 DAY));

ההצהרה יוצרת עמודה בשם ExpiredDate שמוסיפה 30 ימים או 180 ימים ל-LastModifiedDate בהתאם לסטטוס ההזמנה. לאחר מכן, היא מגדירה את המדיניות בנושא מחיקת נתונים כך שתוקף השורות יפוג ביום שמאוחסן בעמודה ExpiredDate על ידי ציון INTERVAL 0 day.

PostgreSQL

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

ב-Spanner TTL אפשר להגדיר רק מדיניות אחת למחיקת שורות לכל טבלה. כדי להציג את שני הקריטריונים בעמודה אחת, אפשר ליצור עמודה שנוצרה:

CREATE TABLE orders (
    orderid bigint NOT NULL,
    orderstatus varchar(30) NOT NULL,
    createdate timestamptz NOT NULL,
    lastmodifieddate timestamptz,
    expireddate timestamptz GENERATED ALWAYS AS (GREATEST(createdate, lastmodifieddate)) STORED,
    PRIMARY KEY(orderid)
) TTL INTERVAL '30 days' ON expireddate;

ההצהרה יוצרת עמודה שנוצרת אוטומטית בשם ExpiredDate שבודקת את התאריך האחרון מבין שני התאריכים (LastModifiedDate או CreateDate). לאחר מכן, היא מגדירה את המדיניות בנושא מחיקת נתונים כך שהתוקף של השורות יפוג 30 ימים אחרי שההזמנה נוצרה, או שאם ההזמנה שונתה במהלך 30 הימים האלה, המחיקה תידחה ב-30 ימים נוספים.

‫TTL וטבלאות משולבות

טבלאות משולבות הן אופטימיזציה של הביצועים שמשייכת שורות קשורות בטבלת צאצא מסוג 'אחד לרבים' לשורה בטבלת אב. כדי להוסיף המדיניות בנושא מחיקת נתונים בטבלת אב, כל טבלאות הבנות שמשולבות בה חייבות לציין ON DELETE CASCADE, כלומר שורות הבנות נמחקות באופן אטומי עם שורת האב. כך מובטח שלמות נתונים, כך שמחיקות בטבלת האב יגרמו גם למחיקה של השורות הקשורות בטבלת הבן באותה טרנזקציה. ‫Spanner TTL לא תומך ב-ON DELETE NO ACTION.

גודל עסקה מקסימלי

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

פעולות שנכשלו מדווחות במדדי TTL.

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

כדאי לצרף המדיניות בנושא מחיקת נתונים לטבלאות צאצא כשהתנאים הבאים מתקיימים:

  • בטבלת הצאצא יש אינדקסים גלובליים שמשויכים אליה.
  • אתם מצפים למספר גדול (>100) של שורות צאצא לכל שורת אב.

מחיקה של המדיניות בנושא מחיקת נתונים של שורות

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

GoogleSQL 

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL 

ALTER TABLE mytable
DROP TTL;

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

מחיקה של עמודה שהמדיניות בנושא מחיקת נתונים למחיקת שורות מתייחסת אליה

ב-Spanner אי אפשר למחוק עמודה שמופנית אליה מדיניות למחיקת שורות. כדי למחוק את העמודה, קודם צריך למחוק את מדיניות מחיקת השורות.

הצגת המדיניות בנושא מחיקת נתונים של טבלה

אפשר לראות את מדיניות מחיקת השורות בטבלאות Spanner.

GoogleSQL 

SELECT TABLE_NAME, ROW_DELETION_POLICY_EXPRESSION
FROM INFORMATION_SCHEMA.TABLES
WHERE ROW_DELETION_POLICY_EXPRESSION IS NOT NULL;

מידע נוסף זמין במאמר בנושא סכימת מידע למסדי נתונים של ניב GoogleSQL.

PostgreSQL 

SELECT table_name, row_deletion_policy_expression
FROM information_schema.tables
WHERE row_deletion_policy_expression is not null;

מידע נוסף זמין במאמר בנושא סכימת מידע למסדי נתונים של ניב PostgreSQL.

שינוי המדיניות בנושא מחיקת נתונים של שורות

אפשר לשנות את העמודה או את הביטוי של המרווח של מדיניות קיימת למחיקת שורות. בדוגמה הבאה, העמודה משתנה מ-CreatedAt ל-ModifiedAt והמרווח מתרחב מ-1 DAY ל-7 DAY. הפונקציה מחזירה שגיאה אם אין המדיניות בנושא מחיקת נתונים קיימת בטבלה.

GoogleSQL 

ALTER TABLE MyTable
REPLACE ROW DELETION POLICY (OLDER_THAN(ModifiedAt, INTERVAL 7 DAY));

PostgreSQL 

ALTER TABLE mytable
ALTER TTL INTERVAL '7 days' ON timestampcolumn;