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.


2 comments:

  1. I feel Postgre SQL and other components and resources really deem and are dedicated enough to provide easy solutions to more complex operations.

    SSIS Postgresql Read

    ReplyDelete