DEV Community: m_aamir

Exploring how the Buffer Manager works in PostgreSQL 1: Introduction

m_aamir — Wed, 28 Jun 2023 23:49:46 +0000

Introduction

The PostgreSQL database management system (DBMS) relies on an efficient buffer manager to optimize data transfers between shared memory and persistent storage. Understanding the intricacies of the buffer manager is crucial for maximizing the performance of PostgreSQL. In this blog post, we will briefly overview the PostgreSQL buffer manager and explore its structure, operations, and key components that contribute to its exceptional efficiency.

Buffer Manager Structure

At the core of the PostgreSQL buffer manager lies a carefully crafted structure consisting of a buffer table, buffer descriptors, and the buffer pool. The buffer pool acts as an array, housing data file pages, such as tables, indexes, freespace maps, and visibility maps. Each slot within the buffer pool represents a single page of a data file, and these slots are referred to as buffer IDs.

Buffer Tags:

In PostgreSQL, every page of all data files is assigned a unique identifier called a buffer tag. When a request is made to the buffer manager, PostgreSQL utilizes the buffer tag to locate the desired page. The buffer tag comprises three values: the RelFileNode and the fork number, indicating the relation to which the page belongs, and the block number, specifying the page itself. Understanding the buffer tag concept is vital for comprehending how PostgreSQL manages data retrieval and storage effectively.

How a Backend Process Reads Pages:

The backend process sends a request containing the buffer tag of the desired page to the buffer manager, to which the buffer manager responds by returning the buffer ID of the slot that stores the page. Additionally, the buffer manager also handles scenarios where the requested page is not present in the buffer pool, requiring it to load the page from persistent storage.

Page Replacement Algorithm:

When the buffer pool is full and a new page needs to be loaded, the buffer manager employs a page replacement algorithm to select a victim page that will be replaced by the new page. In PostgreSQL, the clock sweep algorithm has been implemented since version 8.1, as it offers simplicity and efficiency compared to earlier versions that used the LRU algorithm.

Flushing Dirty Pages:

Dirty pages, which have been modified by backend processes but not yet written back to storage, require periodic flushing. PostgreSQL employs two background processes, known as the checkpointer and background writer, to handle this task. This section explores the role of these background processes in ensuring that dirty pages are appropriately flushed to storage, maintaining data integrity and performance.

Understanding Transaction Status and Commit Log in PostgreSQL

m_aamir — Tue, 30 May 2023 11:29:10 +0000

Transactions play a crucial role in ensuring the reliability and consistency of data in a database management system. In PostgreSQL, the statuses of transactions are stored in the Commit Log, commonly known as the clog. This article dives into the intricacies of transaction statuses, the functioning of the clog, and the maintenance involved.

Transaction Status in PostgreSQL

PostgreSQL defines four transaction states: IN_PROGRESS, COMMITTED, ABORTED, and SUB_COMMITTED. The first three statuses are self-explanatory. When a transaction is ongoing, its status is IN_PROGRESS. Similarly, when a transaction successfully completes, it is marked as COMMITTED. In contrast, if a transaction fails or is explicitly rolled back, its status is set to ABORTED. The SUB_COMMITTED status is specific to sub-transactions but won't be covered in detail here.

How the Commit Log (Clog) Functions

The Commit Log, or clog, is a portion of shared memory in PostgreSQL that holds the transaction statuses. Physically, the clog consists of one or more 8 KB pages, forming a logical array. Each index in the array corresponds to a transaction ID, and the array stores the status of each transaction.

Let's consider two scenarios:

Transaction T1 with txid 200 commits, causing its status to change from IN_PROGRESS to COMMITTED.
Transaction T2 with txid 201 aborts, resulting in its status changing from IN_PROGRESS to ABORTED. As the transaction ID advances and the clog reaches its capacity, a new page is appended to accommodate additional transaction statuses.

To retrieve the status of a transaction, internal functions are invoked, which read the clog and provide the requested transaction's status.

Maintenance of the Commit Log

When PostgreSQL shuts down or the checkpoint process runs, the data from the clog are written into files stored under the pg_xact subdirectory. It's worth noting that prior to Version 9.6, the subdirectory was called pg_clog. These files are named 0000, 0001, and so on, with a maximum file size of 256 KB.

For instance, if the clog occupies eight pages (from the first to the eighth, totaling 64 KB), its data will be written into the file 0000 (64 KB). In another scenario, if it comprises 37 pages (296 KB), the data will be stored in both 0000 (256 KB) and 0001 (40 KB).

During startup, PostgreSQL loads the data from the pg_xact files to initialize the clog.

Since new pages are appended to the clog as it fills up, its size continuously increases. However, not all data in the clog are necessary. Regular vacuum processing is responsible for removing old data, including both clog pages and files.

Understanding Transaction Snapshots

A transaction snapshot refers to a dataset that stores information about the active status of transactions at a specific point in time for an individual transaction. Here, an active transaction refers to one that is either in progress or has not yet started.

PostgreSQL internally defines the textual representation format of transaction snapshots as '100: 100:', where '100: 100:' signifies that transactions with IDs less than 99 are inactive, while those equal to or greater than 100 are active.

Transaction snapshots are essential for maintaining data consistency in PostgreSQL's multi-version concurrency control (MVCC) system. They allow transactions to access a consistent view of the database, even if other transactions are modifying it concurrently.

Each transaction maintains its own snapshot, which is stored in memory and includes information about the transaction's start time, visibility of data, and other relevant details. The snapshot is used to determine which data versions are visible to the transaction based on the transaction's start time and the commit status of other transactions.

When a transaction reads data, it uses its snapshot to identify the appropriate data versions based on their visibility. If a data version is marked as committed and was committed before the transaction's start time, it is considered visible to the transaction. On the other hand, if a data version is marked as aborted or was committed after the transaction's start time, it is considered invisible to the transaction.

By using transaction snapshots, PostgreSQL provides a consistent and isolated view of the data for each transaction, ensuring that transactions operate on a consistent set of data even in the presence of concurrent modifications.

References:

https://www.interdb.jp/pg/pgsql05.html

Concurrency Control in PostgreSQL Part 1: The Building Blocks

m_aamir — Tue, 30 May 2023 08:28:03 +0000

Concurrency control may sound like a complex term, but it's an essential mechanism for maintaining atomicity and isolation in databases when multiple transactions are running simultaneously. Understanding the basics of concurrency control can help you build more robust and reliable applications. In this blog post, we'll go over the basic information required to understand how concurrency control is implemented in the PostgreSQL relational database management system.

Concurrency Control in Relational Databases

There are three broad concurrency control techniques: Multi-version Concurrency Control (MVCC), Strict Two-Phase Locking (S2PL), and Optimistic Concurrency Control (OCC). Each technique has its variations, but let's focus on the main ideas.

MVCC, as the name suggests, creates multiple versions of a data item when a write operation occurs while retaining the old version. When a transaction reads a data item, the system selects the appropriate version to ensure transaction isolation. One significant advantage of MVCC is that readers and writers don't block each other. For example, in an S2PL-based system, readers are blocked when a writer writes an item because the writer acquires an exclusive lock. PostgreSQL and some other Relational Database Management Systems (RDBMSs) use a variation of MVCC called Snapshot Isolation (SI), which offers similar benefits.

Concurrency Control in Postgres

To implement Snapshot Isolation, PostgreSQL uses rollback segments or a simpler method of inserting new data items directly into the relevant table page. When reading items, PostgreSQL applies visibility check rules to select the appropriate version for each transaction.

Snapshot Isolation (SI) prevents three anomalies defined in the ANSI SQL-92 standard: Dirty Reads, Non-Repeatable Reads, and Phantom Reads. However, SI falls short of achieving true serializability because it allows serialization anomalies like Write Skew and Read-only Transaction Skew. It's important to note that the ANSI SQL-92 standard's classical serializability definition is not equivalent to the definition in modern theory.

To address this issue, Serializable Snapshot Isolation (SSI) was introduced in PostgreSQL version 9.1. SSI can detect serialization anomalies and resolve conflicts caused by such anomalies, providing a true SERIALIZABLE isolation level. It's worth mentioning that SQL Server also uses SSI, while Oracle continues to use only SI.

We will now go over some basic concepts required to understand how concurrency control is implemented in postgres.

Transaction ID:

When a transaction begins in PostgreSQL, it is assigned a unique identifier called a transaction id(txid). The txid is a 32-bit unsigned integer, allowing for approximately 4.2 billion possible values. The built-in function txid_current() can be used to retrieve the current txid within a transaction.

There are three special txids in PostgreSQL:

