How to obtain reliable realtime updates in the real world.

[GaryAustin1]

I've been looking at the reliability of Supabase realtime for awhile and conclude that just doing the standard examples of starting a subscription and letting realtime handle re-connections in the background will cause loss of data changes. When faced with a lost connection or heartbeat the realtime code attempts to reconnect and resume BUT loses any updates on server during this time.

You have to put an error monitor in .subscribe() to see these connection errors (the payload portion of the code will look fine because of the reconnect attempts realtime is doing):
.subscribe((status) => {console.log('status - ', status)})

After testing on several browsers on a Windows desktop as well as mobile devices with (Safari and Chrome) it becomes clear subscriptions are lost in all cases (except Firefox on Windows) when the tab running realtime is not in focus, or device goes to sleep. Of course loss of internet connection even briefly can cause the same issue. Basic realtime examples in Supabase docs will all look like they are running normally when tab returns.

I'm looking at the following flow to be able to use realtime reliably (never miss a subscribed database change).

At first I was going to just use visibility of the page, but then if you just tab away from a page for a minute, you have to reload all your monitored realtime data.

There are other approaches in addition to above using timestamps that might reduce amount of data needing to be refreshed.

Here are some examples of realtime in background/sleeping tabs:

Windows Edge (Chrome is similar) on background tab:

iPad/Safari:
Note in this case the device goes to sleep and errors from earlier in time show up after tab comes back.

Android/Chrome (note results differ between powered and battery operation, but all still lose subscription in time):

[GaryAustin1]

I've implemented the above flowchart and done limited testing using this code structure.
So far this just handles a table or subset of table with limit, but would work for a single row with a filter also.
The code will keep the data array store updated thru failures in internet, tabs in background and devices going into power saving mode.
It also will allow hidden tabs to stay alive (not need reload) as long as realtime does not error (which will occur after minutes in many cases).

    let dataArrayStore = []  //store table data here
    const idCol = "id"
    const table = "messages"
    let hiddenError = false
	
    function visibilityListener() {
        if (document.visibilityState === "visible" && hiddenError) {
            startStream()  //going visible, start stopped stream
        }
    }
	
    document.addEventListener("visibilitychange", visibilityListener);

    startStream()

    function startStream () {
        hiddenError = false
        const mySubscription = supabase
            .from(table)
            .on('*', payload => {
                realtimeUpdate(payload.eventType, payload)
            })
            .subscribe((status) => {
                if (status === "SUBSCRIPTION_ERROR") {
                    if (document.visibilityState === "hidden") {    // page visible so let realtime reconnect and reload data  
                        supabase.removeSubscription(mySubscription)
                        hiddenError = true
                    }
                }
                if (status === "SUBSCRIBED") {
                    realtimeUpdate("RELOAD", {})
                }
            })
    }
	
    async function realtimeUpdate(eventType,payload) {
        switch (eventType)  {
            case "INSERT":
                dataArrayStore.unshift(payload.new)
                break
            case "UPDATE":
                let objIndex1 = dataArrayStore.findIndex((obj => obj[idCol] === payload.new[idCol]));
                if (objIndex1 !== -1)
                    dataArrayStore[objIndex1] = payload.new
                break
            case "DELETE":
                let objIndex2 = dataArrayStore.findIndex((obj => obj[idCol] === payload.old[idCol]));
                if (objIndex2 !== -1)
                    dataArrayStore.splice(objIndex2, 1)
                break
            case "RELOAD":
                const {data, error} = await supabase
                    .from(table)
                    .select('*')
                    .order(idCol, {ascending: false})
                    .limit(5)
                dataArrayStore = data
        }
    }

This yields the following when run in Chrome tab that errors after 5 minutes hidden.

[GaryAustin1]

@GaryAustin1 Thanks for posting this. Does this solution work for mobile devices switching to a different app and coming back?

I'll just say it "should" in general. Basically you just use a visibility handler (it triggered in all my tests) to know you have left focus for whatever reason and assume bad things can happen with realtime and shuts down on error until visible and restarts then. If the error occurs while visible, it just restarts the subscription and loads from "scratch" whatever info you missed. If realtime server misses a heartbeat the subscribe will error either while out of focus (like on edge/chrome where it runs for awhile then errors) or it will error on mobile power down when you come back as nothing ran while powered down.

