在 SQL 裡面避免大量刪除資料的方式

看到 Percona 的「An Overview of Sharding in PostgreSQL and How it Relates to MongoDB’s」這篇,雖然是在講 PostgreSQL 上的 sharding (以及 partition),突然想到好像沒寫過要怎麼避免大量刪除資料的操作...

一個常見的情境是,想要讓某個表格只保留這一個月的資料,所以每個月開頭都會跑一隻 cron job 負責刪掉上個月的資料,像是 DELETE FROM xxx WHERE timestamp < yyy; 這樣的指令。

這個方式無論是在 PostgreSQL 或是 MySQL 都需要很多時間與 I/O 資源,而透過 partition 將不同時間區段切開到不同的表格,再用 TRUNCATE 直接清空表格剛好可以解這樣的問題。

Percona 的文章裡說了一些 PostgreSQL 的歷史與目前的進展。

在 PostgreSQL 9 或更早以前的版本,一個常見的作法是透過 table inheritance 實做 partition,然後用再用 function 實做 INSERT

CREATE TABLE temperature (
  id BIGSERIAL PRIMARY KEY NOT NULL,
  city_id INT NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  temp DECIMAL(5,2) NOT NULL
);

CREATE TABLE temperature_201901 (CHECK (timestamp >= DATE '2019-01-01' AND timestamp <= DATE '2019-01-31')) INHERITS (temperature);
CREATE TABLE temperature_201902 (CHECK (timestamp >= DATE '2019-02-01' AND timestamp <= DATE '2019-02-28')) INHERITS (temperature);
CREATE TABLE temperature_201903 (CHECK (timestamp >= DATE '2019-03-01' AND timestamp <= DATE '2019-03-31')) INHERITS (temperature);

CREATE OR REPLACE FUNCTION temperature_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.timestamp >= DATE '2019-01-01' AND NEW.timestamp <= DATE '2019-01-31' ) THEN INSERT INTO temperature_201901 VALUES (NEW.*);
    ELSIF ( NEW.timestamp >= DATE '2019-02-01' AND NEW.timestamp <= DATE '2019-02-28' ) THEN INSERT INTO temperature_201902 VALUES (NEW.*);
    ELSIF ( NEW.timestamp >= DATE '2019-03-01' AND NEW.timestamp <= DATE '2019-03-31' ) THEN INSERT INTO temperature_201903 VALUES (NEW.*);
    ELSE RAISE EXCEPTION 'Date out of range!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

在 PostgreSQL 10 之後,就直接支援一些與 partition 相關的設計,像是這樣:

CREATE TABLE temperature (
  id BIGSERIAL NOT NULL,
  city_id INT NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  temp DECIMAL(5,2) NOT NULL
) PARTITION BY RANGE (timestamp);

CREATE TABLE temperature_201901 PARTITION OF temperature FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
CREATE TABLE temperature_201902 PARTITION OF temperature FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');
CREATE TABLE temperature_201903 PARTITION OF temperature FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');

雖然還是有些限制,但可以看出比起以前簡單不少。

而有了 partition 後,文章的後續就在討論這跟 MongoDB 的 sharding 有什麼關係,但這就不是我關注的事情了...