Parked Supabase query changes and when to reopen them #326

Key: SPR-19 · Type: record · Part of: #327 · Blocked by: none · Checked against: 3bb26de

Problem

The 2026-09-28 prod performance review parked 4 query changes and rejected 14 ideas on the numbers. This issue records both lists, so no one proposes them again without new evidence. The do-not-do table has 15 rows, because it splits the foreign-key and unused-index idea into Q-3 and Q-4.

The prod database is almost idle. Every SQL statement together uses 35.0 s of DB time per hour. That is 0.97% of one CPU core. Supabase bills compute by instance size, not by use, so query work saves $0. Each item below saves a fraction of that already small total.

This is a record, like #259, #260 and #261. Nothing here is to be built now. Reopen an item only when its trigger fires.

Context

Source. The maintainer's local performance report of 2026-09-28. It used a 69.8-minute prod window (10:27:21Z to 11:37:07Z) from pg_stat_statements. Local benches ran on PostgreSQL 15.8 with pgmq 1.4.4, N=15, median reported.

Tags. [prod] = measured on prod. [local] = measured on a local database with prod-shaped data. [pred] = predicted, not measured. The local host has 18 cores and prod probably has 2. Read local results as ratios, not as prod milliseconds.

Baseline [prod], per hour, current window.

One change from the same review is not parked. #323 ships it: get_cron_job_health sampled every 180 s instead of 60 s. After #323, that RPC should drop to about 40 calls/h and about 1,245 ms/h [pred].

Useful conversion. 0.1% of one core = 3,600 ms of DB time per hour.

Parked changes

cron-2 detail. The current body is in packages/supabase/scripts/16-cron-jobs.sql ~63-90 (public.get_cron_job_health). It uses (array_agg(d.status order by d.start_time desc))[1], which sorts every row of cron.job_run_details on each call. Lifetime rows read by this RPC on prod: 129,781,904,655 [prod]. The daily cleanup job (cleanup-cron-job-run-details, same file ~42) now keeps the table small. That cut the call cost from a lifetime mean of 103 ms to 31.0 ms. The parked body:

create or replace function public.get_cron_job_health()
returns table (
    jobname text,
    last_run_status text,
    last_success_at timestamptz
)
language sql
security definer
stable
set search_path = public
as $$
    select
        j.jobname::text,
        (select d.status from cron.job_run_details d where d.runid = a.last_runid) as last_run_status,
        a.last_success_at
    from cron.job j
    left join (
        select jobid,
               max(runid) as last_runid,
               max(end_time) filter (where status = 'succeeded') as last_success_at
        from cron.job_run_details
        group by jobid
    ) a using (jobid);
$$;

Why it is safe:

• pg_cron takes runid from cron.runid_seq when it inserts the row at launch. So max(runid) per job is the latest run.
• A run still in starting has a null start_time. The old DESC sort put nulls first, and max(runid) also picks that newest row. The local bench found 0 mismatched rows against the current body.
• The status comes from one probe per job on job_run_details_pkey (runid). No new index is needed. postgres cannot create one, because it does not own cron.job_run_details.
• Same name, arguments and return columns, so create or replace keeps the ACL. Keep the revoke ... from public, anon, authenticated and grant ... to service_role lines after it, as the current script does (~89-90).

cron-4 detail. The function is public.aggregate_document_view_stats() in packages/supabase/scripts/09-document-views.sql ~497. Its CTE daily_raw (~602) filters on view_date >= current_date - 1 (~612) only. view_date is not the partition key, so each run scans every document_views partition. The parked one-line change:

        where view_date >= current_date - 1  -- Only today and yesterday
          and viewed_at >= (current_date - 2)::timestamptz  -- lets pg prune old partitions

Why the result cannot change: the only writer, process_document_views_queue, sets view_date = (viewed_at at time zone 'UTC')::date. So view_date >= C-1 implies viewed_at >= (C-1) 00:00 UTC. The new bound is (C-2) 00:00 in the session zone. That is earlier for any offset from -12h to +14h.

Honest saving: about 2 ms per run in absolute terms. One sampled prod run took 44.8 ms, and the window averaged about 55 ms per run (665 ms/h over 12 runs) [prod]. Most of that is not the daily scan. A cold backend still plans over every partition. So scale the absolute 2 ms, not the 2.7x ratio.

Do not fold in these two ideas with it:

• An early exit when no new views arrived. update_view_duration changes duration_ms and is_bounce without a new viewed_at, so the daily rows would go stale.
• An IS DISTINCT FROM guard on the upsert. It saves about 12 row updates per run.

