Re: Limit memory usage by postgres_fdw batches

Alexander Pyhalov писал(а) 2026-01-23 09:18:
> Alexander Pyhalov писал(а) 2026-01-13 13:44:
>> For now I start thinking we need some form of FETCH, which stops
>> fetching data based on batch size...
>
> Hi.
>
> To limit memory consumption, we actually have to retreive less data.
> And we can do it only on the side of the foreign server. I've rewritten
> the third patch. We introduce a new parameter - cursor_fetch_limit,
> which is set by postgres_fdw. When it is set, fetching limited count of
> records from the cursor is also limited by memory consumed by the
> records. Of course, record size is some estimation (for example, we
> don't know what out function will do).
>
> This works as expected - in my tests with tables of large records,
> backends, executing selects, were always restricted by about 2 GB of
> RAM overall (without patch memory consumption easily grows up to 8 GB).
> However, now when we got less tuples from executor, than expected, we
> should recheck, if these are all tuples we can get. I've introduced
> es_eof EState field to signal that there's no more tuples. Don't know
> if it's the best way.

The issue which worried me is that we could change behavior for cursors,
which don't expect this (for example, if these cursors are used in
functions, which are executed via FDW). So I added new cursor option and
used it in postgres_fdw.

Also rearranged patches. While working with test cases I found that we
should avoid batch insert for big tuples, as it has significant
overhead. Switching from (limited) batch insert to foreign insert
allowed to decrease memory consumption in 3 times in some tests.
--
Best regards,
Alexander Pyhalov,
Postgres Professional