D#1: PostgreSQL Internal Architecture PART1 || #postgresql #postgresqldba #postgresqltutorial

What Is PostgreSQL? PostgreSQL is an open source object relational database management system used for transactional and analytical workloads. It incorporates and expands upon the SQL programming language, offering an array of features designed to securely store and efficiently scale even the most complex data workloads. Tracing its roots back to 1986 as part of the POSTGRES project at UC Berkeley, it has over 35 years of constant development. Basic Architecture of PostgreSQL System Like other solutions, PostgreSQL architecture adheres to the client-server model. Its primary program operates as a service responsible for defining data structures, storing data, and processing queries. PostgreSQL Process Types PostgreSQL encompasses a variety of processes, each with its unique role in ensuring smooth database operations. Broadly these processes are classified into following 3 categories: PostgreSQL Server Process Backend Process Background Worker Process PostgreSQL Memory Management Memory plays a vital role in the inter-process communication and performance of the PostgreSQL database. Memory in PostgreSQL can be broadly classified into 2 based on how they are used: Local Memory: Used by individual backend processes for running the queries Shared Memory: Used by the PostgreSQL Server process. Local Memory Area In PostgreSQL, each backend process allocates local memory for query processing; each area is divided into sub-areas whose sizes are either fixed or variable. Work_mem The executor uses this area for sorting tuples by ORDER BY and DISTINCT operations. It also uses it for joining tables by merge-join and hash-join operations. Maintenance_work_mem This parameter is used for some kinds of maintenance operations (VACUUM, REINDEX). The default value of maintenance_work_mem = 64MB. General recommendation to set maintenance_work_mem is as follows. Set the value 10% of system memory, up to 1GB Maybe you can set it even higher if you are having VACUUM problems Temp_buffers The executor uses this area for storing temporary tables. The default value of temp_buffer = 8MB. Shared Memory Area Shared memory area is allocated by PostgreSQL server when it starts up. This areas is divided into several fixed sized sub-areas. Shared buffer pool PostgreSQL loads pages within tables and indexes from persistent storage to a shared buffer pool, and then operates on them directly. This parameter designates the amount of memory used for shared memory buffers. The shared_buffers parameter determines how much memory is dedicated to to the server for caching data. The default value of shared_buffers is typically 128 megabytes (128MB). Default value of shared_buffers = 128 MB. The change requires restart of PostgreSQL server. General recommendation to set the shared_buffers is as follows. Below 2GB memory, set the value of shared_buffers to 20% of total system memory. Below 32GB memory, set the value of shared_buffers to 25% of total system memory. Above 32GB memory, set the value of shared_buffers to 8GB WAL buffer PostgreSQL supports the WAL (Write ahead log) mechanism to ensure that no data is lost after a server failure. WAL data is really a transaction log in PostgreSQL and WAL buffer is a buffering area of the WAL data before writing it to a persistent storage. The default value of work_mem = 4MB. General recommendation to set the work_mem is as follows. Start with low value: 32-64MB Then look for ‘temporary file’ lines in logs Set to 2-3 times the largest temp file Commit Log The, commit log (CLOG) keeps the states of all transactions, and is part of the concurrency control mechanism. The commit log is allocated to the shared memory and used throughout transaction processing. PostgreSQL defines the following four transaction states. IN_PROGRESS COMMITTED ABORTED SUB-COMMITTED Understanding the memory architecture and tuning the appropriate parameters is important to improve the performance.