postgresql - 清理最旧的最大行

Question

我有一个 postgresql 表，我正在其中存储“ session ”记录。这些 session 有时创建得非常快(有时高达每秒几百个)。我显然不能让 table 无限增长。所以我有一个守护进程定期清扫表，对大小施加外部可配置的限制。

当记录数超过限制时，我想拉出最旧的记录(使用限制值)，将它们以文本形式写入一个单独的(非关系型)日志文件，然后从表中删除。我目前正在做的事情是有效的，但在高限制下，它非常慢。我当前的查询是这样的:

SELECT * FROM sessions ORDER BY modified DESC OFFSET 1000000 LIMIT 10000;

然后我的程序将那些最近最少修改的记录记录到离线历史记录中，然后执行单独的 SQL DELETE 删除行。问题是，如果我说有 1000010 条记录，执行上面的查询大约需要 25-30 秒才能获得这 10 条记录。在 modified 字段上创建索引似乎没有任何明显的效果，所以我假设 postgres 在计算出前 100 万条记录然后排除它们之前在某处创建了一个临时数据集。

是否有更好的方法来实现相同的结果？我基本上只想获得表中最旧的“count(*) - N”行。

(我现在意识到我可以先做一个单独的查询来获取表的 count(*)，然后反转查询的 ORDER BY 子句，然后只使用 LIMIT 但这看起来很笨拙。这将是我的后备方案，但如果可能的话，我更愿意直接执行上面所说的操作。)

Answer 1

我认为这里的问题是偏移量，因为你偏移量 1000000 你做了很多扫描，这使得索引不被使用。我不知道你的表大小是多少，但你为什么不使用修改日期对你的表进行分区。在这种情况下，您将确切地知道您需要访问和删除数据表的分区。