Subject: [pgjdbc/pgjdbc] issue #3483: Support adaptive fetching without enforcing memory limits, and/or have a separate buffer size for it

From: "james-johnston-thumbtack (@james-johnston-thumbtack)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:08:04 +0000

The `maxResultBuffer` configuration parameter effectively performs two roles:
- **Hard memory buffer limit**: Enforce a hard maximum on the result set memory buffer: if the server returns more rows than can fit in the buffer, then an exception is thrown as implemented at https://github.com/pgjdbc/pgjdbc/blob/032d0e225a91e866d7dae680ebb784507392e803/pgjdbc/src/main/java/org/postgresql/core/PGStream.java#L808-L825
- **Adaptive caching memory target**: Act as a target for the adaptive fetching to try to aim for - it is used in the calculation of the next fetch size by dividing buffer limit by max row size seen so far, as implemented at https://github.com/pgjdbc/pgjdbc/blob/032d0e225a91e866d7dae680ebb784507392e803/pgjdbc/src/main/java/org/postgresql/core/v3/adaptivefetch/AdaptiveFetchCache.java#L64-L90

Unfortunately, I don't see a way to decouple these behaviors.  Thus, there is a possible failure mode: suppose the first few batches of rows have small row sizes.  But then another batch arrives with larger rows such that _the average row size is larger than the maximum row size seen so far_.  In this case, the new batch won't fit in the buffer, and the exception would be thrown.  A hypothetical example data set of where this might fail is if there is a JSON column, rows are sorted by creation time, older rows have empty JSON, but then the application starts filling out a fat JSON blob in every newer row.  Due to this, I'm a little reluctant to turn it on in production (or invest significant time testing), because I know we have some data sets like this and I'm not optimistic.

If these behaviors could be independently configured and decoupled, I think it would be quite useful.  We can imagine there could be a number of different possibilities when decoupled:

| Hard memory buffer limit | Adaptive fetching memory target | Comment |
|-|-|-|
| Disabled | Disabled | Default behavior of driver when nothing configured (fixed fetch size and no buffer limit) |
| Disabled | Enabled | Adaptive fetching tries to hit a memory target, but a temporary excursion won't encounter any hard memory limit enforced by the driver.  No way to configure this today. |
| Enabled | Disabled | Fixed fetch size (no adaptive fetching), and a hard buffer limit enforced resulting in exception |
| Enabled | Enabled, and equal to hard memory buffer limit | Adaptive fetching, and hard memory limit enforced by driver: this is the status quo and only way to enable adaptive fetching today, which is more vulnerable to the above-described problem |
| Enabled | Enabled, and smaller than hard memory buffer limit | Adaptive fetching is enabled, and tries to hit a buffer size target smaller than the enforced limit.  For example, I could configure a 100 MB hard limit, and a 60 MB adaptive fetching target.  If the average row size in a fetch exceeds the maximum row size so far, there's still a 40 MB margin of safety before exceptions would be thrown. |

The final possibility in this list I think is the most interesting, but maybe other users would like other options in this table to tune and customize this buffer and fetching behavior.  I could see the option of enabling adaptive fetching and disabling the hard memory limit to also be of interest.

(I do know of `adaptiveFetchMaximum`, but that value is not... adaptive... the appropriate max value to avoid encountering the buffer limit is hard to generically set in advance if the row size is not known.)

