Problem in panel query in `global db overview` dashboard

[0xgouda]

While doing some updates to demo.pgwatch.com I did several restarts, then the Connections Overview panel in the 1. Global Database Overview pg dashboard showed negative numbers!

That a problem with the panel query that needs investigation

[0xgouda]

In general, I think the entire dashboard's queries are a bit old and need re-checking and investigation, probably addressing #1152 as well.

[mohamadyasser118]

can I work on this?

[Mazen050]

Hey I have encountered this while working on #1239, but I couldn’t find reliable reproduction steps at the time.

If you can share a way to reproduce the issue, I can take a look and implement a fix within my existing PR.

[Mazen050]

@0xgouda Hello, I have investigated this and I think the dip happens because of

  coalesce((data->>'sessions')::int8 - lag((data->>'sessions')::int8) over w, 0) as established

also checking the db around the dip time

             time              | sessions_raw | prev_sessions
-------------------------------+--------------+---------------
2026-03-16 01:00:27.73623+00  |          721 |           719
 2026-03-16 01:00:57.753922+00 |          722 |           721
 2026-03-19 14:31:54.982433+00 |            6 |           722
 2026-03-19 14:32:24.985809+00 |            6 |             6

this is the dip:

 dbname |             time              | sessions_raw | established 
--------+-------------------------------+--------------+-------------
 demo   | 2026-03-19 14:31:54.982433+00 |            6 |        -716

Because since (data->>'sessions')::int8 is the current sessions and lag((data->>'sessions')::int8 is the previos. When restarting the current session becomes a small number and sometimes when there was a high number of session this can cause lag((data->>'sessions')::int8 to be bigger than the current session causing a negative number.

I have considered wrapping the calculation with GREATEST which would return 0 instead of a negative number, but that silently discards real data, since sessions were established after the restart, they shouldnt be compared to previos sessions before the restart.

The more correct fix is counter reset detection, when the current value is less than the previos, treat the current value itself as the established:

coalesce(
  case
    when (data->>'sessions')::int8 < lag((data->>'sessions')::int8) over w
    then (data->>'sessions')::int8
    else (data->>'sessions')::int8 - lag((data->>'sessions')::int8) over w
  end,
  0
) as established

I will add this fix to my PR and I will investigate the other queries, I remember the postgres role panel had a problem aswell I will check them all.