我正在使用 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
起作用吗?