So on a mobile app (this is only javascript I've looked at) it either keeps running fine in background or in some cases might be throttled or stopped depending on the device/browser/os. In any of those cases you will get an error and you reload when you come back.

The problem not watching the subscription error is that realtime will just retry in the background and reconnect with the same subscription and you just miss data. So all I'm really doing is watching the subscription error, which the documentation really does not (or did not, I've not looked in awhile) mention much.

In my real app, I also just shut down the realtime after 5 minutes hidden, even without error, since I have code to fix things up anyway, why waste a connection and bandwidth.

I've not done a whole lot of testing, especially on mobile, as I've been working on other things with the app, since this seemed to solve my problem.

I'm also interested to see if the new realtime (even though focused on something else) might change the way heartbeat works.

[zbennett]

Hey @GaryAustin1. I just wanted to follow up and say this solution does work for mobile web apps. Thanks again for posting this. Seems like a big hole in supabase realtime.

[GaryAustin1]

There might be a hole (not sure if new with the latest realtime server, or just did not test quick updates occurring after subscribe). Hopefully gets resolved and .subscribe() status works like one would expect... I just filed this:

Realtime-js returns "SUBSCRIBED" message before websocket returns subscribed message 2 to 3 seconds later
Even though marked closed, it is open for a previous more general issue, linked in the issue.

[shankar-narayanan-d]

Hi @GaryAustin1, even in this case, when the visiblity changes from hidden to visible, it takes 2-4 seconds to get the subscription right?
If there is any postgres changes in those 4 seconds, the client would not receive right?

[GaryAustin1]

Yes, that was the trigger back before Realtime changed to be more cloud focused. Now there is a second event message you have to watch for to do your init. This link towards the end has info supabase/realtime#282 on the message to watch. I am not near my code base for awhile so can’t show a sample.

[GaryAustin1]

Started working on an update to this for current realtime and also adding in how to deal with issues of getting reliable data at start up...

https://github.com/GaryAustin1/Realtime2

[clounie]

Hi Gary, thanks for your work on this.

I think you might find Jepsen interesting as a tool to test realtime2 at some point.

[sorokolokarim]

Hello ! May somebody help me to know what to do when Status === Closed ? Thank you

[GaryAustin1]

Not exactly sure what you are asking. I always restart the subscription and reload everything on error or close.
https://github.com/GaryAustin1/Realtime2/blob/main/src/app.js
You can see my last somewhat working code here. Note this is just test code and not in production so not well tested.

[JFGHT]

Was this already addressed? I am seeing this comment here and I am confused.

[GaryAustin1]

Was what addressed? The comment you link is just about the start up delay. It was 3 seconds or more, now it is much better at 100s of msec or less but there is still a delay. There are lots of other cases mentioned here also that exist like back ground tabs, low power modes, etc. in general you potentially miss payloads at startup and during error conditions and need to deal with it if you need 100% data updates.

[JFGHT]

Yeah, I read you previous message and misunderstood thinking they were both linked.

Thank you for you finding and your more than generous efforts to improve Supabase. I'll keep following the thread in case I can help with something.

[aslakhellesoy]

Hi all - I've just released Supabase Live Table.

The algorithm is similar to what @GaryAustin1 has implemented - subscribing before requesting a snapshot is pretty standard in trading software. I've also added additional reconciliation logic based on timestamps. During reconciliation we can decide to discard a message, or emit an error if we detect an anomaly.

You can see some sequence diagrams here - they are generated from the unit tests.

By decoupling the reconciler from the networking layer, I can unit test the reconciler by just firing messages at it. If anyone finds a bug, reproducing it in this test suite is a good place to start.

We've been using this in production at Open Art Market for a while now, and it's worked well for us.
Please try it out and let me know what you think. I'm open to feedback - good or bad.

[GaryAustin1]

I can't look at it right now, but will do so soon. Have you looked at my realtime2 repository that looks at another issue, although much lower probability of occurring with reasonable connect latency between the realtime server and your instance server? https://github.com/GaryAustin1/Realtime2/blob/main/src/app.js