0 represents an invalid txid.
1 is the bootstrap txid, used during the initialization of the database cluster.
2 is the frozen txid

PostgreSQL treats the txid space as a circular range due to limitations on the number of available txids. The previous 2.1 billion txids are considered "in the past," while the next 2.1 billion txids are considered "in the future." This circular range allows for efficient comparison and management of txids.

Tuple Structure

A heap tuple consists of three parts: the HeapTupleHeaderData structure, a NULL bitmap, and user data. The HeapTupleHeaderData structure contains several fields, listed below:

t_xmin (transaction id of the inserting transaction)
t_xmax (transaction id of the deleting or updating transaction)
t_cid (command id indicating the number of executed SQL commands within the current transaction)
t_ctid (tuple identifier pointing to itself or a new tuple).

References:

https://www.interdb.jp/pg/pgsql05.html

PostgreSQL Memory Architecture

m_aamir — Tue, 25 Apr 2023 19:37:58 +0000

PostgreSQL has two kinds of memory locations to store data: the shared memory area, and local memory area.

Shared Memory Area

The shared memory area is used by all processes of a postgreSQL server. It is allocated when the server is started.

The shared memory area can be divided into sub-areas based on their function. The main sub-areas are described below:

Shared pool buffer: this is where postgres loads pages and operates on them.
WAL buffer: this is where WAL data is stored before being written to permanent storage.
Commit log: stores the states of all transactions.
Checkpointer
Autovacuum
Sub-areas for semaphores and locks for concurrency control.

Local Memory Area

This is unique to each backend process. It is used for query processing. It has 3 major sub-areas:

work_mem: this area is used for sorting tuples by the ORDER BY and DISTINCT operations.
maintenance_work_mem: used for maintenance operations.
temp_buffers: used for storing temporary tables.

References:

https://www.interdb.jp/pg/pgsql02.html

PostgreSQL Process Architecture

m_aamir — Fri, 14 Apr 2023 16:10:52 +0000

PostgreSQL is a powerful open-source relational database management system (RDBMS) used by many organizations and individuals around the world. If you are a developer who uses PostgreSQL frequently, it helps to understand its internal structure. In this article, we will discuss the process architecture of postgres.

Introduction

PostgreSQL has a client/server architecture. The client side is what the developer sees. The server side is made up of what is called a database cluster. A collection of multiple processes work together to manage the cluster, otherwise known as a PostgreSQL server. These processes are:

A postgres server process
Backend processes to handle queries from the client side
Various Background processes for different database management features
Replication associated processes for streaming replication
Background worker processes

The Postgres Server Process

A PostgreSQL server's postgres server process is the parent of all other processes. It used to be referred to as "postmaster" in older versions.

A postgres server process can be launched by executing the pg_ctl utility with the start option. The process sets aside a portion of shared memory area in memory, launches a number of background processes, launches background worker processes and replication-related processes as needed, and waits for client connection requests. It launches a backend procedure each time a client requests a connection. (All requests sent by the connected client are handled by the running backend process.)

A postgres server process listens to one network port, the default port is 5432.

Backend Processes

The postgres server process starts a backend process, which is also called postgres, that responds to all queries sent by a single connected client. It only uses one TCP connection to communicate with the client, and it terminates when the client disconnects.

You must specify the database you intend to use when connecting to a PostgreSQL server since only one database can be used at a time.

Multiple customers may connect to PostgreSQL at once; the configuration option max_connections restricts the number of clients that may connect at once (the default value is 100).

Due to PostgreSQL's lack of a native connection pooling functionality, expenses associated with establishing connections and developing backend procedures increases when several clients, such as WEB applications, repeatedly connect and disconnect from a PostgreSQL server. Such an event has a detrimental impact on the database server's performance. A pooling middleware (such as pgbouncer or pgpool-II) is typically used to handle such circumstances.

Background Processes

PostgreSQL has a wide variety of background processes. The most commonly used ones are briefly described below:

Background writer: In this process, dirty pages on the shared buffer pool are written to a persistent storage (e.g., HDD, SSD) on a regular basis gradually.
Checkpointer: Checkpoint process is performed.
Autovacuum Launcher: Periodically invokes the vacuum process.
Statistics Collector: Collects information for pg_stat_activity and pg_stat_database.
Logging Collector: Writes error messages into log files.
Archiver: Executes archiving logging.

References:

https://www.interdb.jp/pg/pgsql02.html