Re: pgAdmin Async Server Cursor

Hi

On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi <
khushboo(dot)vashi(at)enterprisedb(dot)com> wrote:

> Hackers,
>
> Regarding #5797 <https://github.com/pgadmin-org/pgadmin4/issues/5797>: Full
> query result is being loaded into RAM despite ON_DEMAND_RECORD_COUNT=1000
>
> pgAdmin uses the *Async cursor* to fetch the data in the query tool,
> which basically fetches and stores the entire result on the client side, in
> our case, the pgAdmin server.
> So, if the query result is very large, it uses more memory on the pgAdmin
> server. We use the ON_DEMAND_RECORD_COUNT to fetch the partial data from
> the cursor (which is already transferred to the pgAdmin server) and show it
> on the UI.
>
> To overcome this, we can use the* Async Server Cursor*, which transfers
> data from the Postgres Server to the client (pgAdmin server) on demand.
> This will reduce memory consumption and improve the performance.
>
> There are some downsides, too,
>
> 1. The *Server Cursor* does not return the Total number of rows.
> - In this case, we will have a problem with pagination. We can either just
> show the *next page* button in pagination and hide the Last page, as we
> will not know the exact pages, so on clicking on the next button, we will
> show the result if it exists, OR we can use infinite scrolling for the
> Server Cursor.
>

Yes, that is a pretty big problem - not just for pagination, but for simply
seeing how many rows your query returned - something I and I suspect many
others do regularly.

Another issue for some might be that it will change query timings such that
they may no longer reflect what might happen in an application. This was a
huge topic of debate when we discussed making this same change in pgAdmin
III, probably 20 or more years ago!

>
> 2. The *Server Cursor* is less efficient for the small query results as
> it takes more commands to receive the results.
> - We can add one option in the query tool to run the query with either the *Server
> or Client cursor*.
>

That might be our solution in general - have a per-query-tool-instance
option to specify client or server cursor. If you choose server, you lose
the row count, but get the performance. If you choose client, you get the
current behaviour.

--
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com