BUG #16363: Memory increases for each table accessed until connection is closed - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #16363: Memory increases for each table accessed until connection is closed
Date
Msg-id 16363-a66916ab04716e58@postgresql.org
Whole thread Raw
Responses Re: BUG #16363: Memory increases for each table accessed until connection is closed
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      16363
Logged by:          Eduardo Barreto Alenxadre
Email address:      sezdocs@gmail.com
PostgreSQL version: 12.2
Operating system:   Ubuntu Linux 19.04
Description:

I have a specific use case when I need a lot of similar tables in my
database, in one case, for example, I have more than 700 tables.

Whenever I insert data in one of these tables within the same connection,
the memory used by that connection process will increase and never be freed
(unless I close the connection), because of that I'm getting Out Of Memory
errors in my backend since the connection eventually consumes the entire
memory available in my system.

Looking around I found this issue in StackOverflow which I think is exactly
the issue I'm having:
https://stackoverflow.com/questions/5587830/postgresql-backend-process-high-memory-usage-issue

I'm not sure if this is a memory leak or simply a cache mechanism of
Postgres, if the later, can I control it somehow to force it to free
memory?

just for completeness, this is a table of the memory usage of a single
Postgres connection process running a script that will simply insert new
data randomly in these 700 tables one at a time.

# Elapsed time   Real (MB)   Virtual (MB)
       0.000        26.453      328.066
      20.017       421.367      766.164
      40.037       889.867     1302.520
      60.057      1305.645     1772.336
      80.077      1681.555     2243.105
     100.093      2001.133     2679.195
     120.109      2333.535     3131.918
     140.129      2622.051     3518.801
     160.141      2887.207     3886.355
     180.157      3209.109     4323.109
     200.177      3414.656     4606.566
     220.197      3414.656     4606.566
     240.218      3414.656     4606.566

After the last line, I got OOM error.


pgsql-bugs by date:

Previous
From: Iipponen Timo
Date:
Subject: Re: GRANT CONNECT statements not shown after pg_dump - pg_restorefrom PostgreSQL 9 to PostgreSQL 12
Next
From: Tom Lane
Date:
Subject: Re: BUG #16363: Memory increases for each table accessed until connection is closed