Background
realtime.list_changes accounts for ~3.7% of DB time under load. It fires every time a row changes in a table that's in the supabase_realtime publication — currently jobs and notifications.
The jobs table is written frequently: every task completion triggers update_job_counters, which updates the jobs row. Each of those writes causes realtime.list_changes to fire.
Removing jobs from the publication would eliminate the overhead but break the live progress bar in the dashboard (it would only update on page refresh).
Options
Option A — Frontend polling (simplest)
Replace the postgres_changes subscription in the JS with a periodic fetch to an API endpoint every 2-3 seconds. Progress bar updates every 2-3s instead of instantly. Removes jobs from the publication. Medium JS change, no backend change.
Option B — Supabase Broadcast (best tradeoff)
Use Supabase's Broadcast channel instead of DB change detection:
- Remove
jobs from the supabase_realtime publication
- Go server pushes progress updates directly to Supabase Realtime via Broadcast API at a controlled rate (e.g. at most once/second per job, batched)
- Frontend subscribes to a Broadcast channel instead of
postgres_changes
realtime.list_changes stops firing for jobs entirely. Live UX preserved. More implementation work.
Option C — Batch jobs counter writes
Rather than updating jobs on every task completion (row-level trigger), batch the counter updates so jobs is written once per batch flush. Fewer writes → fewer list_changes events. Bigger trigger architecture change.
Priority
Low — deferred during the #312 optimisation pass. Other fixes (trigger merge, indexes, promotion fix) deliver the bulk of the CPU reduction. Revisit if CPU is still a bottleneck post-merge.
Background
realtime.list_changesaccounts for ~3.7% of DB time under load. It fires every time a row changes in a table that's in thesupabase_realtimepublication — currentlyjobsandnotifications.The
jobstable is written frequently: every task completion triggersupdate_job_counters, which updates the jobs row. Each of those writes causesrealtime.list_changesto fire.Removing
jobsfrom the publication would eliminate the overhead but break the live progress bar in the dashboard (it would only update on page refresh).Options
Option A — Frontend polling (simplest)
Replace the
postgres_changessubscription in the JS with a periodicfetchto an API endpoint every 2-3 seconds. Progress bar updates every 2-3s instead of instantly. Removesjobsfrom the publication. Medium JS change, no backend change.Option B — Supabase Broadcast (best tradeoff)
Use Supabase's Broadcast channel instead of DB change detection:
jobsfrom thesupabase_realtimepublicationpostgres_changesrealtime.list_changesstops firing for jobs entirely. Live UX preserved. More implementation work.Option C — Batch
jobscounter writesRather than updating
jobson every task completion (row-level trigger), batch the counter updates sojobsis written once per batch flush. Fewer writes → fewerlist_changesevents. Bigger trigger architecture change.Priority
Low — deferred during the #312 optimisation pass. Other fixes (trigger merge, indexes, promotion fix) deliver the bulk of the CPU reduction. Revisit if CPU is still a bottleneck post-merge.