Re: PostgreSQL partition tables use more private memory

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_f8(at)lab(dot)ntt(dot)co(dot)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