realtime data lagging behind by ~20 minutes

[ldpr]

Describe the Bug

i've been trying to figure this out myself but I've hit a brickwall. things i've checked:

1. the machine umami is running on is set to UTC
2. the machine postgres is running on is set to UTC
3. it shouldn't be affected by the timezone in my umami profile as if it was, it'd be hour(s) before or afterwards, not ~20 minutes
4. nothing in postgres logs indicating connections may be maxed (which could cause umami requests to be queued)
5. nothing in syslog on the machine that runs umami indicating any errors

to be safe i bumped the postgres max_connections and restarted both postgres & umami afterwards and it didn't "work through the backlog" that i assumed existed.

to confirm the delay, i changed VPN location and disabled all extensions, triggered some views and events and nothing popped in realtime. i did this at around 4am UTC and waited for it to show in umami.

at 4:20 am UTC, it appeared

anyone have any ideas what might be causing this? nb: there is no cpu/ram/io issues on either host either and both are hosted inside the same hypervisor so latency isn't an issue

Database

PostgreSQL

Relevant log output

nothing in any logs that would indicate any possible issue

Which Umami version are you using? (if relevant)

2.13.2

Which browser are you using? (if relevant)

firefox

How are you deploying your application? (if relevant)

selfhosted, non-docker

[ldpr]

umami isn't held back by postgres either:

postgres=# select datname, datconnlimit from pg_database;
  datname   | datconnlimit 
------------+--------------
 umami      |           -1

postgres=# SHOW max_connections;
max_connections 
-----------------
300

300 should be plenty of elbow room, we do have a decent amount of traffic but it'd be well under 300.

---

i can't say for sure when this started happening, though i do remember checking it a few weeks ago and working as expected. does anyone else on 2.13.2 have the same problem?

[obstkompost]

My Umami instance is also extremely laggy in the realtime tab

[Brawl345]

Recently started using Umami and my "realtime" data also lags behind by 15-20 minutes. Using v2.13.2 with Postgres 17.

[notramo]

Sometimes it occurs with Cloud version too. #3001 is possibly related, but it's also possible that it's completely unrelated.

[czingenberg]

Same issue here.

Got 2 sites - The first one is in sync with realtime, and the second one is 15-20 min behind

[mikeckennedy]

Same, sort of. If you look at the graph, it's real time but if you look at the log of activity, it's the thing lagging. This screen shot was taken at 12:48. You can see the graph has data at this snapshot time. But the log does not. How can we have visits between 12:31 and 12:48 but not in the log? :) That's where the problem is.

[mikecao]

The realtime page does a single query for the entire page, so it's possible it's doing something wrong when fetching the logs.

[Ternado-git]

The realtime page does a single query for the entire page, so it's possible it's doing something wrong when fetching the logs.

Hi, The bug originates from the file https://github.com/umami-software/umami/blob/master/src/queries/analytics/getRealtimeActivity.ts, where events are sorted by created_at. When you sort the timestamps in ascending order and limit the number of returned items, the latest event data gets lost. I think it should be changed to sort the timestamps in descending order to prioritize fetching the most recent 100 entries. Additionally, you need to modify the events.reverse() in the https://github.com/umami-software/umami/blob/master/src/queries/analytics/getRealtimeData.ts file, as reversing is no longer necessary.