PostgreSQL Memory Architecture

#apacheage #database #postgres #agedb

In this post, we will discuss the Memory Architecture of PostgreSQL. Memory architecture in PostgreSQL can be classified into two broad categories: In the following subsections, those are briefly described:

`✔️ Local memory area:`

It is allocated by each backend process for its own use. Each backend process allocates a local memory area for query processing; each area is divided into several sub-areas – whose sizes are either fixed or variable.

By default, each session will take size of 4Mb. Eg.: If there are 100 sessions, then they will consume 400 Mb. Below table shows a list of the major sub-areas:

`✔️ Shared memory area:`

It is used by all processes of a PostgreSQL server. A shared memory area is allocated by a PostgreSQL server when it starts up. This area is also divided into several fix sized sub-areas. Below table shows a list of the major sub-areas:

In the following Figure, those process are graphically described:

If we want to check all this from postgreSQL, then below queries will give proper outcomes:

postgres=# select name,setting,unit from pg_settings where name like '%buffer%';
      name      | setting | unit
----------------+---------+------
 shared_buffers | 16384   | 8kB
 temp_buffers   | 1024    | 8kB
 wal_buffers    | 512     | 8kB
(3 rows)

postgres=# select name,setting,unit from pg_settings where name like '%work_mem%';
           name            | setting | unit
---------------------------+---------+------
 autovacuum_work_mem       | -1      | kB
 logical_decoding_work_mem | 65536   | kB
 maintenance_work_mem      | 65536   | kB
 work_mem                  | 4096    | kB
(4 rows)