Make presence heartbeats HOT updates and drop the duplicate username index #491

Summary

Each presence heartbeat rewrites one public.users row, and every heartbeat is a non-HOT update today. So each heartbeat adds an entry to all 5 indexes on the table and writes more WAL than it needs. This issue changes one partial index, so a heartbeat becomes a HOT update. It also drops idx_users_username, which repeats users_username_key.

This is part of the stuck-Online presence set in #496. It does not fix the stuck-Online bug, and it changes no behaviour. It removes a cost that grows with every visible tab.

This is not urgent. The 1x scale here is about 181 users, the production public.users row count from the 2026-09-28 Supabase review (not measured here). The Step 0 total gives the live count. At 1x, WAL goes from about 13 MB to 7 MB a day (derived). Ship it as cheap cleanup, ideally after #489.

Root cause

• The heartbeat writes only status, once every 60 s per visible tab (useHandleUserStatus.ts#L9, #L34-L41, updateUser.ts#L4-L9).
• On every status write, the trigger trigger_update_user_online_at (BEFORE UPDATE OF status) sets NEW.online_at := now() (10-1-func-users.sql#L133-L150). Bring back push on Chrome and Android devices the server switched off #417 (migration 20261006120000_push_online_at_and_preference_checks.sql) made it stamp on every write. Before Bring back push on Chrome and Android devices the server switched off #417, it stamped only when status changed. The Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 body stamps on every ONLINE write, so it also stamps on each heartbeat.
• idx_users_online_status keys (id, online_at) WHERE status = 'ONLINE' (02-users.sql#L83-L87). Each heartbeat changes online_at, which is an indexed column. So Postgres cannot do a HOT update, and the heartbeat adds an entry to every index.
• idx_users_username (11-indexes.sql#L8-L10) repeats users_username_key. The unique at 02-users.sql#L13 creates that index.
• No migration creates either index, so production most likely got them by hand. Step 0 reads the production shape.

Evidence

• Measured locally (shared dev DB, read-only, 2026-10-14). public.users has 3 rows, 73,481 updates and 2,182 HOT updates (3.0 %). users_pkey is 11.8 MB and users_email_key is 14.1 MB. Index churn from non-HOT updates explains this.
• Measured locally (scratch container public.ecr.aws/supabase/postgres:15.8.1.073, since removed). The table copies the public.users columns and the stamp trigger. Each heartbeat is UPDATE … SET status = 'ONLINE' WHERE id = … in its own transaction.

• ONLINE↔OFFLINE flips stay non-HOT with (id), because status is in the index predicate. They are HOT only with no partial index.
• The cron sweep at 181,000 rows took 0.10 ms today, 0.41 ms with (id), and 13.8 ms with no partial index (5,818 buffer reads). Measured locally, scratch container.

Scale

Assumption, not measured: about 5 % of users have a visible tab. Heartbeats per day are derived from one heartbeat every 60 s per tab. WAL per day is derived from the table above.

At 1000x the change also removes about 72 M index inserts a day (derived: 14.4 M heartbeats × 5 indexes). #434 took public.users out of supabase_realtime in production (its closing comment), so a heartbeat sends no Realtime change.

Readers of the two indexes

No index on public.users is the replica identity (relreplident = d, checked locally).

Decisions

Maintainer rulings are posted as a comment.

1. Keep the partial index as (id), or drop it? Ruled 2026-10-15: keep (id). At 181,000 rows the sweep costs 0.41 ms with it and 13.8 ms without it (measured locally). Dropping it would make flips HOT too, and that is also defensible. But flips are rare next to heartbeats. A tab sends 60 heartbeats an hour (derived from the 60 s interval). We assume a session flips about twice an hour (assumption, not measured).

Depends on

• Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489: ship it first if possible; it does not block this issue. Its stamp body still stamps every ONLINE write, so the HOT half still helps after it.
• Make the chat bell use the online lease instead of the raw status flag #490: independent. The two issues touch different objects, so either order works. Take the next free migration timestamp ([Tracking] Stuck-Online users and the presence review follow-ups #496 Shared rules).
• Stop exposing every user's online status and last-active time #434: independent. Its migration 20261009120500 changed grants and the publication, not indexes. Its closing comment says it is applied in production.

Fix plan

Step 0. Production read (the maintainer runs it; read-only)

Paste this into the production SQL editor as one statement. It returns one row with one JSON value. Copy that value into a comment on this issue. Run it before Step 1, and again just before Step 4. The second run is reading R0 for Step 6. If the paste follows on the same day, one run serves both.

select json_build_object(
  'read_at', now(),
  'current_user', current_user,
  'can_drop', (select pg_has_role(current_user, tableowner, 'MEMBER') from pg_tables where schemaname = 'public' and tablename = 'users'),
  'total', (select count(*) from public.users),
  'counters', (select row_to_json(t) from (
      select n_tup_upd, n_tup_hot_upd,
             round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) as hot_pct
        from pg_stat_user_tables where relid = 'public.users'::regclass) t),
  'indexes', (select json_agg(t order by t.index) from (
      select indexrelid::regclass::text as index, pg_get_indexdef(indexrelid) as def,
             indisunique, indisreplident
        from pg_index where indrelid = 'public.users'::regclass) t)
) as reading;

This query ran on the local DB (measured locally, 2026-10-15). It returned can_drop: true and the 5 indexes.

Stop and post the reading if any of these is true:

• can_drop is false. The paste cannot drop the indexes.
• An index other than idx_users_online_status has a def that names online_at. The migration guard would raise.
• users_username_key is not a unique btree on (username), or idx_users_username exists and is not a plain btree on (username).
• An index on public.users has indisreplident true.

Step 1. Repo change

1. In packages/supabase/scripts/02-users.sql, replace lines 83-87 with:

   -- The cron sweep reads this index. online_at stays out of the key,
   -- so a heartbeat is a HOT update.
   create index if not exists idx_users_online_status
       on public.users (id)
       where status = 'ONLINE';

2. In packages/supabase/scripts/11-indexes.sql, delete lines 8-10 and the blank line after them.

3. Add packages/supabase/migrations/20261014120200_users_heartbeat_hot_index.sql. If any file in packages/supabase/migrations/ sorts at or after it, use a timestamp newer than the newest file ([Tracking] Stuck-Online users and the presence review follow-ups #496 Shared rules). With if not exists alone, the old index would stay, so a guard drops it while its definition names online_at.

   -- Heartbeats become HOT updates: no index on public.users names online_at.
   set local lock_timeout = '5s';
   
   drop index if exists public.idx_users_username;
   
   do $$
   begin
       if exists (select 1 from pg_indexes
                   where schemaname = 'public' and indexname = 'idx_users_online_status'
                     and position('online_at' in indexdef) > 0) then
           drop index public.idx_users_online_status;
       end if;
   end $$;
   
   create index if not exists idx_users_online_status
       on public.users (id)
       where status = 'ONLINE';
   
   do $$
   begin
       if exists (select 1 from pg_indexes
                   where schemaname = 'public' and tablename = 'users'
                     and position('online_at' in indexdef) > 0) then
           raise exception 'an index on public.users still names online_at; heartbeats stay non-HOT';
       end if;
   end $$;

4. In apps/hocuspocus.server/CHANGELOG.md, under ## [Unreleased], add a ### Changed heading above ### Fixed if it is missing. Add this bullet under it, word for word. If item 3 took a later timestamp, use the real migration name.

   - **Presence heartbeats no longer touch every `users` index.**
     `idx_users_online_status` now keys only `id`, so a heartbeat is a HOT
     update. The duplicate `idx_users_username` is dropped; `users_username_key`
     serves the same lookups. Apply the Supabase migration
     `20261014120200_users_heartbeat_hot_index` (#491).

5. Do not run types, and do not commit apps/webapp/src/types/supabase.ts ([Tracking] Stuck-Online users and the presence review follow-ups #496 Decision 1).

Step 2. Regenerate and apply locally

Run each command from the repo root.

1. Regenerate the seed. seed.sql changes. No app code changes.

   bun run --filter @docs.plus/supabase_back seed

2. Apply the migration locally, twice. Always use --single-transaction and ON_ERROR_STOP. Plain psql < runs in autocommit, so set local only warns, and a failed guard leaves the drops committed.

   docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 --single-transaction < packages/supabase/migrations/20261014120200_users_heartbeat_hot_index.sql

   The first run prints SET, DROP INDEX, DO, CREATE INDEX, DO. The second run prints the same tags plus only these two notices: index "idx_users_username" does not exist, skipping and relation "idx_users_online_status" already exists, skipping.

Step 3. Prove by sabotage

Run from the repo root. The block puts back the old index shape, sends one heartbeat, then runs the migration file and sends one more. It ends in rollback;, so it leaves no change. Before each heartbeat, the block ages online_at by one hour. That update leaves status alone, so the trigger does not fire. The heartbeat's stamp then really changes online_at, even inside one transaction. pg_stat_xact_user_tables counts only this transaction, so other local writers cannot change the result.

M=packages/supabase/migrations/20261014120200_users_heartbeat_hot_index.sql
{ cat <<'SQL'
begin;
set local lock_timeout = '5s';
-- sabotage: the Rollback body (old shape)
drop index if exists public.idx_users_online_status;
create index idx_users_online_status on public.users (id, online_at) where status = 'ONLINE';
create index if not exists idx_users_username on public.users (username);
select id as u from public.users order by id limit 1 \gset
update public.users set status = 'ONLINE' where id = :'u';
update public.users set online_at = now() - interval '1 hour' where id = :'u';
select 'old before' as t, n_tup_upd, n_tup_hot_upd from pg_stat_xact_user_tables where relid = 'public.users'::regclass;
update public.users set status = 'ONLINE' where id = :'u';
select 'old after' as t, n_tup_upd, n_tup_hot_upd from pg_stat_xact_user_tables where relid = 'public.users'::regclass;
SQL
cat "$M"
cat <<'SQL'
update public.users set online_at = now() - interval '1 hour' where id = :'u';
select 'new before' as t, n_tup_upd, n_tup_hot_upd from pg_stat_xact_user_tables where relid = 'public.users'::regclass;
update public.users set status = 'ONLINE' where id = :'u';
select 'new after' as t, n_tup_upd, n_tup_hot_upd from pg_stat_xact_user_tables where relid = 'public.users'::regclass;
rollback;
SQL
} | docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 -q

Expect n_tup_hot_upd to stay the same from old before to old after, and to grow by 1 from new before to new after. n_tup_upd grows by 1 in both pairs. A local run with this migration body gave 2|0 → 3|0 and 4|1 → 5|2 (measured locally, 2026-10-15). If the old pair also grows by 1, the stamp did not change online_at; check the local trigger body before you go on.

Step 4. Production paste (the maintainer; no app deploy)

Paste at low traffic. The drop holds ACCESS EXCLUSIVE until commit. While it waits for that lock, reads of users queue behind it for up to 5 s, and message lists join users.

1. Take reading R0 (Step 0), unless the Step 0 reading is from the same day.

2. Paste begin;, the migration body from Step 1 item 3 and commit; into one tab, and run the tab once. Separate runs may use different pooled connections, so begin; would not cover the rest.

3. On a lock_timeout error, the transaction rolled back and nothing was applied. Run the tab again.
   If the next db push ran before the paste, it applied this migration already. Skip the paste and run only item 4.

4. Check on production. Run each select on its own, because the SQL editor may show only the last result.

   select pg_get_indexdef('public.idx_users_online_status'::regclass);
   -- expect: CREATE INDEX idx_users_online_status ON public.users USING btree (id) WHERE (status = 'ONLINE'::user_status)
   select to_regclass('public.idx_users_username');
   -- expect: null

A plain build is fine at this size. A plain rebuild at 181,000 rows took 22.8 ms (measured locally, scratch container). A plain build blocks heartbeat writes while it runs, and that wait grows with the table.

Step 5. Migration history

Do not run db push for this issue alone. The CLI pushes every pending file, so this migration ships with the maintainer's next planned db push. #496 The next db push gives the steps for that push. Do not run migration repair for this version. A re-run is harmless, so a repair is not needed (packages/supabase/CLAUDE.md:23).

That re-run changes nothing and prints only skipping notices. create index if not exists still takes a brief ShareLock on users. That lock blocks heartbeat writes but not reads, and lock_timeout limits the wait.

Step 6. Close-out reading

24 h after the paste, the maintainer runs Step 0 again. This is reading R1. Compute the delta: R1 n_tup_upd and n_tup_hot_upd minus R0. Post R1 and the delta here.

Success means 80 % HOT or more in the delta. Heartbeats alone were 99.1 % HOT (measured locally, scratch container). Flips and profile edits stay non-HOT, so 80 % leaves room for them. If R1 n_tup_upd is lower than R0, the counters were reset; take a new R0 and read again 24 h later. Under 80 %, post the readings and leave the issue open.

Rollback

No data changes. Paste into one tab and run it once:

begin;
set local lock_timeout = '5s';
drop index if exists public.idx_users_online_status;
create index idx_users_online_status on public.users (id, online_at) where status = 'ONLINE';
create index if not exists idx_users_username on public.users (username);
commit;

In the repo, revert the change. If remote history already has the migration, ship a forward migration with this body. Leave out begin; and commit;, because the CLI wraps each file in a transaction.

Verify

Run on the local DB after Step 3. Save the block as /tmp/i3-verify.sql, outside the repo, and run docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < /tmp/i3-verify.sql. The block ends in rollback;, so set local does not stay.

begin;
select indexrelid::regclass, pg_get_indexdef(indexrelid)
  from pg_index where indrelid = 'public.users'::regclass order by 1;
-- expect 4 rows: users_pkey, users_username_key, users_email_key,
-- idx_users_online_status ... (id) WHERE (status = 'ONLINE'::user_status)
select to_regclass('public.idx_users_username');
-- expect: null
select count(*) from pg_indexes
 where schemaname = 'public' and tablename = 'users' and position('online_at' in indexdef) > 0;
-- expect: 0
set local enable_seqscan = off;   -- the local table has 3 rows, so without it the planner picks a Seq Scan
explain (costs off) select id from public.users where username = 'abc';
-- expect: Index Scan (or Index Only Scan) using users_username_key
explain (costs off) select count(*) from public.users where status = 'ONLINE';
-- expect: a scan that uses idx_users_online_status
rollback;

The two explain lines ran locally with enable_seqscan = off (measured locally, 2026-10-14). They gave Index Scan using users_username_key and Index Only Scan using idx_users_online_status.

Acceptance criteria

• A Step 0 reading is posted before Step 1, and none of its stop rules applies.
• scripts/02-users.sql, scripts/11-indexes.sql, the new migration and the regenerated seed.sql match Step 1.
• The [Unreleased] CHANGELOG bullet names the real migration.
• apps/webapp/src/types/supabase.ts is not in the change ([Tracking] Stuck-Online users and the presence review follow-ups #496 Decision 1).
• The local apply runs twice with --single-transaction. The second run raises no error, and its only notices are the two skipping notices in Step 2 item 2.
• The Step 3 block shows no HOT gain for the old shape. It shows a HOT gain of 1 after the migration.
• The Verify block returns the expected rows locally. The Step 4 item 4 checks return the expected rows on production.
• db push and migration repair were not run for this issue alone.
• R0 and R1 are posted here, and the delta is 80 % HOT or more.

Out of scope

• The stamp body in update_user_online_at: Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 owns it.
• Making ONLINE↔OFFLINE flips HOT: status stays in the index predicate. Decision 1 keeps the index.
• Fillfactor 90: the scratch run gave 100 % HOT against 99.1 %, for more WAL per heartbeat.
• A new automated test: Step 3 is the real-stack check, and its sabotage half proves it.
• Removing public.users from the Realtime publication: Stop exposing every user's online status and last-active time #434 already did it in production.
• Using CONCURRENTLY at today's size: a plain build takes milliseconds below about 1M rows.
• Fixing the generator drift in apps/webapp/src/types/supabase.ts: [Tracking] Stuck-Online users and the presence review follow-ups #496 Decision 1 sends it to its own issue.

Generated by Claude Code