[aslakhellesoy]

Hi @GaryAustin1 - yes I discovered your repo after I had written Supabase Live Table. There is a lot of overlap.

What specific issue are you referring to? If you don't think it's covered by any of the unit tests - could you give me a concrete example so I can add a test for it? -Or just send a PR?

Also check out the generated sequence diagrams that illustrate each unit test.

My implementation relies on timestamps to decide whether a realtime message should be processed or not. If it's older than the snapshot, it's dropped. The snapshot timestamp is determined by using the most recent created_at / updated_at timestamps of the snapshot rows.

I don't think it's possible to implement syncronization reliably without relying on timestamps.

[GaryAustin1]

The issues were payload events arriving before or after the initial table select data gets back to the client may or may not be in the returned data. So you need to buffer payloads before the select data returns and merge them together knowing they may exist or not in the select data. You also have to handle payloads after the select as they may be in the returned data or not. For instance you can have an insert that occurs right before your select is handled in the database. But it could show up in the payload handler after your select data gets to the client. So the insert exists in the select data and should be ignored even though it is a payload event after the select is returned.

I did not find timestamp was needed in my quick testing a few months ago. Just buffering and then using a primary key to always do the update as repeating it won't hurt and ignoring repeated inserts and deletes that have no match to delete seem to handle the cases.

I did not look at the payload commit timestamp so that likely helps handle the cases also.

The yellow and blue here show the two "odd" cases that have to be handled. At a quick glance at your code you appear to be handling them although you have some cases you flag as errors that I need to look at closer when I get a chance. It is perfectly valid to have an insert payload on existing data or a delete on data that does not exist right after your table select.

[aslakhellesoy]

I believe I have handled all the cases you describe. The tricky bit is with update events, and whether to ignore them or process them.

There are two things to consider:

1. Did the update event arrive before or after the snapshot?
2. Is the update event timestamped before or after the snapshot?

Whether the event arrives before or after the snapshot should not affect the outcome.
Only the timestamp should be used to determine whether or not to discard the event.

There are 4 scenarios to consider here:

replays updates that are more recent than snapshot and arrived after

sequenceDiagram
  LiveTable->>+Supabase: subscribe
  Supabase->>-LiveTable: subscription active
  LiveTable->>+Supabase: get snapshot
  Supabase->>-LiveTable: snaphot: [{"id":1,"created_at":"1","updated_at":"2","name":"Bicycle","type":"vehicle"}]
  Supabase-->>LiveTable: UPDATE {"id":1,"created_at":"1","updated_at":"3","name":"Bike","type":"vehicle"}

replica

[
  {
    "id": 1,
    "created_at": "1",
    "updated_at": "3",
    "name": "Bike",
    "type": "vehicle"
  }
]

replays updates that are more recent than snapshot and arrived before

sequenceDiagram
  LiveTable->>+Supabase: subscribe
  Supabase->>-LiveTable: subscription active
  LiveTable->>+Supabase: get snapshot
  Supabase-->>LiveTable: UPDATE {"id":1,"created_at":"1","updated_at":"3","name":"Bike","type":"vehicle"}
  Supabase->>-LiveTable: snaphot: [{"id":1,"created_at":"1","updated_at":"2","name":"Bicycle","type":"vehicle"}]

replica

[
  {
    "id": 1,
    "created_at": "1",
    "updated_at": "3",
    "name": "Bike",
    "type": "vehicle"
  }
]

skips updates that predate the snapshot and arrive after

sequenceDiagram
  LiveTable->>+Supabase: subscribe
  Supabase->>-LiveTable: subscription active
  LiveTable->>+Supabase: get snapshot
  Supabase->>-LiveTable: snaphot: [{"id":1,"created_at":"1","updated_at":"3","name":"Bicycle","type":"vehicle"}]
  Supabase-->>LiveTable: UPDATE {"id":1,"created_at":"1","updated_at":"2","name":"Bike","type":"vehicle"}

replica

