>Have you got numbers showing any actual performance win for postgres_fdw?
For JDBC purposes, it would be nice to have an ability of asking backend "to stop fetching if produced more than X MiB of response data". For small table (4 int4 fields), having decent fetchSize (~1000) makes result processing 7 times faster than with fetchSize of 50 rows (14 ms -> 2 ms for 2000 rows). Here are the measurements: [1] and [2].
Note: it is not required to precisely follow given "max fetch bytes" limit. It would be enough just to stop after certain amount of data was sent. The whole thing of using limited fetch size is to avoid running out of memory at client side. I do not think developers care how many rows is fetched at once. It they do, they should rather use "limit X" SQL syntax.
Do you have a suggestion for such a use case?
For fixed-size data types, JDBC driver can estimate "max sane fetch size", however: 1) In order to know data types, a roundtrip is required. This means the first fetch must be conservative, thus small queries would be penalized. 2) For variable length types there is no way to estimate "sane number of rows", except of using "average row size of already received data". This is not reliable, especially if the first rows have nulls, and subsequent ones contain non-empty strings.
I believe that Kyotaro proposed something like that, wherein the FDW would be more adaptive based on the amount of memory available, and fetch a number of rows that, by its estimation, would fit in the memory available. I don't know the progress of that patch.
This patch is a far less complicated solution and puts the burden on the DBA to figure out approximately how many rows would fit in memory based on the average row size, and set the per-table option accordingly. If it is later determined that the rows are now too heavy to fit into the space allotted, the fetch size can be altered for that table as needed.