(One other final, somewhat unrelated thought.... I have wondered why the ResultSet buffering is really necessary in the first place... couldn't the `ResultSet.next` function stream the rows directly from the TCP socket without significant buffering?  This would avoid the whole issue of buffer size management in the first place.)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:21:40 +0000

> (One other final, somewhat unrelated thought.... I have wondered why the ResultSet buffering is really necessary in the first place... couldn't the `ResultSet.next` function stream the rows directl…

This would be the best solution. Have a look at https://github.com/pgjdbc/pgjdbc/pull/1735 see if it is reviveable

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:36:31 +0000

>>couldn't the ResultSet.next function stream the rows directly from the TCP socket without significant buffering? This would avoid the whole issue of buffer size management in the first place.)

Imagine we don't fully consume the data from the socket.
Imagine user executes yet another "prepare statement / execute call".
We can't multiplex over the single connection, so we would have to consume and buffer the leftover in that case.

>buffer size management in the first place

The core there is to reduce the number of network roundtrips (==avoid `fetch 1` for `.next()`), and we don't want run into "fetch everything in a single go" scenario as well as it might degrade to "buffer everything" in cases like "running extra query while processing the resultset"

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:39:55 +0000

> Imagine we don't fully consume the data from the socket. Imagine user executes yet another "prepare statement / execute call". We can't multiplex over the single connection, so we would have to consume and buffer the leftover in that case.
> 
Interesting problem. One would have to at least consume everything before issuing the next query.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 17 Jan 2025 09:54:54 +0000

> but then the application starts filling out a fat JSON blob in every newer row

Even a single json can exceed the limits, so I am not sure we can do much from the client side to make it 100% safe.
I would be nice if the backend could support "fetch at most N rows and stop the fetch after M bytes sent".

I wonder if it can come as a connection-level property like `suspend_portals_after_sending=50MiB`, so it would automatically suspend portals as it produces the given number of bytes.

Then it would reduce the chances for "out of memory" in case the application uses row-limited fetch. For instance, currently we issue `fetch N` request, and we don't care how many rows it produces. We wait for `PortalSuspended` vs `CommandComplete` messages to tell if there's anything left.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 10:43:52 +0000

> I would be nice if the backend could support "fetch at most N rows and stop the fetch after M bytes sent".

We should add this to protocol nice to have list

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 17 Jan 2025 10:45:57 +0000

> We should add this to protocol nice to have list

As to me, it does not look like a protocol change though. It looks like a more-or-less safe change that should be compatible with the existing clients.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 17 Jan 2025 11:00:03 +0000

well we need a way to tell the backend this, which I presume is a protocol change

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 17 Jan 2025 11:11:12 +0000

> well we need a way to tell the backend this, which I presume is a protocol change

I guess it could be a GUC, so it does not require a protocol change

---

From: "james-johnston-thumbtack (@james-johnston-thumbtack)" <[email-masked]>
Date: Fri, 17 Jan 2025 19:01:51 +0000

> Even a single json can exceed the limits, so I am not sure we can do much from the client side to make it 100% safe.

Indeed - a single very large JSON could even exceed all physical memory/disk on the client machine.  Not much you can do about that.

But, if the buffer/adaptive fetching configuration was more flexible, then the user would have more control to make it _safer_ (even though it cannot be theoretically proved to be 100% safe).  I could at least tune the adaptive fetching to give some extra safety margin so that the adaptive fetching doesn't so aggressively try to come so close to hard memory limits (whether imposed by pgJDBC in the hard buffer limit discussed in this issue - last row in my table, or imposed by the JVM itself if pgJDBC's buffer limit was disabled - second row in my table).

> I would be nice if the backend could support "fetch at most N rows and stop the fetch after M bytes sent".

I think this would also be a fantastic idea and a good improvement to PostgreSQL, although like Dave, I would imagine it would require changes to the server so you can tell it when to stop sending data.

I would add that one would also need to think about what to do if a single row size exceeds `M`.  In order to make progress, we would need to exceed `M` just for that one row (alternatively, the backend could return an error).  For example, suppose you have a 50 byte limit, and the rows to be retrieved are 20, 20, and 70 bytes.  You first fetch the first two rows for 40 bytes.  Then the second fetch gets the 70 byte row, which does exceed the configured 50 byte limit.  (Or, an error is returned by the server on the second fetch).  Realistically I would imagine the choice could be the user's, e.g. the server will exceed the limit for one row, but the client can still error out if a hard limit was set on the client with `maxResultBuffer`.  (It's kind of like in Kafka clients, where they will temporarily exceed the configured limit in order to make progress if there is a single large message - see [max.partition.fetch.bytes](https://kafka.apache.org/documentation/#consumerconfigs_max.partition.fetch.bytes) as an example in the consumer where they will exceed the fetch size for large messages.  Although do note they also still have a configured max message size, so there is still an upper bound.)

One would also consider that someone may want to only limit by byte size, and not by row count.  For example, maybe you pass `0` rows to [Execute](https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-EXECUTE) but then still request a byte size limit using the proposed mechanism - in this case, PG would fetch as many rows as possible that fit within the byte size limit.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Sun, 19 Jan 2025 09:08:51 +0000

Just in case, I am `+1` for adding `Hard memory buffer limit`. I'm not sure if we should set it to non-zero value by default, however, having a limit is a useful feature.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Mon, 20 Jan 2025 15:08:28 +0000

I've raised a question on the hackers list: https://www.postgresql.org/message-id/CAB%3DJe-HyOz00Ms6pMuOJoCig1eNDp1hpejMvqz%3Dg6M--hoxnKA%40mail.gmail.com

>One would also consider that someone may want to only limit by byte size, and not by row count. For example, maybe you pass 0 rows to [Execute](https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-EXECUTE) but then still request a byte size limit using the proposed mechanism - in this case, PG would fetch as many rows as possible that fit within the byte size limit.

I am afraid it might break the semantics: "execute 0" was supposed to fetch all the rows, and the application might assume extra fetches would not be required after "fetch all rows" execute. So I think it might be ok to keep "fetch all" intact.

---

From: "james-johnston-thumbtack (@james-johnston-thumbtack)" <[email-masked]>
Date: Tue, 21 Jan 2025 00:41:54 +0000

> I am afraid it might break the semantics: "execute 0" was supposed to fetch all the rows, and the application might assume extra fetches would not be required after "fetch all rows" execute. So I t…

The main point was just to have a way to specify something like "get me roughly 50 MB at a time" without worrying about row counts at all.  I suppose one can pass a row fetch size of `9999999999` or something like that if row fetch size of `0` were to ignore the requested/proposed byte limit.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 21 Jan 2025 15:17:44 +0000

So there's no reason we can't limit the number of bytes read on the client. We set a limit and when it exceeds the limit we just toss them away instead of storing them

Processing N bytes at a time is a more difficult problem. Unfortunately backpressure is not in the current protocol, however we could do the same thing by just returning what we have and stop reading until asked to do so. This however would require significant refactoring.

---

From: "james-johnston-thumbtack (@james-johnston-thumbtack)" <[email-masked]>
Date: Tue, 21 Jan 2025 20:01:15 +0000

> Processing N bytes at a time is a more difficult problem

Yeah, this is what I was referring to by "get me roughly 50 MB at a time".  I.e. imagine doing `setFetchSizeInBytes(50000000)` and that is a clear upper bound of what memory would be used.  The semantics of the `next()` function on the `ResultSet` otherwise works exactly the same.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 21 Jan 2025 20:04:50 +0000

> I.e. imagine doing setFetchSizeInBytes(50000000) and that is a clear upper bound of what memory would be used

It would require wire protocol update which is a very distant future

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 21 Jan 2025 20:13:36 +0000

> It would require wire protocol update which is a very distant future

Have some faith. I'm working on it :)

---

From: "Chris-SP365 (@Chris-SP365)" <[email-masked]>
Date: Mon, 27 Jan 2025 12:31:16 +0000

> We can't multiplex over the single connection, so we would have to consume and buffer the leftover in that case.

This is the way multiple other database vendors JDBC implementations do it.

SQL Server by default:
https://learn.microsoft.com/en-us/sql/connect/jdbc/using-adaptive-buffering?view=sql-server-ver16
"Avoid executing more than one statement on the same connection simultaneously. Executing another statement before processing the results of the previous statement may cause the unprocessed results to be buffered into the application memory."

MariaDB with setFetchSize(1)
https://mariadb.com/kb/en/about-mariadb-connector-j/#streaming-result-sets
If another query is run on same connection while the resultset has not been completly read, the connector will fetch all remaining rows before executing the query. This can lead to still needing lots of memory. Recommendation is then to use another connection for simultaneous operations.

MySQL with setFetchSize(Integer.MIN_VALUE) throws an exception
https://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html
"There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown. "

Result set streaming was our reason to switch to [PGJDBC-NG](https://impossibl.github.io/pgjdbc-ng/) which has the ability to stream the result set row by row. Our implementation does not issue a second command over one connection, it's just using a separate connection. So this is not a problem for us.

@james-johnston-thumbtack would that behaviour help you?

---

From: "james-johnston-thumbtack (@james-johnston-thumbtack)" <[email-masked]>
Date: Mon, 27 Jan 2025 20:26:20 +0000

In my situation, I'm an end-user using & tuning parameters for the existing open source [Debezium's PostgreSQL connector](https://debezium.io/documentation/reference/stable/connectors/postgresql.html), so changing JDBC drivers isn't practical.  One of the tricky things here is easily setting efficient parameters generally while not running out of memory / making good use of memory, when row sizes and row counts do vary widely from table to table.  Picking an appropriate buffer size measured in bytes, not rows, for the JDBC driver would make this much easier.  I don't think there's a useful need to multiplex multiple queries or fetches over one connection in this use case, and IMHO it's perfectly reasonable to read the rest of the fetch before being able to do something else on the connection.

---

From: "Chris-SP365 (@Chris-SP365)" <[email-masked]>
Date: Tue, 28 Jan 2025 06:34:32 +0000

Thank you for your explanation

> One of the tricky things here is easily setting efficient parameters generally while not running out of memory / making good use of memory

You can request and deliver the entire result If you stream it directly to the application. There is no need to calculate, set limits or use cursors.