PostgreSQL Summary Pt8
Tags: postgres, postgressql
Published: 2023-07-10 11:48:28

Continuing on [part 7](https://dev.to/yosefahab/postgresql-summary-pt7-3pdn), i'll be discussing XLOG records, and the part they play in database recovery.

## Writing of XLOG Records
We'll explore the internal function **exec_simple_query()** by invoking the following query: `INSERT INTO tbl VALUES ('A');`

The pseudo code of **exec_simple_query()**:
```c++
exec_simple_query() @postgres.c

(1) ExtendCLOG() @clog.c                  /* Write the state of this transaction
                                           * "IN_PROGRESS" to the CLOG.
                                           */
(2) heap_insert()@heapam.c                /* Insert a tuple, creates a XLOG record,
                                           * and invoke the function XLogInsert.
                                           */
(3)   XLogInsert() @xlog.c (9.5 or later, xloginsert.c)
                                          /* Write the XLOG record of the inserted tuple
                                           *  to the WAL buffer, and update page's pd_lsn.
                                           */
(4) finish_xact_command() @postgres.c     /* Invoke commit action.*/   
      XLogInsert() @xlog.c  (9.5 or later, xloginsert.c)
                                          /* Write a XLOG record of this commit action 
                                           * to the WAL buffer.
                                           */
(5)   XLogWrite() @xlog.c                 /* Write and flush all XLOG records on 
                                           * the WAL buffer to WAL segment.
                                           */
(6) TransactionIdCommitTree() @transam.c  /* Change the state of this transaction 
                                           * from "IN_PROGRESS" to "COMMITTED" on the CLOG.
                                           */
```

### Explanation:
1. **ExtendCLOG()** writes the state of this transaction **IN_PROGRESS** in the (in-memory) CLOG.
2. **heap_insert()** inserts a heap tuple into the target page on the shared buffer pool, creates this page's XLOG record, and invokes **XLogInsert()**. 
3. **XLogInsert()** writes the XLOG record created by the _heap_insert()_ to the WAL buffer at _LSN_1_, and then updates the modified page's **pd_lsn** *from LSN_0 to LSN_1*. 
4. **finish_xact_command()**, which is invoked to commit this transaction, creates this commit action's XLOG record, and then **XLogInsert()** writes this record into the WAL buffer at **LSN_2**.
5. **XLogWrite()** writes and flushes all XLOG records on the WAL buffer to the WAL segment file.
   If the parameter _wal_sync_method_ is set to _'open_sync'_ or _'open_datasync'_, the records are synchronously written because the function writes all records with the _open()_ system call specified the flag _O_SYNC_ or _O_DSYNC_. If the parameter is set to _'fsync'_, _'fsync_writethrough'_ or _'fdatasync'_, the respective system call – _fsync()_, _fcntl() with F_FULLFSYNC option_, or _fdatasync()_ – will be executed. *In any case, all XLOG records are ensured to be written into the storage*. 
6. **TransactionIdCommitTree()** changes the state of this transaction *from IN_PROGRESS to COMMITTED* on the CLOG.

## WAL Writer Process
It is a background process to check the WAL buffer periodically and write all unwritten XLOG records into the WAL segments.

The purpose of this process is to improves performance by avoiding bursts of of XLOG records writes when a large amount of data are committed at once. 

This process cannot be disabled, and it checks the WAL buffer every 200 ms by default, this interval can by changed using the configuration parameter **wal_writer_delay**.'

## Checkpoint Processing in PostgreSQL
Checkpointing in PostgreSQL is performed by the checkpointer background process, it has two responsibilities: 
1. Preparation of database recovery.
2. Cleaning of dirty pages on the shared buffer pool.

It starts when one of the following occurs:
1. A superuser issues `CHECKPOINT` command manually.
2. The interval time set for **checkpoint_timeout** from the previous checkpoint has exceeded 300secs.
3. (version 9.4 or earlier) The number of WAL segment files (3 by default) set for **checkpoint_segments** has been consumed since the previous checkpoint.
4. (version 9.5 or later) The total size of the WAL segment files in the **pg_xlog** (called pg_wal in version 10 or later) has exceeded the value of the parameter **max_wal_size** (1GB (64 files) by default).
5. PostgreSQL server stops in smart or fast mode.

### pg_control File
This file contains fundamental information required by the checkpointer process, which is essential for database recovery.

This file stores over 40 items, however i'll be talking about three specific items:
1. **State**: The state of database server at the time of the latest checkpointing starts. 
   There are seven states in total, some of which are:
	   1. **start up** is the state that system is starting up.
	   2. **shut down** is the state that system is going down normally by the shutdown command
	   3. **in production** is the state that system is running.

2. **Latest checkpoint location**: LSN Location of the latest checkpoint record. 
3. **Prior checkpoint location** (deprecated in version 11): LSN Location of the prior checkpoint record .

A pg_control file is stored in the global subdirectory under the base-directory; its contents can be shown using the **pg_controldata** utility.

### To summarize
The checkpointer creates the **checkpoint record** which contains the **REDO point**, stores the checkpoint location into the **pg_control** file.
This allows PostgreSQL to recover itself *by replaying WAL data from the REDO point* which is obtained from the checkpoint record provided by the **pg_control** file.



#### References
1. https://www.interdb.jp/pg/pgsql09.html