About the system: Ubuntu 20.04, 64GB ram, 16GB shared buffer, 500 MB working mem, Postgresql 14.1
Core issue: 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. Statement: 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
