Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tYlVY-00F3e9-W1 for pgadmin-hackers@arkaria.postgresql.org; Fri, 17 Jan 2025 12:31:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tYlVX-00FA0X-BJ for pgadmin-hackers@arkaria.postgresql.org; Fri, 17 Jan 2025 12:31:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tYlVX-00FA0P-0a for pgadmin-hackers@lists.postgresql.org; Fri, 17 Jan 2025 12:31:27 +0000 Received: from mail-lj1-x22e.google.com ([2a00:1450:4864:20::22e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tYlVU-0001MK-1g for pgadmin-hackers@postgresql.org; Fri, 17 Jan 2025 12:31:26 +0000 Received: by mail-lj1-x22e.google.com with SMTP id 38308e7fff4ca-3022484d4e4so19760881fa.1 for ; Fri, 17 Jan 2025 04:31:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1737117083; x=1737721883; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=TV7cg8SkbIhY9afADD0LgFykUZtNj4Fn/MkoleksWdw=; b=K/k3ET69SWPl3Z269tc4AZC24bTuJagqjYKYpDUH1E860JD8NnqPjRYpSXclIIYvQ5 ChYvRxqXRjFHGRHJuz8bZNE2ebKtOATsAKKCM+qim6TLVb2+GSgQJLVU0Iv65GwZFC86 B8XHEX0aKdoMchZioe6qMy4CgOxOtJe7l5ayURQT4c3skJhx6sN3wfTR/Ssct8SSpJjr iRRf0AdALevf6sPY8cm0N93DWdSwEjzE6GrUiAeICM/izS1eZI73ewveBhw/tsQhIRqP tv1SQIYFocFf8TNLnvKAshoaznL7n+t9Qx1W1IJBFHpo8vkl6JBXlb5d1+9LYQ2p7K3E 6W7Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737117083; x=1737721883; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=TV7cg8SkbIhY9afADD0LgFykUZtNj4Fn/MkoleksWdw=; b=BbucvbPx3qGnk30KWPHAbJAqJJ5AeIzIMwSyx2wCB3uo0IkRYuVbDMQNFQfzIEUkjy 4XW9Y06Xx2d3cWQTVCbhaTh94tD3fH6M9bPObPZqtMWZPY/qPxfPVEzE8iL54BC2/w9W piKoHQOhV2cxAZppmOvWR/a9p+YW1idjKJ8cstLeqWBDNG8oiqw1q55a0/NApHgJLGc8 mpIHs1GNsJA62RGUdtYDZOojCZSl/rjGBOfD1hIGF6XhuafSGgqEY6Q1eXp+9MJNfRfx nCJRhwd9BU9ZdlzPhW6XhLzaVKjEauPYxBvH1wQBx9LrPv1n2m4o+Sd4mHJ1XO/Jn83Z DVXg== X-Gm-Message-State: AOJu0YxmgzzFTf/Y7InArvH6RpZcuiCxaH1V0MNPfcOwGS7K8AEGcGZa wX3pjTtJzQSh5vpQlM7+d1R84TaXEW85RUXwq869eWLkRAGzl+Y2mUOMa+3fG57Yy5elFBjH0rx ZesoyfVlL2BpeDR/IZGOAi+dEkQx8KsWD0Vy1UjDWE7/QLuCmdA== X-Gm-Gg: ASbGnctk/yqaoAozEAbgpOTD8ZzOWzozb/L8Y0/i4ukM87mlKjY3HgjqVKBwhkv1twT MD9zp8+xTDIqwwzMH10FPnfb3m6md18iOspCOTA== X-Google-Smtp-Source: AGHT+IFqgfbsRpg5K8kmRiqG+VxsSPCKJOYHRMpIkKr3QEDOaIXmFc2bSuJLAcGy2Ky0CdFiUySOmZfD4VUXBXGbMWY= X-Received: by 2002:a05:651c:546:b0:304:4e03:f9d7 with SMTP id 38308e7fff4ca-3072cb0ef67mr8527151fa.30.1737117082815; Fri, 17 Jan 2025 04:31:22 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Fri, 17 Jan 2025 12:31:12 +0000 X-Gm-Features: AbW1kvb5iEBjLFdbX3VqXR2A6AFR7ef72ZItmD5ZlrkfxTUtIKE5O9Pyg4mjFGI Message-ID: Subject: Re: pgAdmin Async Server Cursor To: Khushboo Vashi Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000a7a8cd062be6162b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a7a8cd062be6162b Content-Type: text/plain; charset="UTF-8" Hi On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi < khushboo.vashi@enterprisedb.com> wrote: > Hackers, > > Regarding #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 --000000000000a7a8cd062be6162b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Mon, 13 Jan 20= 25 at 09:25, Khushboo Vashi <khushboo.vashi@enterprisedb.com> wrote:
Hackers,

Regarding #5797:=C2=A0Full query result is bein= g loaded into RAM despite ON_DEMAND_RECORD_COUNT=3D1000

pgAdmin uses the = Async cursor to fetch the data in the query tool, which basically fe= tches and stores the entire result on the client side, in our case, the pgA= dmin server.
So, if the query result is very large, it uses more memory on the pgA= dmin server. We use the=C2=A0ON_DEMAND_RECORD_COUNT to fetch the partial data = from the cursor (which is already transferred to the pgAdmin server) and sh= ow 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 d= emand. This will reduce memory consumption and improve the performance.=C2= =A0
There= are some downsides, too,

1. The Server Cursor does not return the Total numb= er 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 th= e Server Cursor.

Yes, th= at 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 mig= ht happen in an application. This was a huge topic of debate when we discus= sed making this same change in pgAdmin III, probably 20 or more years ago!<= /div>
=C2=A0

2. The Server Cursor is less effi= cient for the small query results as it takes more commands to receive the = results.
--
--000000000000a7a8cd062be6162b--