Mengelola retensi data dengan TTL

Halaman ini membahas cara menggunakan time to live (TTL) pada tabel Spanner di database dialek GoogleSQL dan database dialek PostgreSQL. Untuk mengetahui informasi selengkapnya, lihat Tentang TTL.

Sebelum memulai

Sebelum memulai, ikuti praktik terbaik berikut.

Mengaktifkan pencadangan dan pemulihan point-in-time

Sebelum menambahkan TTL ke tabel, sebaiknya aktifkan pencadangan dan pemulihan Spanner. Dengan begitu, Anda dapat memulihkan database sepenuhnya jika tidak sengaja menghapus data dengan kebijakan TTL.

Jika telah mengaktifkan pemulihan point-in-time, Anda dapat melihat dan memulihkan data yang dihapus—tanpa pemulihan penuh dari cadangan—jika masih dalam periode retensi versi yang dikonfigurasi. Untuk mengetahui informasi tentang cara membaca data di masa lalu, lihat Melakukan pembacaan data lama.

Membersihkan data lama

Jika ini adalah pertama kalinya Anda menggunakan TTL dan Anda memperkirakan bahwa proses pertama akan menghapus banyak baris, sebaiknya bersihkan data lama secara manual terlebih dahulu menggunakan DML berpartisi. Dengan begitu, Anda memiliki lebih banyak kontrol atas penggunaan resource, bukan menyerahkannya ke proses latar belakang TTL. TTL berjalan dengan prioritas rendah, ideal untuk pembersihan inkremental. Namun, hal ini kemungkinan akan memperpanjang waktu yang diperlukan untuk menghapus kumpulan baris awal dalam database yang sibuk karena penjadwal tugas internal Spanner memprioritaskan tugas lain, seperti kueri pengguna.

Memverifikasi kondisi Anda

Untuk tabel GoogleSQL, jika ingin memverifikasi data yang terpengaruh oleh kebijakan penghapusan baris sebelum mengaktifkan TTL, Anda dapat membuat kueri tabel menggunakan kondisi yang sama. Contoh:

GoogleSQL 

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

Izin yang diperlukan

Untuk mengubah skema database, Anda harus memiliki izin spanner.databases.updateDdl. Untuk mengetahui detailnya, lihat Kontrol akses untuk Spanner.

Membuat kebijakan penghapusan baris

GoogleSQL

Untuk membuat kebijakan penghapusan baris menggunakan GoogleSQL, Anda dapat menentukan klausa ROW DELETION POLICY saat membuat tabel baru, atau menambahkan kebijakan ke tabel yang sudah ada. Klausa ini berisi ekspresi kolom dan interval.

Untuk menambahkan kebijakan pada saat pembuatan tabel, lakukan hal berikut:

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

Ganti kode berikut:

  • timestamp_column harus berupa kolom yang ada dengan jenis TIMESTAMP. Kolom dengan stempel waktu commit bersifat valid, begitu juga kolom yang dihasilkan. Namun, Anda tidak dapat menentukan kolom yang dihasilkan yang mereferensikan kolom stempel waktu commit.

  • num_days adalah jumlah hari setelah stempel waktu di timestamp_column saat baris ditandai untuk dihapus. Nilai harus berupa bilangan bulat non-negatif dan DAY adalah satu-satunya unit yang didukung.

Untuk menambahkan kebijakan ke tabel yang sudah ada, gunakan pernyataan ALTER TABLE. Tabel dapat memiliki maksimal satu kebijakan penghapusan baris. Menambahkan kebijakan penghapusan baris ke tabel dengan kebijakan yang sudah ada akan gagal dengan error. Lihat TTL pada kolom yang dihasilkan untuk menentukan logika penghapusan baris yang lebih canggih.

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

PostgreSQL

Untuk membuat kebijakan penghapusan baris menggunakan PostgreSQL, Anda dapat menentukan klausa TTL INTERVAL saat membuat tabel baru, atau menambahkan kebijakan ke tabel yang sudah ada.

Untuk menambahkan kebijakan pada saat pembuatan tabel, lakukan hal berikut:

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

Ganti kode berikut:

  • timestamp_column_name harus berupa kolom dengan jenis data TIMESTAMPTZ. Anda harus membuat kolom ini dalam pernyataan CREATE TABLE. Kolom dengan stempel waktu commit bersifat valid, begitu juga kolom yang dihasilkan. Namun, Anda tidak dapat menentukan kolom yang dihasilkan yang mereferensikan kolom stempel waktu commit.

  • interval_spec adalah jumlah hari setelah stempel waktu di timestamp_column_name saat baris ditandai untuk dihapus. Nilai harus berupa bilangan bulat non-negatif dan harus dievaluasi ke bilangan bulat hari. Misalnya, '3 days' diizinkan, tetapi '3 days - 2 minutes' menampilkan error.

