The most possible reason for why you see LWLockTranche/buffer_mapping wait event in PostgreSQL

Well, if you are here you probably came across an issue where your database had CPU spikes for a couple of minutes and then recovered by itself. If you are lucky you are on PostgreSQL 9.6 or on a newer version, so querying pg_stat_activity view you discovered your database was waiting on buffer_mapping event.

As you might know, PostgreSQL might benefit from 2 types of caches to retrieve rows without visiting the disk subsystem. One of them is shared_buffers which is maintained by PostgreSQL and the other one is OS cache that is maintained by OS.

That brings anybody to a point that the balance between shared_buffers and OS cache should be decided. If most of the memory is reserved for shared_buffers, any memory intensive operation other than table/index access might not be able to benefit from caching decently. On the other hand, the first area where table/index rows are cached is the OS cache, then it is moved to shared_buffers according to the LRU algorithm and if most of the memory is reserved for OS cache, the processes might fight to reserve buffers in shared_buffers in which to read data pages. The latter is the reason why you would see the database processes are waiting on buffer_mapping event.

In most of the database systems, we adjust shared_buffers to 25% of the instance memory and this works quite well generally, but in rare ones with large memory areas (i.e 256GB) and with relatively lower database sizes (i.e 1TB), we keep shared_buffers at a lower value than 25% of the memory(i.e 8G instead of 64GB) based on an assumption that the active data which is accessed most frequently is really small and it can fit into that space. However, as the database grows beyond our estimation, that needs to be revised and the shared_buffers should be adjusted to a higher value than its actual value.