public inbox for [email protected]help / color / mirror / Atom feed
pgAdmin Async Server Cursor 7+ messages / 5 participants [nested] [flat]
* pgAdmin Async Server Cursor @ 2025-01-13 09:25 Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Khushboo Vashi @ 2025-01-13 09:25 UTC (permalink / raw) To: pgadmin-hackers 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgAdmin Async Server Cursor @ 2025-01-17 12:31 Dave Page <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Dave Page @ 2025-01-17 12:31 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers Hi On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi < [email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgAdmin Async Server Cursor @ 2025-05-12 05:35 Khushboo Vashi <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 3 replies; 7+ messages in thread From: Khushboo Vashi @ 2025-05-12 05:35 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers 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. [image: Screenshot 2025-05-06 at 4.54.56 PM.png] 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. [image: Screenshot 2025-05-06 at 4.54.06 PM.png] *2. View/Edit Data* 1. Context menu [image: Screenshot 2025-05-06 at 4.58.28 PM.png] 2. On the left side panel, top menu bar. (We can change the icon later.) [image: Screenshot 2025-05-12 at 10.45.48 AM.png] *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 protected]> wrote: > Hi > > On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi < > [email protected]> 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 > > Attachments: [image/png] Screenshot 2025-05-06 at 4.54.56 PM.png (60.9K, 3-Screenshot%202025-05-06%20at%204.54.56%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.54.06 PM.png (48.5K, 4-Screenshot%202025-05-06%20at%204.54.06%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.58.28 PM.png (198.0K, 5-Screenshot%202025-05-06%20at%204.58.28%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-12 at 10.45.48 AM.png (20.2K, 6-Screenshot%202025-05-12%20at%2010.45.48%E2%80%AFAM.png) download | view image ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgAdmin Async Server Cursor @ 2025-05-12 06:09 Aditya Toshniwal <[email protected]> parent: Khushboo Vashi <[email protected]> 2 siblings, 0 replies; 7+ messages in thread From: Aditya Toshniwal @ 2025-05-12 06:09 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: Dave Page <[email protected]>; pgadmin-hackers 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 protected]> wrote: > 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. > > > [image: Screenshot 2025-05-06 at 4.54.56 PM.png] > > 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. > > [image: Screenshot 2025-05-06 at 4.54.06 PM.png] > > > > > *2. View/Edit Data* > > > 1. Context menu > > [image: Screenshot 2025-05-06 at 4.58.28 PM.png] > > > 2. On the left side panel, top menu bar. (We can change the icon later.) > > [image: Screenshot 2025-05-12 at 10.45.48 AM.png] > > > > > > *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 protected]> wrote: > >> Hi >> >> On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi < >> [email protected]> 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 >> >> -- Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* <https://www.enterprisedb.com/; "Don't Complain about Heat, Plant a TREE" Attachments: [image/png] Screenshot 2025-05-06 at 4.54.56 PM.png (60.9K, 3-Screenshot%202025-05-06%20at%204.54.56%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.54.06 PM.png (48.5K, 4-Screenshot%202025-05-06%20at%204.54.06%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.58.28 PM.png (198.0K, 5-Screenshot%202025-05-06%20at%204.58.28%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-12 at 10.45.48 AM.png (20.2K, 6-Screenshot%202025-05-12%20at%2010.45.48%E2%80%AFAM.png) download | view image ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgAdmin Async Server Cursor @ 2025-05-12 06:10 Murtuza Zabuawala <[email protected]> parent: Khushboo Vashi <[email protected]> 2 siblings, 0 replies; 7+ messages in thread From: Murtuza Zabuawala @ 2025-05-12 06:10 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: Dave Page <[email protected]>; pgadmin-hackers On Mon, May 12, 2025 at 11:05 AM Khushboo Vashi < [email protected]> wrote: > 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. > > > [image: Screenshot 2025-05-06 at 4.54.56 PM.png] > > 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. > > [image: Screenshot 2025-05-06 at 4.54.06 PM.png] > > > > > *2. View/Edit Data* > > > 1. Context menu > > [image: Screenshot 2025-05-06 at 4.58.28 PM.png] > > > 2. On the left side panel, top menu bar. (We can change the icon later.) > > [image: Screenshot 2025-05-12 at 10.45.48 AM.png] > > > > > > *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. > I would suggest providing a toggle button (may be on top right corner) of each component. > > > > Thanks, > Khushboo > > > > > > > > > > > > > > > > On Fri, Jan 17, 2025 at 6:01 PM Dave Page <[email protected]> wrote: > >> Hi >> >> On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi < >> [email protected]> 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 >> >> Attachments: [image/png] Screenshot 2025-05-06 at 4.54.56 PM.png (60.9K, 3-Screenshot%202025-05-06%20at%204.54.56%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.54.06 PM.png (48.5K, 4-Screenshot%202025-05-06%20at%204.54.06%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.58.28 PM.png (198.0K, 5-Screenshot%202025-05-06%20at%204.58.28%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-12 at 10.45.48 AM.png (20.2K, 6-Screenshot%202025-05-12%20at%2010.45.48%E2%80%AFAM.png) download | view image ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgAdmin Async Server Cursor @ 2025-05-12 06:19 Akshay Joshi <[email protected]> parent: Khushboo Vashi <[email protected]> 2 siblings, 1 reply; 7+ messages in thread From: Akshay Joshi @ 2025-05-12 06:19 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: Dave Page <[email protected]>; pgadmin-hackers 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. On Mon, May 12, 2025 at 11:05 AM Khushboo Vashi < [email protected]> wrote: > 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. > > > [image: Screenshot 2025-05-06 at 4.54.56 PM.png] > > 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. > > [image: Screenshot 2025-05-06 at 4.54.06 PM.png] > > > > > *2. View/Edit Data* > > > 1. Context menu > > [image: Screenshot 2025-05-06 at 4.58.28 PM.png] > > > 2. On the left side panel, top menu bar. (We can change the icon later.) > > [image: Screenshot 2025-05-12 at 10.45.48 AM.png] > > > > > > *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 protected]> wrote: > >> Hi >> >> On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi < >> [email protected]> 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 >> >> Attachments: [image/png] Screenshot 2025-05-06 at 4.54.56 PM.png (60.9K, 3-Screenshot%202025-05-06%20at%204.54.56%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.54.06 PM.png (48.5K, 4-Screenshot%202025-05-06%20at%204.54.06%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.58.28 PM.png (198.0K, 5-Screenshot%202025-05-06%20at%204.58.28%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-12 at 10.45.48 AM.png (20.2K, 6-Screenshot%202025-05-12%20at%2010.45.48%E2%80%AFAM.png) download | view image ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgAdmin Async Server Cursor @ 2025-05-13 10:51 Dave Page <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Dave Page @ 2025-05-13 10:51 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: Khushboo Vashi <[email protected]>; pgadmin-hackers Hi On Mon, 12 May 2025 at 02:19, Akshay Joshi <[email protected]> wrote: > 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. > 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. > > On Mon, May 12, 2025 at 11:05 AM Khushboo Vashi < > [email protected]> wrote: > >> 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. >> >> >> [image: Screenshot 2025-05-06 at 4.54.56 PM.png] >> >> 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. >> >> [image: Screenshot 2025-05-06 at 4.54.06 PM.png] >> >> >> >> >> *2. View/Edit Data* >> >> >> 1. Context menu >> >> [image: Screenshot 2025-05-06 at 4.58.28 PM.png] >> >> >> 2. On the left side panel, top menu bar. (We can change the icon later.) >> >> [image: Screenshot 2025-05-12 at 10.45.48 AM.png] >> >> >> >> >> >> *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 protected]> wrote: >> >>> Hi >>> >>> On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi < >>> [email protected]> 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 >>> >>> -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com Attachments: [image/png] Screenshot 2025-05-06 at 4.54.56 PM.png (60.9K, 3-Screenshot%202025-05-06%20at%204.54.56%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.54.06 PM.png (48.5K, 4-Screenshot%202025-05-06%20at%204.54.06%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-06 at 4.58.28 PM.png (198.0K, 5-Screenshot%202025-05-06%20at%204.58.28%E2%80%AFPM.png) download | view image [image/png] Screenshot 2025-05-12 at 10.45.48 AM.png (20.2K, 6-Screenshot%202025-05-12%20at%2010.45.48%E2%80%AFAM.png) download | view image ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-05-13 10:51 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-13 09:25 pgAdmin Async Server Cursor Khushboo Vashi <[email protected]> 2025-01-17 12:31 ` Dave Page <[email protected]> 2025-05-12 05:35 ` Khushboo Vashi <[email protected]> 2025-05-12 06:09 ` Aditya Toshniwal <[email protected]> 2025-05-12 06:10 ` Murtuza Zabuawala <[email protected]> 2025-05-12 06:19 ` Akshay Joshi <[email protected]> 2025-05-13 10:51 ` Dave Page <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox