在 SQL 裡面避免大量刪除資料的方式
栏目: 数据库 · PostgreSQL · 发布时间: 5年前
内容简介:看到一個常見的情境是,想要讓某個表格只保留這一個月的資料,所以每個月開頭都會跑一隻 cron job 負責刪掉上個月的資料,像是這個方式無論是在 PostgreSQL 或是
看到 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 有什麼關係,但這就不是我關注的事情了...
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。