Memory Components in PostgreSQL

In PostgreSQL, Memory is broadly divided into 4 parts 

1. Shared_Buffers
2. Work_Mem
3. Mainteanance_Work_MEM
4. Temp_Buffers

Shared_Buffers:

• Used for caching the table data which is accessed frequently
• Tables index data/pages get stored and operated directly and referred directly from the shared buffer if available instead of pulling from the file system.
• Bigger the buffer, more tables (data) can be stored.
• Extra-large buffer is not an advantage.
• As per recommendation should be set to 25% of system memory if system has a single cluster.
• To monitor buffer cache we can use below catalogs.
• pg_buffer_cache: to see which tables are frequently cached

Work memory: 

• Allocated for an individual session (every client get an allocation of work memory)
• It is used for Sorting / distinct / joins (merge join/ hash join) operation. These operations are first tried within memory to make it fast, it can not be done then it will move to physical directory temp files.
• How much should be allocated? After a shared buffer whatever left is divided by max connection.
• By default, its 4 MB per session, but can be increased

Maintenance_Work_mem:

• It is required for maintenance.
• The basic maintenance operations are vacuum, Analyze, Reindex
• If we do maintenance manually then Work_mem will be used.
• Specifies the maximum amount of memory to be used by maintenance operations.
• It defaults to 16 megabytes (16MB)

Temp_Buffers: 

• If we are using a lot of temp tables, then it's preferred to set.
• By setting the maximum number of temporary buffers used by each database session we can achieve performance gains. 
• These are session-local buffers used only for access to temporary tables. 
• The default is eight megabytes (8MB). 
• The setting can be changed within individual sessions, but only before the first use of temporary tables within the session; subsequent attempts to change the value will have no effect on that session.