diff.patch

From 713a79d054ba98576faf90073dfac3d7720bddb1 Mon Sep 17 00:00:00 2001
From: Induja Sreekanthan <indujasreekanth@gmail.com>
Date: Mon, 23 Feb 2026 14:21:16 -0500
Subject: [PATCH] Limit the max_pinned_buffers in ReadStream to prevent
 look-ahead exhausting the local buffers

The ReadStream look-ahead logic can pin up to GetLocalPinLimit()=num_temp_buffers local buffers if the effective_io_concurrency is 64 or higher. This can end up pinning all the local buffers, and the backend being unable to find any local buffers available outside the ReadStream. An example where this is seen is when the temporary table has TOAST data, and the backend needs to access the toast index and the toast page between the sequential scan. Another scenario to keep in mind is when there are multiple SeqScan nodes in the same query plan and run by the same backend.

This patch fixes this with the following fixes:
- Limit the maximum possible number of pinned local buffers in ReadStream to use only 75% of the total available local buffers (GetLocalPinLimit()).
- Limit the max_ios for temp tables to DEFAULT_EFFECTIVE_IO_CONCURRENCY=16.
---
 src/backend/storage/aio/read_stream.c | 31 ++++++++++++++++++++++++++-
 1 file changed, 30 insertions(+), 1 deletion(-)

diff --git a/src/backend/storage/aio/read_stream.c b/src/backend/storage/aio/read_stream.c
index 88717c2ff8f..7f92a903130 100644
--- a/src/backend/storage/aio/read_stream.c
+++ b/src/backend/storage/aio/read_stream.c
@@ -80,6 +80,20 @@
 #include "utils/rel.h"
 #include "utils/spccache.h"
 
+/*
+ * This indicates the upper limit on the percentage of the local buffers can be
+ * used by the read-stream's look-ahead logic.
+ */
+#define LOCAL_BUFFERS_USE_FOR_READ_STREAM_LIMIT 75
+/*
+ * This indicates the maximum number of IOs that can be outstanding for a
+ * read-stream for temporary tables. This needs to be limited to avoid running
+ * out of local buffers during look-ahead. Think multiple SeqScan nodes on temp
+ * tables in the same query plan executed by the same backend, resulting in
+ * multiple read streams.
+ */
+#define READ_STREAM_TEMP_TABLES_MAX_IOS DEFAULT_EFFECTIVE_IO_CONCURRENCY
+
 typedef struct InProgressIO
 {
 	int16		buffer_index;
@@ -576,6 +590,14 @@ read_stream_begin_impl(int flags,
 		max_ios = get_tablespace_maintenance_io_concurrency(tablespace_id);
 	else
 		max_ios = get_tablespace_io_concurrency(tablespace_id);
+	if (SmgrIsTemp(smgr))
+	{
+		/*
+		 * For temp relations, use a smaller value to avoid using up all the local
+		 * buffers for look-ahead.
+		 */
+		max_ios = Min(max_ios, READ_STREAM_TEMP_TABLES_MAX_IOS);
+	}
 
 	/* Cap to INT16_MAX to avoid overflowing below */
 	max_ios = Min(max_ios, PG_INT16_MAX);
@@ -616,7 +638,14 @@ read_stream_begin_impl(int flags,
 	 * we'll check that later in read_stream_start_pending_read().
 	 */
 	if (SmgrIsTemp(smgr))
-		max_possible_buffer_limit = GetLocalPinLimit();
+	{
+		/*
+		 * Don't use up the entire local buffer pool. Leave some buffers so that the
+		 * backend can do other work as well.
+		 */
+		max_possible_buffer_limit =
+			GetLocalPinLimit() * LOCAL_BUFFERS_USE_FOR_READ_STREAM_LIMIT / 100;
+	}
 	else
 		max_possible_buffer_limit = GetPinLimit();
 	max_pinned_buffers = Min(max_pinned_buffers, max_possible_buffer_limit);
-- 
2.53.0.414.gf7e9f6c205-goog