Follow-up to the PostgreSQL sink (#1532, integration in #1687), prompted by the review remark on #1532 that the pushes table is the scale risk.
Push rows are large: the stored action carries the full diff inside steps (both steps[].logs and steps[].content), so any query that reads the data JSONB detoasts megabyte rows. Three hot paths currently do that on the PostgreSQL sink:
getRepoPushRollupsByCanonicalUrl runs on every repos page and selects data->>'url' from every push row: a full scan that detoasts every row.getPushes (dashboard list) returns the full data document, diffs included. The mongo backend projects a field allowlist that excludes steps, so this is also a backend parity gap.getPushesForUserProfile filters on JSONB expressions (data->>'userEmail', reviewer username) with no index support.
Proposed, all append-only:
- A covering expression index so the rollup becomes an index-only scan that never touches the heap:
ON pushes ((data->>'url'), timestamp) INCLUDE (error, rejected, canceled, authorised, blocked, allow_push) WHERE type = 'push'.
- A partial index matching the default pending-list query:
ON pushes (timestamp DESC) WHERE type = 'push' AND blocked AND NOT error AND NOT authorised AND NOT allow_push.
- Expression indexes for the user-profile predicates.
- List queries select
data - 'steps', matching the mongo projection and keeping diffs out of list responses.
Acceptance: schema migration plus query changes on the PostgreSQL sink branch family, with unit coverage. The broader question of shrinking the stored push document itself is tracked separately.
Follow-up to the PostgreSQL sink (#1532, integration in #1687), prompted by the review remark on #1532 that the pushes table is the scale risk.
Push rows are large: the stored action carries the full diff inside
steps(bothsteps[].logsandsteps[].content), so any query that reads thedataJSONB detoasts megabyte rows. Three hot paths currently do that on the PostgreSQL sink:getRepoPushRollupsByCanonicalUrlruns on every repos page and selectsdata->>'url'from every push row: a full scan that detoasts every row.getPushes(dashboard list) returns the fulldatadocument, diffs included. The mongo backend projects a field allowlist that excludessteps, so this is also a backend parity gap.getPushesForUserProfilefilters on JSONB expressions (data->>'userEmail', reviewer username) with no index support.Proposed, all append-only:
ON pushes ((data->>'url'), timestamp) INCLUDE (error, rejected, canceled, authorised, blocked, allow_push) WHERE type = 'push'.ON pushes (timestamp DESC) WHERE type = 'push' AND blocked AND NOT error AND NOT authorised AND NOT allow_push.data - 'steps', matching the mongo projection and keeping diffs out of list responses.Acceptance: schema migration plus query changes on the PostgreSQL sink branch family, with unit coverage. The broader question of shrinking the stored push document itself is tracked separately.