realtime views

is it possible to create realtime views? do all tables joined in the view need to be realtime?

14 Replies

Realtime works on real tables only one (well actually you can do all tables) per subscription. You can either monitor all tables and join in the client, or when you get a change on a table do a select to a view or select with joins to get the whole picture.
You can also be creative and do triggers on several tables to update a "subscribe" table that realtime monitors to get a the joined data thru that table. But this is probably better for lots of subscriptions with few changes.

jtOP•3y ago

thanks. for a view with 5+ joins, is your first option unrealistic? the second option is interesting but what do you mean few changes? like not a chat room?

jtOP•3y ago

also, what do you think of this solution? https://github.com/supabase/supabase/discussions/5152#discussioncomment-2091589

GitHub

Schema Isolation approach when working with tables used in realtime...

Problem Statement For reference, Schema Isolation as defined in PostgREST doc: It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which in...

garyaustin•3y ago

It is hard to know in advance. Writes are very expensive in a database, reads are cheap. So with the intermediate table you are doing 5 reads and one extra write to the subscribe table. Versus 5 extra reads per user that is subscribed. So with small chat rooms benefit goes down. With 10,000 users subscribe on a single room the benefit goes up. You also are generating a bunch of extra code because you have to do the initial load from the client with the view or joins, then anytime realtime connection drops, reload it again. The subscribe table would not have context of the past (well at least the way I implement it with update on a single row, I guess you can just keeping doing inserts and clean it up).

jtOP•3y ago

hmm. thanks for the tip a json join n jam upon content creation/edit could be added to the realtime messsge table no? because essentially i have this as a view

 SELECT slideshow_creator.account_id AS slideshow_creator_id,
    slideshow_creator.username AS slideshow_creator_username,
    slide_creator.account_id AS slide_creator_id,
    slide_creator.username AS slide_creator_username,
    account.account_id,
    account.username AS account_username,
    slideshow.slideshow_id,
    slideshow.account_id AS slideshow_account_id,
    slideshow.created_at AS slideshow_created_at,
    slideshow.title AS slideshow_title,
    slide.slide_id,
    slide.created_at AS slide_created_at,
    slide.title AS slide_title,
    slide.sort_order AS slide_sort_order,
    slide.tags AS slide_tags,
    slide.slide_type_id,
    slide_content.slide_content_id,
    slide_content.content_entity_id AS slide_content_content_entity_id,
    slide_content.slide_content_type_id,
    slide_content_type.content_type AS slide_content_type_content_type,
    slide_image.image_url AS slide_image_image_url,
    slide_video.video_url AS slide_video_video_url
   FROM slideshow
     LEFT JOIN slide USING (slideshow_id)
     LEFT JOIN account ON account.account_id = slide.account_id
     LEFT JOIN account slideshow_creator ON slideshow.account_id = slideshow_creator.account_id
     LEFT JOIN account slide_creator ON slide.account_id = slide_creator.account_id
     LEFT JOIN slide_content USING (slide_id)
     LEFT JOIN slide_image ON slide_content.content_entity_id = slide_image.slide_image_id AND slide_content.slide_content_type_id = 2
     LEFT JOIN slide_video ON slide_content.content_entity_id = slide_video.slide_video_id AND slide_content.slide_content_type_id = 4
     LEFT JOIN slide_content_type ON slide_content_type.slide_content_type_id = slide_content.slide_content_type_id
  ORDER BY slideshow.created_at DESC, slide.sort_order;

 SELECT slideshow_creator.account_id AS slideshow_creator_id,
    slideshow_creator.username AS slideshow_creator_username,
    slide_creator.account_id AS slide_creator_id,
    slide_creator.username AS slide_creator_username,
    account.account_id,
    account.username AS account_username,
    slideshow.slideshow_id,
    slideshow.account_id AS slideshow_account_id,
    slideshow.created_at AS slideshow_created_at,
    slideshow.title AS slideshow_title,
    slide.slide_id,
    slide.created_at AS slide_created_at,
    slide.title AS slide_title,
    slide.sort_order AS slide_sort_order,
    slide.tags AS slide_tags,
    slide.slide_type_id,
    slide_content.slide_content_id,
    slide_content.content_entity_id AS slide_content_content_entity_id,
    slide_content.slide_content_type_id,
    slide_content_type.content_type AS slide_content_type_content_type,
    slide_image.image_url AS slide_image_image_url,
    slide_video.video_url AS slide_video_video_url
   FROM slideshow
     LEFT JOIN slide USING (slideshow_id)
     LEFT JOIN account ON account.account_id = slide.account_id
     LEFT JOIN account slideshow_creator ON slideshow.account_id = slideshow_creator.account_id
     LEFT JOIN account slide_creator ON slide.account_id = slide_creator.account_id
     LEFT JOIN slide_content USING (slide_id)
     LEFT JOIN slide_image ON slide_content.content_entity_id = slide_image.slide_image_id AND slide_content.slide_content_type_id = 2
     LEFT JOIN slide_video ON slide_content.content_entity_id = slide_video.slide_video_id AND slide_content.slide_content_type_id = 4
     LEFT JOIN slide_content_type ON slide_content_type.slide_content_type_id = slide_content.slide_content_type_id
  ORDER BY slideshow.created_at DESC, slide.sort_order;

