On Wed, Oct 14, 2026 at 12:22 PM Corey Huinker <[email protected]> wrote: > My interest in the DEBUG messages was giving a curious user an answer to the > question "If I'm not getting the batch_size I asked for, what batch_size am I > actually getting?", but that should infer-able from logs looking for "FETCH > <number> FROM cursor_name", albeit with some additional effort to line up > which FETCH commands correspond to which FDW queries, and cross referencing > that against the fetch_size that they've set, so it may be that a curious > user already has the instrumentation they need.
Unless I'm confused, that's fetch_size, and this patch is about batch_size. So, you're doing a bulk-insert to a foreign table, and the question is about how many separate INSERT statements we're going to have to issue in order to handle all the rows. If the concern is about the difficulty of correlating local and remote log lines, we could give postgres_fdw an option that causes the commands it ships to the remote side to be logged locally at a level of the user's choosing, and that could also involve logging fetch sizes. I am not sure whether that would be valuable enough to justify its carrying costs, but I can see the argument for it. You could watch it issue transaction control commands and stuff, too, which could be helpful for debugging. But all of that is a separate project from this. > I agree that's the best course of action once a user knows that they have a > batch_size problem, but I don't think the user affected by this situation > knows that specific detail just yet. They probably know that their FDW > queries have slow and/or stair-step stuttering performance (i.e. why does a > result set of 1372 rows take so much longer than one with 1371? What's > special about that number?), and they might also know that they made some > configuration changes to cut down on network chatter for very large result > sets, but the root cause still eludes them. When in the discovery phase of > problem, it is reasonable to crank up the logging detail and see what's in > there. But that same increase in logging detail will surface the FETCH x FROM > y queries as well Do you think this is a real effect? I'm guessing that you're supposing that the fetch size is 1372 here, so that with 1371 rows we fetch all the rows in one go and see that there are no more and with 1372 rows we have to issue a second fetch. But it's probably not the second fetch that is the dominant cost here. In my mind, when you're sad about the performance in these kinds of scenarios, you're more likely to be sad that the plan had to use a cursor at all, because that may for example have precluded the use of parallel query. Whether you have to do one fetch or two probably matters relatively little by comparison. That's not to say that the extra fetch is free. It isn't, and you might be able to see that in some scenarios, like when the cost of generating each row is very low. But I suspect it's not the biggest of deals, either. In the INSERT scenario, which is what I think the patch is targeting, the issue would be one INSERT with 1372 rows vs. two inserts, one with 1371 rows and a second with 1 row. I don't know how much slower the second one is, but I bet it isn't much slower. The extra statement has some overhead, but so does inserting 1372 rows. You could test a case with a single-row, single-column table with no indexes, so that the extra network round trip is the biggest chunk of the cost possible, but that's a pretty artificial scenario. Honestly, there's room in my mind to question whether very large batch sizes are even a good idea. Up near 64k parameters, the generated INSERT INTO ... VALUES (...), (...), (...) ... statement will be enormous. It has not been my experience that PostgreSQL users routinely experience regret that they're only sending half a megabyte of SQL in one shot; indeed, most of the regrets I've seen in this area have come from query strings that are longer than they really ought to be. One more new discovery: apparently, you can already get the post-clamp batch size by just using EXPLAIN (ANALYZE, VERBOSE) -- or if that's too tricky in an IRL situation, you could set up auto_explain to do the same thing for you automatically and dump the results to the logs. In some ways, that's actually better than the DEBUG message this patch proposes to add, because it takes into account all the ways that postgresGetForeignModifyBatchSize can decide to adjust the numbers, instead of only the last case. Regrettably, it appears that without ANALYZE, you see the unclamped value, because postgresGetForeignModifyBatchSize doesn't have an fmstate and skips clamping the batch size for lack of knowledge. Potentially, that could be fixed by factoring the p_nums calculation in create_foreign_modify into a helper function that postgresGetForeignModifyBatchSize uses, or by a larger refactoring that creates a partial fmstate even in pure EXPLAIN mode, and just skips connection to the remote side. None of that seems essential, but it could be done. Also regrettably, EXPLAIN can only help with INSERT, not COPY FROM, so theoretically a DEBUG message to cover that case could still have value, but it's aiming at a very narrow target: the batch size is gigantic, you actually want to be sending huge SQL statements, you're using COPY FROM, and you don't have the ability to easily inspect the remote logs. > I see this as an argument for removing the DEBUG message but keeping the > WARNING in option.c - as it immediately tells the well user "the value you > specified cannot possibly achieve the thing you think it will do", > potentially eliminating a fruitless or at least confusing testing/debug > cycle, and possibly making them aware that there is an upper bound to > fetch_size, thus initiating the voyage of discovery as outlined by Robert > above, as well as providing a strong clue as to why the logs show a > fetch_size of 652 despite the number 652 not showing up in the config files > anywhere. Maybe, but keep in mind that (1) this is still about batch_size, not fetch_size and (2) the two warnings aren't the same. The postgres_fdw_validator WARNING is only going to fire if you set the batch size above 64k, whereas the DEBUG message fires if the batch size is more than 64k divided by the number of columns per row. They only have the same threshold for a 1-column table, which is probably not what you've got. Said differently, it's much more likely that you have a ten-column table and a batch size of 7000 than a one-column table and a batch size of 70000. -- Robert Haas Databricks