RT-2 detail. Prod published 7 tables in supabase_realtime when this was written [prod]. #434 has since removed users (migration 20261009120500, applied in production). So 6 remain: channels, messages, channel_members, channel_message_counts, push_subscriptions, email_queue. No client code subscribes to postgres_changes on push_subscriptions or email_queue. In the repo:

• packages/supabase/scripts/17-realtime-replica.sql drops the publication (~9) and re-creates it with 5 tables (~18-26).
• packages/supabase/scripts/07-4-push-notifications-pgmq.sql ~699-704 adds push_subscriptions to it. The seed runs 07-4 before 17, so a local reset ends with 5 tables. In packages/supabase/seed.sql the add is at ~1699 and the re-create at ~9868.
• Prod got push_subscriptions from the applied migration 20260519200000_scripts_functions_triggers_parity.sql, which carries the 07-4 add.
• No script or migration adds email_queue to the publication. That membership is prod drift. The table itself is defined in 07-5-email-notifications-pgmq.sql ~26.
• The comment in 17-realtime-replica.sql ~14-15 names packages/admin-dashboard/src/pages/users.tsx. The real path is apps/admin-dashboard/src/pages/users.tsx (useRealtimeSubscription, ~122-123). The admin dashboard also subscribes to channels (apps/admin-dashboard/src/pages/channels.tsx ~39-40).
• packages/supabase/scripts/31-connected-app-token-gate.sql ~59-82 puts the restrictive policy "Connected apps use only the MCP server" on every published table. Its migration 20260928120000 is not on prod yet (Ship the three migrations that prod's history lacks #313). Once it is, prod gates 7 tables, and a local reset gates 5.
• Drop that policy with the table, so prod stays equal to a local reset. It does not open the Data API: the pre-request hook in the same script (~10-35) already refuses connected-app tokens there. The maintainer's performance report said to leave the policy alone. Its later breakage review replaced that advice with the drop.

P2 detail. apps/hocuspocus.server/src/lib/workerMetricsSampler.ts ~139-145 (sampleWorkerMetrics) calls samplePgmqQueueMetrics() on every 15 s tick (QUEUE_METRICS_INTERVAL_MS, ~15). Prometheus scrapes every 30 s (scripts/observability/prometheus/prometheus.yml ~2). So half of the samples are never read. The pgmq-message-age alert fires only at more than 15 min for 5 min (scripts/observability/grafana/provisioning/alerting/rules-supabase-jobs.yml ~47-50). The review judged 0.007% of one core not worth a second constant.

Do-not-do list

Each idea was measured and rejected. Reopen one only with new numbers that beat the row.

What to do

There is nothing to build now. Keep this issue open as the record. When a trigger fires, follow these steps.

1. [maintainer] Measure the RPC in question on prod. Run this read-only query, wait one hour, and run it again:

   select now(), s.queryid, s.calls, round(s.total_exec_time::numeric, 1) as total_ms
   from pg_stat_statements s
   where s.query ilike '%get_cron_job_health%'
      or s.query ilike '%get_pgmq_queue_metrics%'
      or s.query ilike '%aggregate_document_view_stats%';

   Per-hour cost = the difference in total_ms. Percent of one core = that difference / 3,600,000 x 100. Post both snapshots as a comment here.

2. [maintainer] If the number crosses the bar in the table, open a new issue for that one item. Link it here. Copy the item's detail block from this issue into it.

3. [agent] For cron-2 or cron-4, work scripts-first. Edit the function in its script (16-cron-jobs.sql or 09-document-views.sql). Then hand-write a timestamped migration in packages/supabase/migrations/ that holds only that one create or replace body. Neither 16-cron-jobs.sql nor 09-document-views.sql is in the generate:functions-parity-migration allowlist (packages/supabase/generate-functions-parity-migration.ts ~16-33). So do not use that generator.

4. [maintainer] Before an agent writes the cron-4 migration, read the prod body and check the bound on real rows. No repo migration has ever carried this body, so prod may differ from scripts/09:

   select pg_get_functiondef('public.aggregate_document_view_stats()'::regprocedure);
   
   -- must return 0
   select count(*) from public.document_views
   where view_date >= current_date - 1
     and viewed_at < (current_date - 2)::timestamptz;

5. [agent] For RT-2, write a new timestamped migration in packages/supabase/migrations/, later than 20260928130000. It drops each table from the publication and drops its gate policy, only if present:

   set local lock_timeout = '5s';
   
   do $$
   declare
     t text;
   begin
     foreach t in array array['push_subscriptions', 'email_queue'] loop
       if exists (select 1 from pg_publication_tables
                  where pubname = 'supabase_realtime' and schemaname = 'public' and tablename = t) then
         execute format('alter publication supabase_realtime drop table public.%I', t);
       end if;
       if to_regclass(format('public.%I', t)) is not null then
         execute format(
           'drop policy if exists "Connected apps use only the MCP server" on public.%I', t);
       end if;
     end loop;
   end $$;

   Also delete the add table push_subscriptions block in 07-4-push-notifications-pgmq.sql ~699-704, and keep the drop table notifications block above it. Fix the stale path in the 17-realtime-replica.sql comment (~14-15) and add the channels page. Do not regenerate the applied migration 20260519200000_scripts_functions_triggers_parity.sql for this. Local resets skip migrations (packages/supabase/config.toml ~53-58). So run the new file once against the local database with psql; it is a no-op there.

6. [agent] For P2, add a separate cadence constant in workerMetricsSampler.ts (2 ticks = 30 s, matching the scrape). Do not share CRON_HEALTH_SAMPLE_EVERY_TICKS: after Ship and measure the cron-health sampling change #323 it is 12 ticks (180 s). No house doc names the pgmq metrics cadence today. The cron-health bullet in apps/hocuspocus.server/CLAUDE.md (~162) describes the tick. Add one sentence there for the new constant, in the same change.

7. [agent] After any SQL change: regenerate the seed with bun run --filter @docs.plus/supabase_back seed, then run bun run --filter @docs.plus/supabase_back types. The signatures do not change, so apps/webapp/src/types/supabase.ts should show no diff. Include it if it does.

8. [agent] For cron-4, prove equal output locally. Run the old and new body in one rolled-back transaction. Compare document_views_daily with EXCEPT both ways, not only by row count.

9. [maintainer] After deploy, run step 1 again and post the before and after numbers on the new issue.

Out of scope

• Ship and measure the cron-health sampling change #323 ships the get_cron_job_health 60 s → 180 s sampling change (cron-1). This issue parks only what comes after it.
• Measure prod memory, swap and CPU for 7 days before any compute change #324 measures memory, swap and CPU before any compute size change, including Micro.
• Find the cause of the 13-15 s realtime list_changes spikes #325 owns the 13-15 s realtime.list_changes spikes.
• Remove pg_net from prod #321 removes pg_net.
• Remove prod-only drift: four functions and the PushSentLog table #320 owns the prod-only functions and the PushSentLog table, including its unused index.
• Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 owns grants and RLS. None of the parked changes touches a grant; create or replace keeps each ACL.

Acceptance criteria

This is a record. It is done when it is filed and each row is traceable.

• Each of the 4 parked items has its numbers, its bench, and a reopen trigger in this issue.
• Each of the 15 do-not-do rows has a number or a named contract that explains the rejection.
• No parked item is built while its trigger has not fired. A new issue for an item links back here and carries a before-and-after measurement from step 1.
• This issue closes only when a later ruling overturns it with new evidence. It also closes when every parked item has shipped or been dropped.

Verification

• The file references still hold at the current tree. Run from the repo root:

  grep -n "array_agg(d.status order by d.start_time desc)" packages/supabase/scripts/16-cron-jobs.sql
  grep -n "view_date >= current_date - 1" packages/supabase/scripts/09-document-views.sql
  grep -n "add table push_subscriptions" packages/supabase/scripts/07-4-push-notifications-pgmq.sql
  grep -n "void samplePgmqQueueMetrics()" apps/hocuspocus.server/src/lib/workerMetricsSampler.ts

  Each command prints one line while its item is still parked. An empty result means someone changed that code. Then update or close the row.

• For RT-2 after deploy, this read-only query on prod lists 4 tables: channel_members, channel_message_counts, channels, messages. (users left with Stop exposing every user's online status and last-active time #434.)

  select tablename from pg_publication_tables where pubname = 'supabase_realtime' order by 1;
  
  -- expect: the same 5 public tables, plus realtime.messages and storage.objects
  select schemaname, tablename from pg_policies
  where policyname = 'Connected apps use only the MCP server' order by 1, 2;

House rules

• Scripts first, then a paired timestamped migration, then regenerate the seed and run bun run --filter @docs.plus/supabase_back types (packages/supabase/CLAUDE.md).
• Never edit an applied migration. Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts.
• Bun only. Never run npm, yarn, pnpm or npx in this repo.
• No commits unless the maintainer asks (/commit-review). No branches.
• Tests are opt-in. The cron-4 EXCEPT check is a one-off local proof, not a new test file.