Trim the heap free memory

Hi hackers,

Currently, all processes in PostgreSQL actually use malloc to allocate and
free memory. In the case of long connections where business queries are
executed over extended periods, the distribution of memory can become
extremely complex.

Under certain circumstances, a common issue in memory usage due to the
caching strategy of malloc may arise: even if memory is released through
the free function, it may not be returned to the OS in a timely manner.
This can lead to high system memory usage, affecting performance and the
operation of other applications, and may even result in Out-Of-Memory (OOM)
errors.

To address this issue, I have developed a new function called
pg_trim_backend_heap_free_memory, based on the existing
pg_log_backend_memory_contexts function. This function triggers the
specified process to execute the malloc_trim operation by sending signals,
thereby releasing as much unreturned memory to the operating system as
possible. This not only helps to optimize memory usage but can also
significantly enhance system performance under memory pressure.

Here is an example of using the pg_trim_backend_heap_free_memory function
to demonstrate its effect:

CREATE OR REPLACE FUNCTION public.partition_create(schemaname character
> varying, numberofpartition integer)
> RETURNS integer
> LANGUAGE plpgsql
> AS $function$
> declare
> currentTableId integer;
> currentSchemaName varchar(100);
> currentTableName varchar(100);
> begin
> execute 'create schema ' || schemaname;
> execute 'create table ' || schemaname || '.' || schemaname || 'hashtable
> (p1 text, p2 text, p3 text, p4 int, p5 int, p6 int, p7 int, p8 text, p9
> name, p10 varchar, p11 text, p12 text, p13 text) PARTITION BY HASH(p1);';
> currentTableId := 1;
> loop
> currentTableName := schemaname || '.' || schemaname || 'hashtable' ||
> ltrim(currentTableId::varchar(10));
> execute 'create table ' || currentTableName || ' PARTITION OF ' ||
> schemaname || '.' || schemaname || 'hashtable' || ' FOR VALUES WITH(MODULUS
> ' || numberofpartition || ', REMAINDER ' || currentTableId - 1 || ')';
> currentTableId := currentTableId + 1;
> if (currentTableId > numberofpartition) then exit; end if;
> end loop;
> return currentTableId - 1;
> END $function$;
>
> select public.partition_create('test3', 5000);
> select public.partition_create('test4', 5000);
> select count(*) from test4.test4hashtable a, test3.test3hashtable b where
> a.p1=b.p1;

You are now about to see the memory size of the process executing the query.

> postgres 68673 1.2 0.0 610456 124768 ? Ss 08:25 0:01
> postgres: postgres postgres [local] idle
> Size: 89600 kB
> KernelPageSize: 4 kB
> MMUPageSize: 4 kB
> Rss: 51332 kB
> Pss: 51332 kB

02b65000-082e5000 rw-p 00000000 00:00 0
> [heap]
>

After use pg_trim_backend_heap_free_memory, you will see:

> postgres=# select pg_trim_backend_heap_free_memory(pg_backend_pid());
> 2024-08-23 08:27:53.958 UTC [68673] LOG: trimming heap free memory of PID
> 68673
> pg_trim_backend_heap_free_memory
> ----------------------------------
> t
> (1 row)
> 02b65000-082e5000 rw-p 00000000 00:00 0
> [heap]
> Size: 89600 kB
> KernelPageSize: 4 kB
> MMUPageSize: 4 kB
> Rss: 4888 kB
> Pss: 4888 kB

postgres 68673 1.2 0.0 610456 75244 ? Ss 08:26 0:01
> postgres: postgres postgres [local] idle
>

Looking forward to your feedback,

Regards,

--
Shawn Wang

Now