[
  {
    "id": 1,
    "created_at": "1",
    "updated_at": "3",
    "name": "Bicycle",
    "type": "vehicle"
  }
]

skips updates that predate the snapshot and arrive before

sequenceDiagram
  LiveTable->>+Supabase: subscribe
  Supabase->>-LiveTable: subscription active
  LiveTable->>+Supabase: get snapshot
  Supabase-->>LiveTable: UPDATE {"id":1,"created_at":"1","updated_at":"2","name":"Bike","type":"vehicle"}
  Supabase->>-LiveTable: snaphot: [{"id":1,"created_at":"1","updated_at":"3","name":"Bicycle","type":"vehicle"}]

replica

[
  {
    "id": 1,
    "created_at": "1",
    "updated_at": "3",
    "name": "Bicycle",
    "type": "vehicle"
  }
]

[GaryAustin1]

Cool. Not sure why you care about timestamp on update. You can just do them I believe. Payloads are never out of order. So would not matter if update gets done twice on your local data.

[aslakhellesoy]

Not sure why you care about timestamp on update.

If you don't look at timestamps, how do you decide whether or not to apply an event? If you apply them regardless, you will end up with an incorrect replica. See the two "skips updates..." examples. Look at the value of name in the results - Bike or Bicycle.

[GaryAustin1]

If you do update to your local table, and it has already been done what changes in the data? Not sure how it hurts anything. Not sure what replica has to do with your local data. I'll look at your stuff closer, but not sure if payloads are guaranteed to come in order how it would matter. But I have a queue of payload operations before the select loads (versus just putting them in the local table) and run them after the selected data is loaded, in order, then new payloads run in order.

[GaryAustin1]

Not saying it is wrong to do what you are doing, just not sure why you think the time of the update matters as I don't use it my approach and need to understand if I am missing something.

[aslakhellesoy]

Let's consider this example:

sequenceDiagram
  LiveTable->>+Supabase: subscribe
  Supabase->>-LiveTable: subscription active
  LiveTable->>+Supabase: get snapshot
  Supabase->>-LiveTable: snaphot: [{"id":1,"created_at":"1","updated_at":"3","name":"Bicycle","type":"vehicle"}]
  Supabase-->>LiveTable: UPDATE {"id":1,"created_at":"1","updated_at":"2","name":"Bike","type":"vehicle"}

Would you agree that applying this UPDATE so that name="Bike" would lead to an incorrect replica?

[GaryAustin1]

Why? If it is the last update to the table, the table needs to be Bike. It does not matter if it occurs before or after your select, it is the real state of the data.
Edit.
I see your snapshot has a date later than the update. There would have to be another update payload with updated 3 after the updated_at 2.

All payloads are guaranteed to be in order.

I guess what I am saying is your scenario is impossible. Where did the update 3 go in the realtime payloads as it has to have occurred AFTER the update payload 2. So the only valid scenario is the payload 3 update is about to come and set things right again.

[aslakhellesoy]

In the example above, this might have happened:

• t1: INSERT INTO thing('name', 'type') VALUES ('Tricycle', 'vehicle')
• t2: UPDATE thing SET name='Bicycle' WHERE id=1
• t3: UPDATE thing SET name='Bike' WHERE id=1

While the realtime messages are guaranteed to be in order, they are not guaranteed to be delivered. It's conceivable that we receive the first update, but not the second.

This means we cannot be sure that our replica doesn't go stale, but with the timestamp check I have implemented, at least it won't be more stale than the snapshot.

However, if all the updates are applied as you suggest, our replica might be more stale than the snapshot.

[GaryAustin1]

Agree. If realtime ever fails to deliver the results of a change without an error on the connection which has to restart the process then that is bad news. And believe it can fail to deliver without dropping connection or erroring.

There could be an update 4 right after the update 3 and both scenarios with and without time stamp are outdated. The difference is 100's of msec in how bad the old data is...

I'll add your repository as a link to mine with a note about time stamping taking things a step further.

[GaryAustin1]

Hey, great work! Glad to see people digging into this.

[aslakhellesoy]

I'll add your repository as a link to mine with a note about time stamping taking things a step further.

Thanks! - I've done the same with yours.