S
Supabase•2d ago
trunch

How to stop persisting old realtime messages?

I feel like there's probably a simple solution to this that I'm missing... but I'm currently hitting database size limits because all of my realtime messages are being persisted in tables like these: 
  realtime.messages_2025_07_27,
  realtime.messages_2025_07_28,
  realtime.messages_2025_07_29,
  realtime.messages_2025_07_30,
  realtime.messages_2025_07_31
  realtime.messages_2025_07_27,
  realtime.messages_2025_07_28,
  realtime.messages_2025_07_29,
  realtime.messages_2025_07_30,
  realtime.messages_2025_07_31
What's the best way to NOT retain messages past some arbitrary amount of time (like a day)? I've tried setting up cron jobs for this but they all work temporarily and then eventually start failing. What's the best way to handle this? I don't think I want to keep a history of realtime updates (maybe I'm missing something).
4 Replies
garyaustin
garyaustin•2d ago
I would generate an issue in supabase/realtime and ask. I thought they were only there for awhile, but your dates say not. They are partitions so could certainly be managed with a cron task if Supabase does not plan on doing it.
trunch
trunchOP•2d ago
Got it, will do -- thanks. Do you have any recommendation for managing via a cron task? I've tried something like this but it doesn't seem to be working. 
create or replace function realtime.drop_old_message_partitions(retention_days integer default 1)
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  part record;
  part_date date;
begin
  for part in
    select p.relname as relname, n.nspname as schemaname
    from pg_inherits i
    join pg_class p on p.oid = i.inhrelid
    join pg_class c on c.oid = i.inhparent
    join pg_namespace n on n.oid = p.relnamespace
    where n.nspname = 'realtime' and c.relname = 'messages'
  loop
    begin
      part_date := to_date(substring(part.relname from 'messages_(\\d{4})_(\\d{2})_(\\d{2})'), 'YYYY_MM_DD');
    exception when others then
      part_date := null;
    end;
    if part_date is not null and part_date < (current_date - retention_days) then
      execute format('drop table if exists %I.%I cascade', part.schemaname, part.relname);
    end if;
  end loop;
end
$$;
create or replace function realtime.drop_old_message_partitions(retention_days integer default 1)
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  part record;
  part_date date;
begin
  for part in
    select p.relname as relname, n.nspname as schemaname
    from pg_inherits i
    join pg_class p on p.oid = i.inhrelid
    join pg_class c on c.oid = i.inhparent
    join pg_namespace n on n.oid = p.relnamespace
    where n.nspname = 'realtime' and c.relname = 'messages'
  loop
    begin
      part_date := to_date(substring(part.relname from 'messages_(\\d{4})_(\\d{2})_(\\d{2})'), 'YYYY_MM_DD');
    exception when others then
      part_date := null;
    end;
    if part_date is not null and part_date < (current_date - retention_days) then
      execute format('drop table if exists %I.%I cascade', part.schemaname, part.relname);
    end if;
  end loop;
end
$$;
select realtime.drop_old_message_partitions(1);
select realtime.drop_old_message_partitions(1);
garyaustin
garyaustin•2d ago
I've never used partitions sorry.
trunch
trunchOP•2d ago
np thx

Did you find this page helpful?