Untuk menambahkan kebijakan ke tabel yang sudah ada, gunakan pernyataan ALTER TABLE. Tabel dapat memiliki maksimal satu kebijakan TTL. Menambahkan kebijakan TTL ke tabel dengan kebijakan yang sudah ada akan gagal dengan error. Lihat TTL pada kolom yang dihasilkan untuk menentukan logika TTL yang lebih canggih.

Untuk menambahkan kebijakan ke tabel yang sudah ada, lakukan hal berikut:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Pembatasan

Kebijakan penghapusan baris memiliki batasan berikut.

TTL pada tabel yang direferensikan oleh kunci asing

Anda tidak dapat membuat kebijakan penghapusan baris:

  • Pada tabel yang direferensikan oleh kunci asing yang tidak menyertakan batasan ON DELETE CASCADE.
  • Pada induk tabel yang direferensikan oleh kunci asing yang tidak menyertakan tindakan referensial ON DELETE CASCADE.

Pada contoh berikut, Anda tidak dapat menambahkan kebijakan penghapusan baris ke tabel Customers, karena direferensikan oleh kunci asing di tabel Orders, yang tidak memiliki batasan ON DELETE CASCADE. Menghapus pelanggan dapat melanggar batasan kunci asing ini. Anda juga tidak dapat menambahkan kebijakan penghapusan baris ke tabel Districts. Menghapus baris dari Districts dapat menyebabkan penghapusan berjenjang di tabel turunan Customers, yang dapat melanggar batasan kunci asing pada tabel 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)
);

Anda dapat membuat kebijakan penghapusan baris pada tabel yang direferensikan oleh batasan kunci asing yang menggunakan ON DELETE CASCADE. Pada contoh berikut, Anda dapat membuat kebijakan penghapusan baris pada tabel Customers yang direferensikan oleh batasan kunci asing CustomerOrder, yang ditentukan pada tabel Orders. Saat TTL menghapus baris di Customers, penghapusan akan berjenjang ke baris yang cocok yang ada di tabel 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
);

Demikian pula, Anda dapat membuat kebijakan penghapusan baris pada induk tabel yang direferensikan oleh batasan kunci asing ON DELETE CASCADE.

TTL pada kolom dengan nilai default

Kebijakan penghapusan baris dapat menggunakan kolom stempel waktu dengan nilai default. Nilai default yang umum adalah CURRENT_TIMESTAMP. Jika tidak ada nilai yang ditetapkan secara eksplisit ke kolom, atau jika kolom ditetapkan ke nilai defaultnya oleh pernyataan INSERT atau UPDATE, nilai default akan digunakan dalam perhitungan aturan.

Pada contoh berikut, nilai default untuk kolom CreatedAt dalam tabel Customers adalah stempel waktu saat baris dibuat.

GoogleSQL 

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

Untuk mengetahui informasi selengkapnya, lihat DEFAULT (ekspresi).

PostgreSQL 

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

Untuk mengetahui informasi selengkapnya, lihat CREATE TABLE.

TTL pada kolom yang dihasilkan

Kebijakan penghapusan baris dapat menggunakan kolom yang dihasilkan untuk mengekspresikan aturan yang lebih canggih. Misalnya, Anda dapat menentukan kebijakan penghapusan baris pada stempel waktu greatest (GoogleSQL atau PostgreSQL) dari beberapa kolom, atau memetakan nilai lain ke stempel waktu.

GoogleSQL

Tabel berikut bernama Orders melacak pesanan penjualan. Pemilik tabel ingin menyiapkan kebijakan penghapusan baris yang menghapus pesanan yang dibatalkan setelah 30 hari, dan pesanan yang tidak dibatalkan setelah 180 hari.

TTL Spanner hanya mengizinkan satu kebijakan penghapusan baris per tabel. Untuk mengekspresikan dua kriteria dalam satu kolom, Anda dapat menggunakan kolom yang dihasilkan dengan pernyataan 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));

Pernyataan ini membuat kolom bernama ExpiredDate yang menambahkan 30 hari atau 180 hari ke LastModifiedDate, bergantung pada status pesanan. Kemudian, kebijakan penghapusan baris akan ditetapkan untuk mengakhiri masa berlaku baris pada hari yang disimpan di kolom ExpiredDate dengan menentukan INTERVAL 0 day.

