主页 / dba / 问题 / 287316
Accepted
cis
Asked: 2021-03-20 01:18:32 +0800 CST

在每个 INSERT 上运行 DELETE 以强制行限制

  • 772

使用 PostgreSQL 11。考虑一个像这样的表

CREATE TABLE "logs" 
    (
      "id" INTEGER NOT NULL,
      "userId" INTEGER NOT NULL, 
      "timestamp" TIMESTAMP NOT NULL,
      CONSTRAINT "PK_8d33b9f1a33b412e4865d1e5465" PRIMARY KEY ("id")
     )

现在，要求是每个userId. 如果有更多数据进入，则必须删除最旧的日志。如果在短时间内存储了 101 行，那么这并不是世界末日。如果多余的行在几秒钟的延迟后被删除，那很好。

我无法创建数据库TRIGGER。所以，我需要编写一个在应用层的日志创建事件上触发的查询。

纯 SQL 优于 plpgsql。

这是我想出的解决方案：

WITH "userLogs" AS (SELECT id, timestamp FROM "logs"
                    WHERE "userId" = $1
                ),
"countLogs" AS (SELECT count(id) FROM "userLogs")
        
DELETE FROM "logs" WHERE id = ANY
                (
                    SELECT id FROM "userLogs" 
                    ORDER BY "timestamp" ASC 
                    LIMIT GREATEST( (SELECT count FROM "countLogs") - 100, 0)
                );

想法是：始终运行 a并根据子查询DELETE实际是否必须删除某些内容来做出决定。LIMIT如果有超过 100 条日志，子查询将返回最旧的 id 以丢弃。否则，LIMIT将为 0，子查询不会返回任何内容，也不会删除任何内容。

我现在的问题是：

  1. DELETE对每个运行查询是否敏感INSERT- 即使它没有删除任何内容？
  2. 这里有任何性能影响吗？（或者其他我可能不知道的陷阱？）
  3. 我不太确定我是否需要一个LOCK. 在我的测试中，当并行运行 s 时，我无法产生任何意外行为INSERT，但是是否存在我需要 a 的边缘情况LOCK？

编辑：很难预测INSERT将针对该表运行多少次。如果一切顺利（业务方面），总共可能每天几千次 - 每个用户每天几十次。

编辑2：timestamp每个用户的值不一定是唯一的：可以有多个具有相同timestamp和相同的日志条目userId。预计该表将获得更多包含实际发生情况的列。

postgresql delete

1 个回答

  1. Best Answer

    如果您在 user_id 上有索引，则可以删除它并用 (user_id,timestamp) 上的索引替换它。这也将在显示最新的日志条目时保存排序（WHERE user_id=... ORDER BY timestamp DESC LIMIT n）。

    然后：

    SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100

    如果有超过 100 行，这将返回第 100 行的时间戳。否则它不会返回任何东西。要删除一位用户的旧日志：

    DELETE FROM logs WHERE userid=1 AND timestamp <=
(SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100);

    这是一个非常快速的查询。如果选择没有找到任何要删除的行，它将远低于 1 毫秒。

    要删除所有旧日志：

    DELETE FROM logs
USING (SELECT userid, f.timestamp FROM users CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC LIMIT 1 OFFSET 100) f) oldlogs
WHERE logs.userid=oldlogs.userid AND logs.timestamp<=oldlogs.timestamp;

    这可能会 seq-scan 日志，所以它可能很慢。这是一个更好的方法，它将利用 (userid,timestamp) 上的索引，并且在无事可做时速度很快：

    DELETE FROM logs USING
( SELECT userid,timestamp FROM users 
  CROSS JOIN LATERAL (SELECT timestamp FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC OFFSET 100) oldlogs ) o
WHERE logs.userid=o.userid AND logs.timestamp=o.timestamp;

    要回答您的评论“如果许多日志都具有相同的时间戳怎么办？”......这永远不会发生，因为如果您希望您的日志有用，它们应该由一些独特的东西排序，否则您不知道在什么为了他们被记录。但是......您可以简单地使用主键：

    -- one user
DELETE FROM logs USING
( SELECT id FROM logs WHERE logs.userid=123 ORDER BY timestamp DESC, id DESC OFFSET 100 ) o
WHERE logs.id=o.id;

-- all users
DELETE FROM logs USING
( SELECT oldlogs.id FROM users 
  CROSS JOIN LATERAL (SELECT id FROM logs WHERE logs.userid=users.userid ORDER BY timestamp DESC, id DESC OFFSET 100) oldlogs ) o
WHERE logs.id=o.id;

    因此，如果它们具有相同的时间戳，则 ORDER BY 将保留应该最后插入的最高 id。

    • 1

相关问题