garyaustin•3y ago

Hmmm, I commented on that post you link, but don't remember it, I'll look at it again. On your view and realtime 😱 . That is beyond anything I feel I can comment on...

jtOP•3y ago

yeah i figured that prob won't make sense in that use case. no real need for a realtime slideshow

jtOP•3y ago

but i'm still interested in doing this for a view that contains 3 tables. i found this: https://github.com/supabase/supabase/discussions/3824

GitHub

Subscribing to materialized views (like Hasura, RethinkDB and Fireb...

Howdy. I’ve been searching for a plausible Firebase alternative for a while now, and am delighted to have stumbled across this. Before I get too excited, I have one quick question: is there any way...

jtOP•3y ago

would appreciate your follow up opinion/comment on that post 🙂

garyaustin•3y ago

Well sort of the same thing with the intermediate table if I read it correctly. If views are fast then either going with just a change event for any table in the view (or using the schema per view approach...) and just reload what you need from the view when anything changes might work well. I think I would not have it maintain state and just do updates to some status row (or rows if it makes sense). I would probably only turn on the realtime when I have that "view" on the screen. Then turn it off when not in that view. You could also just have the realtime event do a flag something changed and only load the view and keep it updated with selects, when the user is actually "viewing" it.

jtOP•3y ago

so a poor-mans real-time? not sure what you mean turning it on and off?
i have a couple different types of UI views. a slideshow/story view and a chat view that consist of the exact same content. the whole slideshow view doesn't need to be realtime, but a status component of it does the chat view needs to be realtime obviously just like here on discord. so here on discord do you think there is a message table that is realtime, a reaction table that is realtime and a status table that is realtime?

garyaustin•3y ago

When I say on and off. If you are not in a screen in the app that shows a room/view, you either shut off realtime subscription, or you just have a flag something changed for an indicator or whatever. Then when the user goes to that page you load up the view you need (last 10 rows, last rows from a certain time, etc), and keep it updated with reloads as events come in, until the user leaves that screen. Basically I'm saying don't keep an entire complex view refreshed on every realtime update, if it is not being used right now. Just go fetch the view when it is actually being used. It all really depends on what your data is and if you just need a snapshot of a row, recent 20 rows, or everything.
I'm going to bail on further comment tonight as my icon is at work... Night.

jtOP•3y ago

thank you!

garyaustin•3y ago

Another thing to consider in the performance decision is the number of tables that are updated for a change in data, like an insert. If several tables change, I believe in the schema case you would generate a realtime event for each one, with the end result after them being what you want. Also with triggers to a common table a similar thing could occur. Seems like you would have to try and only trigger on a main table if the other tables don't change independently (which breaks the schema approach).

Gaming

Programming

realtime views

Did you find this page helpful?