Thread

Limit memory usage by postgres_fdw batches

Alexander Pyhalov <a.pyhalov@postgrespro.ru> — 2025-12-26T10:54:32Z

Hi.

We had some real cases when client set rather big batch_size on server 
level, but for some foreign table, containing large documents, it was 
inadequate and lead to OOM killer intervention. You can argue that 
batch_size can be set on foreign table level, but it can still be not 
flexible enough, when tuple size varies. I suppose this case is also 
takes place for fetch_size. Issue here is that we can't somehow limit 
size of data (versus number of rows) while fetching from cursor. But we 
can use tuple store to preserve fetched results, so that they spill out 
to the disk.

I'm attaching two patches which try to fix issues with possible huge 
memory usage by postgres_fdw batches.
With fetched tuples we still can't use only tuplestore, as ctids are not 
preserved, and so have to store them separately.

The reproducer for insert is simple.

create extension postgres_fdw ;
create server loopback foreign data wrapper postgres_fdw options (dbname 
'postgres', port '5432', batch_size '100', fetch_size '100');
create table base_table(i int, s bytea);
create foreign table foreign_table (i int, s bytea) server loopback 
options(table_name 'base_table');
create user mapping for public server loopback ;

insert into foreign_table select i, 
pg_read_binary_file('/some/big/file') from generate_series(1,1000) i;

will easily grow backend RSS to several gigabytes.
The first patch fixes this problem.

The second patch alleviates the second issue - SELECT * queries also can 
grow backend memory to several GBs. Still memory usage can peak (on my 
toy examples) up to 3-4 GB, but at least it seams 1-2 GB less than 
non-patched version.

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional