Subject: pgAdmin Async Server Cursor

From: Khushboo Vashi <[email-masked]>
Date: Mon, 13 Jan 2025 14:55:22 +0530

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.

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*.

Thanks,
Khushboo

---

From: Dave Page <[email-masked]>
Date: Fri, 17 Jan 2025 12:31:12 +0000

Hi

On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi <
[email-masked]> wrote:

> 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.

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

---

From: Khushboo Vashi <[email-masked]>
Date: Mon, 12 May 2025 11:05:02 +0530

Hello Dave/hackers,

I'd like to update you on this issue. As we discussed in this thread, I
have implemented the server cursor. The psycopg implementation is also
done; the only part left is how we are going to show it on the UI.

I have identified 3 to 4 places where we need to make a UI decision.

*Menu/Button options*

*1. Query Tool*
There are two options for the query tool.

1. Show as a menu item along with the Execute buttons panel, as shown below.

2. Give a separate button in the Execute buttons panel, as shown below.
Note: I copied and pasted the same icon; we can change this icon once we
decide to go with this approach.

*2. View/Edit Data*

1. Context menu

2. On the left side panel, top menu bar. (We can change the icon later.)

*3. Save the Result to the File*
We can choose a different button option or a dropdown menu item like the
above approaches.

*Other than Menu/button option*
Another option is to provide the* Preferences option* to use the server
cursor instead of the client cursor.
The drawback of this approach is that the user needs to select/deselect
that option from the Preferences.
And it would be inconvenient if those settings need to be changed
frequently.

Thanks,
Khushboo

On Fri, Jan 17, 2025 at 6:01 PM Dave Page <[email-masked]> wrote:

>> - 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!
>
>
>>
>> the *Server or Client cursor*.
>>
>

---

From: Aditya Toshniwal <[email-masked]>
Date: Mon, 12 May 2025 11:39:30 +0530

Hi Khushboo,

I would suggest not adding new menus. We can add a preference whether to
use the server cursor or not. And based on that, the query tool/view data
will behave.
Users anyway have a choice to disable it even after opening it.

On Mon, May 12, 2025 at 11:05 AM Khushboo Vashi <
[email-masked]> wrote:

>>> 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.
>>>

-- 
Thanks,
Aditya Toshniwal
pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com*
<https://www.enterprisedb.com/>
"Don't Complain about Heat, Plant a TREE"

---

From: Murtuza Zabuawala <[email-masked]>
Date: Mon, 12 May 2025 11:40:13 +0530

> And it would be inconvenient if those settings need to be changed

I would suggest providing a toggle button (may be on top right corner) of
each component.

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 12 May 2025 11:49:26 +0530

Hi Khushboo,

I feel that having duplicate buttons for the same action, specifically for
using the server cursor, can be a bit redundant. Instead, I suggest
introducing a preference setting like *"Use server cursor for more than N
rows"*, with a default value of, say, 100,000 rows. Users could then easily
adjust this threshold based on their needs.

OR, we could offer two preferences:

   1.

   *"Use server cursor?"* (a simple toggle), and
   2.

   *"Use server cursor for more than N rows?"* (to fine-tune behavior).

This approach would keep the UI cleaner and still give users the
flexibility they need.

---

From: Dave Page <[email-masked]>
Date: Tue, 13 May 2025 06:51:04 -0400

Hi

On Mon, 12 May 2025 at 02:19, Akshay Joshi <[email-masked]>
wrote:

> rows"*, with a default value of, say, 100,000 rows. Users could then
> easily adjust this threshold based on their needs.
>
I agree that we should not add duplicate buttons. Like others, I would
suggest a simple option in Preferences, that is also reflected on the menu
with the commit options, as per your screenshot.

I do not think we should have a "Use server cursor for more than N rows?"
option... how will we know how many rows there are going to be without
running a potentially expensive query that might have other undesirable or
even dangerous side effects if executed twice.

>>>> 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.
>>>>
>>>> 2. The *Server Cursor* is less efficient for the small query results
>>>> as it takes more commands to receive the results.