介绍
我有一个 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文档有点不清楚。