Tag: 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 有什麼關係，但這就不是我關注的事情了...