介绍
我有一个 PostgreSQL 表设置作为队列/事件源。
我非常想保留事件的“顺序”(即使在处理队列项之后)作为 e2e 测试的来源。
我开始遇到查询性能下降的问题(可能是因为表膨胀),而且我不知道如何根据不断变化的键有效地查询表。
初始设置
Postgres: v15
表 DDL
CREATE TABLE eventsource.events (
id serial4 NOT NULL,
message jsonb NOT NULL,
status varchar(50) NOT NULL,
createdOn timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT events_pkey PRIMARY KEY (id)
);
CREATE INDEX ON eventsource.events (createdOn)
抓取查询(伪代码)
BEGIN; -- Start transaction
SELECT message, status
FROM eventsource.events ee
WHERE status = 'PENDING'
ORDER BY ee.createdOn ASC
FOR UPDATE SKIP LOCKED
LIMIT 10; -- Get the OLDEST 10 events that are pending
-- I found that having a batch of work items was more performant than taking 1 at a time.
...
-- The application then uses the entries as tickets for doing work as in "I am working on these 10 items, no one else touch"
...
UPDATE ONLY eventsource.events SET status = 'DONE' WHERE id = $id_1
UPDATE ONLY eventsource.events SET status = 'DONE' WHERE id = $id_2
UPDATE ONLY eventsource.events SET status = 'FAIL' WHERE id = $id_3
UPDATE ONLY eventsource.events SET status = 'DONE' WHERE id = $id_n
...
END; -- finish transaction
粗工大纲
多个工作人员批量处理工作项目形成队列,然后对它们进行操作并报告它们的状态。我希望重叠尽可能少。
评估
查看执行计划时,查询似乎必须遍历整个表才能获取处于“待处理”状态的记录。
我认为这可能是因为一ORDER BY ee.createdOn ASC
开始。但在查看执行计划后,我发现查询正在遍历整个表以搜索status
,然后才对它进行排序。
试图
我看到部分索引,希望它可以减少查询的搜索空间。
CREATE INDEX ON eventsource.events (status)
WHERE status = 'PENDING'
但我认为我让事情变得更糟......
正在插入状态为“待处理”的记录,然后随着应用程序正在使用队列几乎立即更改为“完成”(或“失败”)。我认为这可能每次都会破坏索引,然后在更新字段后从头开始重新创建它status
(可能非常昂贵)。
问题
更新部分索引的键/谓词(如果重要)有什么影响我如何有效地过滤一个不断变化的键的大表?
指数法
我的索引方法合理吗?
我的第一个想法是索引,但也许分区更适合这里?
如果分区键被更改会发生什么?
它是否与破坏索引一样具有破坏性?
索引类型
我知道默认索引类型是 B-Tree,在这种情况下 HASH 索引(或其他)会更好吗?
在幕后,更改 HASH 索引的索引键是否会导致破坏/重新创建索引表,就像它对 B-Tree 所做的那样?
索引创建
我不确定部分索引的键与谓词的效果是什么。索引之间的有效区别是什么:
CREATE INDEX ON eventsource.events (status)
WHERE status = 'PENDING'
和
CREATE INDEX ON eventsource.events (createdOn)
WHERE status = 'PENDING'
我在这里使用是createdOn
因为它在我的抓取查询中,但我认为id
也可以。
将索引键移动到不同的字段会影响索引的创建/重新创建吗?在这种情况下,我将它从
status
字段(将更改)移动到字段createdOn
,而该字段不会。我不太明白这个SO意味着什么。
对于这种类型的部分索引,我对Postgres文档有点不清楚。
不要使用
timestamp
(without time zone
)您的整个设置很容易失败:
CURRENT_TIMESTAMP
(又名now()
)返回timestamptz
,而不是timestamp
。如果偶然、意外或恶意的任何会话曾经设置不同
timezone
,然后根据列默认值插入一行,您将得到不同的(错误的)本地时间,从而破坏排序顺序。你很难找出原因。不要这样做。特别是没有这样的列默认值。(LOCALTIMESTAMP
遇到同样的问题:也取决于当前timezone
设置。)有关的:
更好的表定义
尽可能使用合法的小写标识符。看:
使用
text
并添加CHECK
约束以强制执行合法状态。IDENTITY
serial
在现代 Postgres 中更可取。看:最重要的是,
timestamptz
按照顶部的说明使用。所有其他点仅仅是建议。更好的指数
正如Charlieface所建议的那样,使用部分索引:
它对于您的用例来说要小得多,并且提供排序的行。小型索引的维护成本也更低。但是,会有很多流失,因此索引会很快膨胀。看:
autovacuum
考虑表的激进设置。喜欢:的全局默认
autovacuum_vacuum_scale_factor
值为0.2。意思是,在更改了autovacuum
20% 的表行 +(默认为 50)后触发。autovacuum_vacuum_threshold
如果桌子很大,那对于您的目的来说可能太懒了。在增加的维护成本和改进的查询性能之间找到平衡点。(created_on)
出于其他目的,您可能需要也可能不需要额外的完整索引。更好的方法
假设:
这种方法在并发写入负载下可靠地工作,并且从不阻塞。它每个会话只锁定一行,最大限度地减少并发症的可能性。它立即锁定并更新行,这比稍后锁定和更新更快。在极少数情况下,您需要进行第二次更新。但相比之下,这是便宜的。
如果您需要(或只是想)锁定和处理多行,则以类似的方式工作。看:
createdon
,所以对查询帮助不大。即使使用了索引,服务器仍然需要PENDING
按照 的顺序对所有行进行排序createdon
。分区在这里无关紧要。正确的索引就是你想要的。
INCLUDE
列。鉴于表格的宽度,放弃它并依赖位图扫描可能是值得的。您可能希望限制中的列SELECT
以避免这种情况。说了这么多,还不清楚你的应用程序做了什么“处理”,也不清楚整个事情是否可以在一个语句中完成
UPDATE
。