Thanks you guys, I will test the patches you mentioned, and keep you updated.
Thanks, Marcus Sent from my iPhone > On Dec 27, 2018, at 19:28, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > > Hi, > >> On 2018/12/27 15:44, 大松 wrote: >> # PostgreSQL partition tables use more private memory >> >> Hi, there is a process private memory issue about partition tables in our >> production environment. We're not sure if it's a bug or Pg just works in >> this way. >> >> - when dml operated on partition tables, the pg process will occupy more >> memory(I saw this in top command result, RES-SHR) than normal tables, it >> could be 10x more; >> >> - it related to partition and column quantity, the more partitions and >> columns the partition table has, the more memory the related process >> occupies; >> >> - it also related table quantity refered to dml statments which executed in >> the process, two tables could double the memory, valgrind log will show you >> the result; >> >> - pg process will not release this memory until the process is disconnected, >> unfortunately our applications use connection pool that will not release >> connections. >> >> Our PostgreSQL database server which encounters this problem has about 48GB >> memory, there are more than one hundred pg processes in this server, and >> each process comsumes couple hundreds MB of private memory. It frequently >> runs out of the physical memory and swap recently. > > Other than the problems Pavel mentioned in his email, it's a known problem > that PostgreSQL will consume tons of memory if you perform an > UPDATE/DELETE on a partitioned table containing many partitions, which is > apparently what you're describing. > > It's something we've been working on to fix. Please see if the patches > posted in the following email helps reduce the memory footprint in your case. > > https://www.postgresql.org/message-id/55bd88c6-f311-2791-0a36-11c693c69753%40lab.ntt.co.jp > > Thanks, > Amit