我正在使用 Postgres 12,在我的应用程序中,我有一个表,用于存储特定事件,这些事件包含有关系统外部发生的事情的信息,并与我的数据库中的某些记录相关。该表如下所示:
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
eventable_type VARCHAR(255) NOT NULL,
eventable_id BIGINT NOT NULL,
type VARCHAR(255) NOT NULL,
data JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
);
CREATE INDEX index_events_on_eventable ON events (eventable_type, eventable_id);
例如:在 Google 日历中预订了一次会议。在此表中创建一个事件,其中包含发生的事情的详细信息,并且该记录与数据库中会议的内部表示相关联。该data
属性包含事件的详细信息,其中还包含一个唯一 ID,例如:
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "created", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "updated", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "deleted", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "created", "GoogleId": "dsfsdf2343"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "updated", "GoogleId": "dsfsdf2343"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "deleted", "GoogleId": "dsfsdf2343"}'::jsonb);
我查询事件表如下:
SELECT * FROM events WHERE events.type = 'GoogleCalendarEvent' AND (data->>'GoogleId' = 'abcdef1234') LIMIT 1;
从操作基数来看,写入次数大约是读取次数的 3 倍。也就是说,写入次数多于读取次数。该表有大约 300 万行数据,增长迅速。每天大约有 30 万行数据添加到表中。
目前,我们仅type
在表中存储另一个事件,我们将其称为GoogleEmailEvent
。按过滤GoogleCalendarEvent
将返回表中大约 50% 的记录。按过滤GoogleId
通常会返回少于 10 条记录,但我们实际上只需要 1 条,因为它们都与同一个“Eventable”相关联,如您在示例插入中看到的那样。
我想提高查询的执行时间,我想过:
- 添加索引
WHERE data->>'GoogleId' IS NOT NULL
。但我担心这会减慢写入速度 data->>'GoogleId'
与事件 ID 一起存储在单独的表中,以便快速检索。这样做有多有效?这也会在一定程度上减慢写入速度。- 建立索引
created_at
并在查询中使用它来以某种方式缩小查询中的记录范围
重要细节:绝大多数情况下(99% 或更多),匹配事件是最近插入表中的事件(例如,10 分钟内)。我可以利用这些详细信息来加快查询速度吗?添加会ORDER BY Id DESC LIMIT 1
起作用吗?
基础知识
您可以像已经思考过的那样在表达式上添加部分索引:
询问:
但这还不是很有用。当条件仅删除一半的行时,部分索引没有多大意义。它可以通过多种方式进行改进。
优化步骤1
您的表正在快速增长,添加的索引也是如此。您的查询大多只需要最近的条目。添加截止时间戳以大幅减少大小:
向您的查询添加相同的截止时间(或更晚的时间戳),以便 Postgres 知道该索引是适用的:
我从今天(UTC 时间)开始。索引将不断增长。您必须不时重新创建它以使其保持较小。就像使用每日 cron 作业一样。我添加了
CONCURRENTLY
以便不阻止写入。仍然不理想。Postgres 无法使用此表达式进行仅索引扫描,并且
jsonb
每次都必须检查(可能很大?)列。此外,该表达式也使写入索引的成本更高一些。优化步骤2
Google ID 似乎始终存在(或大多数时间存在)。专用列会更好。实际上,如果您的 JSON 文档是常规的,那么存储所有纯列而不是一开始就存储 JSON 文档会更有效率。存储更少、访问更快等等。在检索时将密钥添加回 JSON 文档非常简单且快速 - 或者从纯 Postgres 列生成整个 JSON 文档。
仅提取 Google ID 进行演示:
“技巧
ALTER TABLE
”是重写整个表的最快方法,但它会阻止并发写入。(我真的会通过额外的优化重新创建整个表。)现在,索引可以是:
假设您只需要
eventable_id
,我添加了一个INCLUDE
子句,使其成为覆盖索引。现在,如果表被足够清理,您将获得仅索引扫描:小提琴
一遍又一遍地将冗长的字符串“GoogleCalendarEvent”/“GoogleEmailEvent”存储为类型是一种浪费。我会用更有效的东西来代替它。等等。
数据类型和表格布局也可能会得到进一步优化。参见: