When Update balloons memory - Mailing list pgsql-general

The following statement below, when not divided up into chunks, but run across all 800M rows, did trigger an OOM-kill from the OS.

I have looked into it by kernel logs as well as postgresql logs. The postgresql just says it was killed, and the OS killed it due to the fact that all mem including swap was exhausted.

Looking at TOP while updating, I can see the RSS column of a single postgresql process (the connection I assume), just grow and grow until it chokes the system.

Update table alfa

set x = beta.x

from beta where beta.id=alpha.id and x <> beta.x

alpha is a wide table (40 columns), partitioned into 5 equally partitions by year. Total row count 800M rows

beta is a 10 column 40M rows table.

the updated field x is non-indexed varchar; the id fields are indexed.

there are no triggers

I am well aware that huge updates have general issues, like locking the table etc, and it is perhaps discouraged.  And I did solve it by batching it in 1M and 1M rows.

However, my curiosity still remains of what is really happening here.  Why do Postgresql run out of memory? Exactly what is it storing in that memory?  I am aware of the work_mem danger, but that is not what is happening here.  I can replicate this with 32MB work mem as well; This is a low connection database.

Any help is appreciated.

Klaudie

track_activity_query_size = 4096                                                 

synchronous_commit = off                                                         

full_page_writes = off                                                           

#wal_compression = on                                                            

wal_level = minimal                                                              

max_wal_senders = 0                                                              

                                                                                 

log_min_duration_statement = 1000                                                

idle_in_transaction_session_timeout = '300s'    # in milliseconds, 0 is disabled 

tcp_keepalives_idle = '300s'                                                     

max_connections = 50                                                             

shared_buffers = 16GB                                                            

effective_cache_size = 48GB                                                      

maintenance_work_mem = 2GB                                                       

checkpoint_completion_target = 0.9                                               

min_wal_size = 4GB                                                               

max_wal_size = 16GB                                                              

#wal_buffers = 16MB                                                              

default_statistics_target = 1000                                                 

random_page_cost = 1.1                                                           

effective_io_concurrency = 200                                                   

work_mem = 1000MB                                                                

max_worker_processes = 8                                                         

max_parallel_workers = 8                                                         

max_parallel_workers_per_gather = 4                                              

max_parallel_maintenance_workers = 4                                             

cpu_tuple_cost = 0.03