PostgreSQL

Tabel berikut bernama Orders melacak pesanan penjualan. Pemilik tabel ingin menyiapkan kebijakan penghapusan baris yang menghapus baris setelah 30 hari tidak aktif.

TTL Spanner hanya mengizinkan satu kebijakan penghapusan baris per tabel. Untuk mengekspresikan dua kriteria dalam satu kolom, Anda dapat membuat kolom yang dihasilkan:

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;

Pernyataan ini membuat kolom yang dihasilkan bernama ExpiredDate yang mengevaluasi tanggal terbaru dari dua tanggal (LastModifiedDate atau CreateDate). Kemudian, kebijakan penghapusan baris akan ditetapkan untuk mengakhiri masa berlaku baris 30 hari setelah pesanan dibuat, atau jika pesanan diubah dalam waktu 30 hari tersebut, penghapusan akan diperpanjang selama 30 hari lagi.

TTL dan tabel sisipan

Tabel sisipan adalah pengoptimalan performa yang mengaitkan baris terkait dalam tabel turunan satu-ke-banyak dengan baris dalam tabel induk. Untuk menambahkan kebijakan penghapusan baris pada tabel induk, semua tabel turunan sisipan harus menentukan ON DELETE CASCADE, yang berarti baris turunan dihapus secara atomik dengan baris induk. Hal ini memastikan integritas referensial sehingga penghapusan pada tabel induk juga menghapus baris turunan terkait dalam transaksi yang sama. TTL Spanner tidak mendukung ON DELETE NO ACTION.

Ukuran transaksi maksimum

Spanner memiliki batas ukuran transaksi. Penghapusan berjenjang pada hierarki induk-turunan yang besar dengan kolom yang diindeks dapat melebihi batas ini dan menyebabkan satu atau beberapa operasi TTL gagal. Untuk operasi yang gagal, TTL akan mencoba lagi dengan batch yang lebih kecil, hingga satu baris induk. Namun, hierarki turunan yang besar untuk satu baris induk pun masih dapat melebihi batas mutasi.

Operasi yang gagal dilaporkan dalam metrik TTL.

Jika satu baris dan turunan sisipannya terlalu besar untuk dihapus, Anda dapat melampirkan kebijakan penghapusan baris langsung di tabel turunan, selain di tabel induk. Kebijakan di tabel turunan harus dikonfigurasi sehingga baris turunan dihapus sebelum baris induk.

Pertimbangkan untuk melampirkan kebijakan penghapusan baris ke tabel turunan jika dua pernyataan berikut berlaku:

  • Tabel turunan memiliki indeks global yang terkait dengannya; dan
  • Anda memperkirakan sejumlah besar (>100) baris turunan per baris induk.

Menghapus kebijakan penghapusan baris

Anda dapat menghapus kebijakan penghapusan baris yang ada dari tabel. Tindakan ini akan menampilkan error jika tidak ada kebijakan penghapusan baris yang ada di tabel.

GoogleSQL 

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL 

ALTER TABLE mytable
DROP TTL;

Menghapus kebijakan penghapusan baris akan segera menghentikan proses TTL yang berjalan di latar belakang. Baris yang sudah dihapus oleh proses yang sedang berlangsung akan tetap dihapus.

Menghapus kolom yang direferensikan oleh kebijakan penghapusan baris

Spanner tidak mengizinkan Anda menghapus kolom yang direferensikan oleh kebijakan penghapusan baris. Anda harus terlebih dahulu menghapus kebijakan penghapusan baris sebelum menghapus kolom.

Melihat kebijakan penghapusan baris tabel

Anda dapat melihat kebijakan penghapusan baris tabel Spanner.

GoogleSQL 

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

Untuk mengetahui informasi selengkapnya, lihat Skema informasi untuk database dialek GoogleSQL.

PostgreSQL 

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

Untuk mengetahui informasi selengkapnya, lihat Skema informasi untuk database dialek PostgreSQL.

Mengubah kebijakan penghapusan baris

Anda dapat mengubah kolom atau ekspresi interval dari kebijakan penghapusan baris yang ada. Contoh berikut mengalihkan kolom dari CreatedAt ke ModifiedAt dan memperpanjang interval dari 1 DAY menjadi 7 DAY. Tindakan ini akan menampilkan error jika tidak ada kebijakan penghapusan baris yang ada di tabel.

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;