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 1tXGhX-005Ay5-BY for pgadmin-hackers@arkaria.postgresql.org; Mon, 13 Jan 2025 09:25:39 +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 1tXGhW-009O9R-3k for pgadmin-hackers@arkaria.postgresql.org; Mon, 13 Jan 2025 09:25:38 +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 1tXGhV-009O9I-OZ for pgadmin-hackers@lists.postgresql.org; Mon, 13 Jan 2025 09:25:38 +0000 Received: from mail-yb1-xb2a.google.com ([2607:f8b0:4864:20::b2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tXGhT-0009TM-2M for pgadmin-hackers@postgresql.org; Mon, 13 Jan 2025 09:25:36 +0000 Received: by mail-yb1-xb2a.google.com with SMTP id 3f1490d57ef6-e545c1e8a15so6063069276.1 for ; Mon, 13 Jan 2025 01:25:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1736760334; x=1737365134; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Ur5+oD4Qgc+YUtZMl/f0MlCFEi71PUGZg7d8ULNeJ0U=; b=aG2A+s6KpinchALxeudQpiSupDPsCeBaW5b8Mo4uRys+Ba4lSAzCy02MRYDgP60hd5 D9hmE5o52bTp7YqQgUlpmgNKBGCzWuy2bxX54hIv64hUk+uUJj2j7zBJa58iQCd14lEG NgXCfWerWfOQOEkXVp1jU9sbpqyYpU5GCe0z+JniOnyCBNZTS6kJPZcnW430/5IJovwr AgcrONffivJxWGuZWdwplQtPbKbNqNqnNOOwcdQYzZuCLsGAv8apHsnzFHrNkML8hiwv sJPnEWAVh9QhRKAjrQH7HsQNtlotRv3JauQ/3XcRof1/aI755gTSFkg/33JmUI5sRQbz pR6w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736760334; x=1737365134; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Ur5+oD4Qgc+YUtZMl/f0MlCFEi71PUGZg7d8ULNeJ0U=; b=EENX+HwCSwyguOvdQlP6MGyZl1xY0VdlqUSmVNihOIsXsHnQOAMXVNmwBEluy4yQYk ilSq/AfsVCdLamnNEOU8nUO9v5FTo7E8xSfHM4PaTgwSQI3rMvpbEcr35EJfWGRJoihR mF9lici4Qkdni5im+yk/i+3E8Q7lAbm2/T66kX6cyM6grRncVzCBEP/9jkr/0lH3JXFp 43LCqFJay1e5ikj08P3vwn6UHWLarjVXhLqAxIisf7E8btKgdPVGxb3UgNm+QyTJpKka 1vsM5d14OLE9gDOM3n4sX98AEVONCbUDl+wqnx0YTSjwJkhx3F69isZiPZlxzU3bnJUq Vxkg== X-Gm-Message-State: AOJu0YxbBiE/nIkPWIPpsy0YN/uNqLY833JvWXlZvkh5egzjPeLw8TFG wqWtuuNWrLiSKW6zospJkkd+eTO+l/5NsiT91WiVYiaiCBQ0jZOOKqt3K+eRR6AsgjTJFin5bFX CGgezWTf+LxxUMf4TFkVg2U/baCgsYMS5IbmOqm/B7hZyT9LyEQ== X-Gm-Gg: ASbGncsch0qjiGOOXbHgqgHNSw4LvnkeBTIdNIPS5/zrr2GlSyK4TPkJchV3H27aQBn GSEAlVu07wpKUf2YTl2XdnW3y+T9gwxfrB0hsMoc= X-Google-Smtp-Source: AGHT+IGsUc+NoBeXiYwcrJYn4JsVNO337vss69P73H3acFVbZF5aI8ciy40/QrRPpXyUa2WjH2t5I0EOfla0PkR8GVk= X-Received: by 2002:a05:6902:91c:b0:e57:406e:9296 with SMTP id 3f1490d57ef6-e57406e9449mr3836837276.17.1736760333638; Mon, 13 Jan 2025 01:25:33 -0800 (PST) MIME-Version: 1.0 From: Khushboo Vashi Date: Mon, 13 Jan 2025 14:55:22 +0530 X-Gm-Features: AbW1kvaQngOnV_t0WKfp5D1rAV2bfW80lb_mGSqORKQq1MEyLcAm8N2Y_n4slbU Message-ID: Subject: pgAdmin Async Server Cursor To: pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000bf3c53062b930638" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bf3c53062b930638 Content-Type: text/plain; charset="UTF-8" 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. 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 --000000000000bf3c53062b930638 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hackers,

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

pgAdmin uses the Async curs= or to fetch the data in the query tool, which basically fetches and sto= res the entire result on the client side, in our case, the pgAdmin server.<= /font>
So, if = the query result is very large, it uses more memory on the pgAdmin server. = We use the=C2=A0ON_DEMAND_RECORD_COUNT to fetch the partial data from the curs= or (which is already transferred to the pgAdmin server) and show it on the = UI.
To ov= ercome this, we can use the Async Server Cursor, which transfers dat= a from the Postgres Server to the client (pgAdmin server) on demand. This w= ill reduce memory consumption and improve the performance.=C2=A0

=
There are some down= sides, 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 no= t 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 Curso= r.

=
2. The= Server Cursor is less efficient for the small query results as it t= akes more commands to receive the results.
- We can add one option in the query to= ol to run the query with either the Server or Client cursor.<= /div>

Thanks,
Khushboo=






--000000000000bf3c53062b930638--