PostgreSQL Architecture – Memory Components

PostgreSQL Architecture – Memory Components


In this post we will discuss Memory Components of PostgreSQL, Memory Components in PostgreSQL can be divided mainly into two category memory region 1). Shared Memory 2). Process Memory or Backend Memory

Shared Memory

Shared Memory: Shared Memory refers to the memory reserved for database caching and transaction log caching. This area is used by all processes of a PostgreSQL server. When the server gets started, it occupies some of the memory from the RAM. Depending upon the various parameters like shared_buffers,  wal_buffers, etc.  


Shared Memory can be further classified
as follow:


Shared Buffer: 
Sets the amount of memory the database server uses for shared memory buffers. The purpose of Shared Buffer is to minimize DISK IO. The shared buffers are accessed by all the background servers and user processes connecting to the database. The data that is written or modified in this location is called “dirty data” and the unit of operation being database blocks (or pages), the modified blocks are also called “dirty blocks” or “dirty pages”. Shared buffers are controlled by the parameter shared_buffers.


WAL (Write Ahead Log) Buffer:
The WAL buffer is a buffer that temporarily stores changes to the database. This WAL data is the metadata information about changes to the actual data and is sufficient to reconstruct actual data during database recovery operations. The WAL data is written to a set of physical files in a persistent location called “WAL segments“. The WAL buffer’s memory allocation is controlled by the wal_buffers parameter.


Clog Buffer:
CLOG stands for “commit log” CLOG buffers is an area to hold commit log pages. The commit log pages contain a log of transaction metadata and differ from the WAL data. The commit logs have the commit status of all transactions and indicate whether or not a transaction has been completed (committed). There is no specific parameter to control this area of memory. This is automatically managed by the database engine in tiny amounts.


Lock Space (Memory for Locks):
This memory component is to store all heavyweight locks used by the PostgreSQL instance. These locks are shared across all the background servers and user processes connecting to the database. A setting of two database parameters namely max_locks_per_transaction and max_pred_locks_per_transaction in way influences the size of this memory component.


Process Memory or Backend Memory

Process Memory: This memory area is allocated by each backend process for its own use. It is temporarily used privately by each Postgres process. By default, each session will take size of 4Mb. Eg.: If there are 100 sessions, then they will consume 400 Mb.


Process Memory can be further classified
as follow:


Vacuum Buffers:
This is the maximum amount of memory used by each of the autovacuum worker processes, and it is controlled by the autovacuum_work_mem database parameter. The memory is allocated from the operating system RAM and is also influenced by the autovacuum_max_workers database parameter. All these parameter settings only come into play when the auto vacuum daemon is enabled. This memory component is not shared by any other background server or user process.


Work Memory:
This is the amount of memory reserved for either a single sort or hash table operation in a query and it is controlled by work_mem parameter. A sort operation could be one of an ORDER BYDISTINCT, or Merge join and a hash table operation could be due to a hash-joinhash-based aggregation, or an IN subquery. A single complex query may have any numbers of such sort or hash table operations, and as many chunks of memory allocations defined by the work_mem parameter will be created for each of those operations in a user connection. 


Maintenance Work Memory:
This is the maximum amount of memory allocation of RAM consumed for maintenance operations. A maintenance operation could be one of the VACUUMCREATE INDEX, or adding a FOREIGN KEY to a table. The setting is controlled by the maintenance_work_mem database parameter.


Temp Buffers:
A database may have one or more temporary tables, and the data blocks (pages) of such temporary tables need a separate allocation of memory to be processed. The temp buffers defined by the temp_buffers parameter. The temp buffers are only used for access to temporary tables in a user session. 

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)

Click on below to know more about Processes and Physical Storage of PostgreSQL.

Postgresql Architecture – Process
Postgresql Architecture – Physical Storage Structure


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

 214 Total Views,  4 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

16 + 10 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver