Analyze memory usage of PostgreSQL – why is it growing constantly?

I am inserting millions of rows into a PostgreSQL 9.5 database and observe a constant growth of memory usage. As the tables are not that large and the operations performed (the insertions trigger a Pl/Python function) should not be that expensive I wonder why this happens.

At the moment PostgreSQL is using ~ 50 GB of total available 60 GB. I would like to understand how PostgreSQL is using those 50 GB especially as I fear that the process will run out of memory.

[Update] Tonight PostgreSQL ran out of memory and was killed by the OS.

$ pg_top
last pid: 13535;  load avg:  1.26,  1.41,  1.42;       up 2+02:57:11                                                                                                                                                                19:29:26
3 processes: 1 running, 2 sleeping
CPU states: 12.4% user,  0.0% nice,  0.1% system, 87.4% idle,  0.0% iowait
Memory: 63G used, 319M free, 192M buffers, 28G cached
DB activity:   2 tps,  0 rollbs/s,   0 buffer r/s, 100 hit%,     42 row r/s,    0 row w/s 
DB I/O:     0 reads/s,     0 KB/s,     0 writes/s,     0 KB/s  
DB disk: 98.0 GB total, 41.9 GB free (57% used)
Swap: 38M used, 1330M free, 12M cached
Re-run SQL for analysis: 
  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
 8528 postgres  20    0   50G   39G run    18.3H 97.55% 99.35% postgres: postgres my_db ::1(51692) EXECUTE                                                                              
11453 postgres  20    0   16G  157M sleep   0:06  0.00%  0.00% postgres: postgres my_db ::1(51808) idle                                                                                 
13536 postgres  20    0   16G   17M sleep   0:00  0.00%  0.00% postgres: postgres postgres [local] idle

```
$ top
top - 21:51:48 up 2 days, 5:19, 4 users, load average: 1.40, 1.31, 1.23
Tasks: 214 total, 2 running, 212 sleeping, 0 stopped, 0 zombie
%Cpu(s): 12.4 us, 0.0 sy, 0.0 ni, 87.4 id, 0.0 wa, 0.0 hi, 0.0 si,

A trigger that is defined as AFTER INSERT...FOR EACH ROW will queue up info all the inserted rows and then fire the trigger for each one at the end of the statement. So if you insert millions of records with a single statement, that queue will take up a lot of memory.

BEFORE INSERT does not do this, it executes the trigger function for each row immediately before each one is inserted, and doesn't queue up anything. If possible, rewrite to a BEFORE trigger.

StackExchange Database Administrators Q#185880, answer score: 16