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

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

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

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

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

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

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

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

אם זו הפעם הראשונה שאתם משתמשים ב-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 (ביטוי).

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;