版本表数据清理

Question

CREATE TABLE audit_table (
    id varchar(30) NOT NULL,
    auditversion int4 NOT NULL,
    value jsonb NOT NULL,
    delta text[] NULL,
    src text NOT NULL,
    usr text NOT NULL,
    meta hstore NULL,
    last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT audit_table PRIMARY KEY (id,auditversion)
);

每个唯一的id都会自动递增auditversion列。对于同一个id，版本会增加。对于新的Id，它从1开始。

我计划写一个清理查询。要求是这样的

1. 保存最新的30个版本，如果记录是在2020年-08-01之后更新的，则
2. 不会删除该记录。因此，在2020-08-01之后，我们有可能超过30个版本。

到目前为止，这就是我所拥有的：

delete from audit_table pa
using (select id,  MAX(a.auditversion) as maxVersion 
       FROM audit_table a group by id) pm 
where pm.id = pa.id
  and pa.auditversion <= (pm.maxVersion-30)
  and pa.last_updated < '2020-08-01';

如果可以用更好的方式写，请告诉我。

Answer 1

你的问题在我看来不错。

您可以考虑的另一种方法是添加一个触发器ON INSERT OR UPDATE，在每次数据修改时清除NEW.id的旧条目。这可能是更全面的努力，但避免了一个长时间运行的DELETE语句。