PolarDB:Overview

Background information

The TTL feature, prevalent in NoSQL databases and log-structured merge-tree (LSM tree)-based storage engines, implements key and row-level lifecycle management policies. The TTL feature can efficiently save storage space in specific scenarios. In key-value stores, background threads periodically scan for expired keys and delete them. In databases based on LSM trees, compaction threads periodically clean up expired data rows.

Examples

Traditional relational databases such as MySQL, Oracle, and PostgreSQL do not provide native support for the TTL feature. Similar functionality is achieved by using triggers or stored procedures.

-- 1. Create a table that has a column to indicate the time information of each row.
CREATE TABLE messages(
    message_id   VARCHAR(36) PRIMARY KEY,
    sender_id    VARCHAR(36),
    receiver_id  VARCHAR(36),
    message_text TEXT,
    sent_at      TIMESTAMP
);

-- 2. Create a scheduled task that runs every 5 minutes to delete expired rows based on the sent_at field.
SET GLOBAL event_scheduler = ON;

CREATE EVENT delete_old_messages
ON SCHEDULE
  EVERY 5 MINUTE
STARTS CURRENT_TIMESTAMP
DO
DELETE FROM messages
WHERE sent_at < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

However, this method may result in the following issues:

• If expired rows are not deleted at the earliest opportunity, the expired rows may still be queried.

• If a large amount of data needs to be deleted, the database performance may fluctuate.

Therefore, the database system must natively support the TTL feature for more precise and efficient TTL management.

Supported versions

You can use the TTL feature in clusters that run the following engine versions:

• PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.49 or later.

• PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.27 or later.

For information about how to view the database engine version of a cluster, see Query the engine version.