[PERF] Chat-scoped inbox claim scans a chat's entire acked trigger history

[bestony]

Summary

The chat-scoped branch of pollInboxInner claims triggers with ORDER BY id ASC LIMIT n, but no index carries both the status = 'pending' predicate and an id-ordered key. Postgres therefore walks every already-acked trigger in the chat before reaching the live ones, so the claim cost grows with a chat's entire consumed history rather than with its live backlog.

Location

packages/server/src/services/inbox.ts — pollInboxInner, the chatId !== undefined branch (around packages/server/src/services/inbox.ts#L144-L155 at f644ac645).

SELECT id FROM inbox_entries
 WHERE inbox_id = $1 AND chat_id = $2 AND status = 'pending' AND notify = true
 ORDER BY id ASC LIMIT $3
 FOR UPDATE SKIP LOCKED

Why no current index covers it

Index	Why it does not resolve the scan
idx_inbox_chat_notify (inbox_id, chat_id, id) WHERE notify = true	Supplies the equality columns and the id ordering, but its predicate omits status, so status = 'pending' stays a filter
idx_inbox_pending_notify (inbox_id, created_at) WHERE status = 'pending' AND notify = true	Correct predicate, but keyed on created_at, so it cannot supply ORDER BY id
idx_inbox_chat_silent_pending (inbox_id, chat_id, id) WHERE status = 'pending' AND notify = false	Opposite notify branch
idx_inbox_pending, idx_inbox_entries_message_status, uq_inbox_delivery	Wrong leading columns for this predicate

The planner picks idx_inbox_chat_notify and discards every acked row it walks. Because acked rows are older, and therefore have lower ids, ORDER BY id ASC reaches them first: the scan length tracks the chat's consumed history, which only grows.

Widening idx_inbox_chat_notify with status = 'pending' is not an option. That index also serves the preceding-context cursor and ACK-through's contiguous notify prefix check, and both must see notify rows in any status — an already-acked trigger still closes a preceding-context window, and the prefix check has to walk acked rows to prove there is no gap.

Measurements

PostgreSQL 17. One chat holding 20,000 acked triggers and 3 pending ones, with the full current index set present. Three warm runs each.

Index set	Plan	Rows discarded	Execution time
current	Index Scan using idx_inbox_chat_notify	20,000	1.24 / 1.27 / 1.39 ms
current + (inbox_id, chat_id, id) WHERE status = 'pending' AND notify = true	Index Scan using idx_inbox_chat_pending_notify	0	0.027 / 0.030 / 0.046 ms

The candidate index is 224 kB against idx_inbox_chat_notify's 840 kB on the same data, because it only covers the live backlog.

Suggested direction

Add a partial index carrying the pending-notify predicate with id as the trailing key, so the filter and the ordering resolve together and the index stays proportional to the live backlog:

CREATE INDEX idx_inbox_chat_pending_notify
  ON inbox_entries (inbox_id, chat_id, id)
  WHERE status = 'pending' AND notify = true;

Two things are worth measuring before settling on a shape:

• whether chat_id belongs in the key, or whether an (inbox_id, id) variant should replace idx_inbox_pending_notify so the unscoped poll and the chat-scoped poll share one index;
• whether idx_inbox_pending_notify still earns its place afterwards.

inbox_entries sits in the message delivery hot path, so any index change here should follow the CREATE INDEX CONCURRENTLY operator-runbook pattern used by 0025_inbox_silent_entries.sql and later migrations rather than a plain CREATE INDEX.

Reproduction

CREATE TABLE inbox_entries (
  id bigserial PRIMARY KEY, inbox_id text NOT NULL, chat_id text,
  message_id text NOT NULL, status text NOT NULL, notify boolean NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now());

INSERT INTO inbox_entries (inbox_id, chat_id, message_id, status, notify, created_at)
SELECT 'ib','c1','m'||g, CASE WHEN g > 20000 THEN 'pending' ELSE 'acked' END, true,
       now() - make_interval(secs => (20003 - g))
FROM generate_series(1, 20003) g;
INSERT INTO inbox_entries (inbox_id, chat_id, message_id, status, notify, created_at)
SELECT 'ib','c1','s'||g,'pending',false,now() FROM generate_series(1,500) g;
INSERT INTO inbox_entries (inbox_id, chat_id, message_id, status, notify, created_at)
SELECT 'ib','c'||((g % 40) + 2),'o'||g,'pending',(g % 3 = 0),now() FROM generate_series(1,20000) g;

CREATE UNIQUE INDEX uq_inbox_delivery         ON inbox_entries (inbox_id, message_id, chat_id);
CREATE INDEX idx_inbox_pending                ON inbox_entries (inbox_id, created_at);
CREATE INDEX idx_inbox_pending_notify         ON inbox_entries (inbox_id, created_at) WHERE status = 'pending' AND notify = true;
CREATE INDEX idx_inbox_chat_silent_pending    ON inbox_entries (inbox_id, chat_id, id) WHERE status = 'pending' AND notify = false;
CREATE INDEX idx_inbox_chat_notify            ON inbox_entries (inbox_id, chat_id, id) WHERE notify = true;
CREATE INDEX idx_inbox_entries_message_status ON inbox_entries (message_id, status);
ANALYZE inbox_entries;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id FROM inbox_entries
 WHERE inbox_id='ib' AND chat_id='c1' AND status='pending' AND notify=true
 ORDER BY id ASC LIMIT 50 FOR UPDATE SKIP LOCKED;

Scope

This predates and is independent of #2045: that PR does not touch pollInboxInner, and the plan above is unchanged whether the four-column idx_inbox_chat_silent or the partial pair that replaced it is present.