CHAPTER 01 PostgreSQL Architecture

1.
Main Process
1.1 Postmaster Process
1.2 Backend Process
1.3 Background Process

2.
memory
2.1 Shared Memory
2.2 Local Memory

3.
PostgreSQL structure
3.1 Logical Structure
3.2 Physical Structure

4.
Shared Buffer
4.1 Shared Buffer Component
4.2 Reading data from a shared buffer
4.3 Clock Sweep
4.4 PostgreSQL IO Strategies

5. WAL (Write-Ahead Log)
5.1 WAL segment files
5.2 Checkpoint
5.3 WAL segment file management
5.4 Writing WAL records
5.5 Data Recovery Using WAL Files

CHAPTER 02 Transactions and MVCC

1.
Transaction Isolation Level
1.1 Isolation Levels in PostgreSQL

2.
Data storage structure
2.1 Page Structure
2.2 Transaction ID
2.3 Tuple structure
2.4 Tuple version
2.5 Indices and Tuples

3.
Snapshot
3.1 What is a snapshot?
3.2 Snapshots and Tuple Visibility
3.3 Snapshots and Database Horizon
3.4 Exporting Snapshot
3.5 Snapshot too old

4.
Single-page cleanup and HOT updates
4.1 Fillfactor
4.2 Single Page Cleanup
4.3 HOT(Heap Only Tuple) Update

5. MVCC (Multi Version Concurrency Control)
5.1 What is MVCC?
5.2 XID Circular Structure and Frozen XID
5.3 Age

6.
Vacuum and Autovacuum
6.1 Vacuum
6.2 Autovacuum

CHAPTER 03 LOCK

1.
Object Level Lock
1.1 Object Types
1.2 Object lock mode

2.
Row Level Lock
2.1 Row-level lock mode
2.2 Multitransactions
2.3 Tuple lock wait
2.4 Dead Lock

3.
Memory Level Lock
3.1 Light-Weight Locks (LWLocks)
3.2 Spinlocks
3.3 Buffer Pin Lock
3.4 WAL Buffer Lock
3.5 Memory Lock Monitoring

CHAPTER 04 SQL Execution

1.
Cost Based Optimizer
1.1 CBO Cost

2.
PostgreSQL statistics
2.1 Manual statistical information collection
2.2 Statistical Information Management

3.
Action plan
3.1 Execution plan extraction method
3.2 Execution Plan Analysis

4. SQL processing process
4.1 SQL Processing Process
4.2 Prepare Statement

5.
Scan Method
5.1 Sequential Scan
5.2 Index Scan
5.3 Bitmap Index Scan
5.4 Index Only Scan
5.5 Covering Index

6.
Join Method
6.1 Nested Loop Join
6.2 Hash Join
6.3 Sort-Merge Join

Appendix

1.
Anomalies according to isolation level
1.1 Read Committed
1.2 Repeatable Read
1.3 Serializable

2.
Table Bloating Monitoring

3. SQL Monitoring
3.1 pg_stat_statements
3.2 pg_stat_monitor

How are tuple versions managed to ensure data read consistency?
Why is Vacuum necessary and what does it have to do with database performance?
How are locks implemented for tables and tuples?
How does the optimizer calculate the cost of SQL execution?
Want to know about the newly introduced join method for query performance?

(introduction)

My first encounter with PostgreSQL was five years ago when I introduced it as an open source database for my company's solutions.
At the time, we only looked at PostgreSQL intermittently to find solutions whenever performance issues arose.
However, as the saying goes, “A thirsty person digs a well,” I delved deeply into solving the problem and ended up writing a book.

With the recent proliferation of cloud environments, interest in PostgreSQL is growing, and various open-source databases based on it are being released.
Despite being an open-source environment, PostgreSQL provides powerful features for performance analysis and tuning by utilizing various performance statistics.
In addition, the optimizer is continuously improved through version upgrades, and extension packs useful for performance analysis are also constantly being developed.

This book covers the core topics essential for performance management, aimed at beginner and intermediate PostgreSQL users.
We cover the core features of PostgreSQL in depth, including architecture, transactions, MVCC, vacuum, locks, and SQL execution plans.
Beyond simply describing the theory, we've written it so that you can clearly understand its internal workings through various tests and verifications.
I hope that this structure will allow you to tackle PostgreSQL without any obstacles, and that is why I titled the book “PostgreSQL Without Obstacles.”
In particular, we have used various pictures and visual aids to help readers easily understand content that may be considered technically difficult, and we have also provided practical examples and scripts so that readers can experience the principles firsthand.
Note that all tests are configured to run on PostgreSQL version 14 or later.

Many people helped bring this book to life.
I am deeply grateful to Chairman Cho Jong-am, who has always emphasized the philosophy that “technicians must share their knowledge” and has reminded us of the value of knowledge sharing.
I would also like to express my gratitude to President Koh Pyeong-seok, who supported and waited patiently for the book's publication, and to Director Jeong Dong-gi, who encouraged me to decide to write.
Writing a book while also doing consulting work was never easy.
Nevertheless, I would like to express my sincere gratitude to Director Kim Cheol-hwan, Senior Manager Park Gwan-gyu, and Manager Kim Gyu-min, who sacrificed their sleep and weekends to silently fulfill their responsibilities.
I would also like to express my deepest gratitude to Senior Editor Song Yu-rim for her passion for design and editing.

I believe that the path to sustainable competency in any field ultimately lies in learning.

Since the path of learning is endless, I wrote this book with the mindset of ‘learning and sharing it with others.’


Although I regret not being able to include more content, I hope that it will be of some help to you, the readers.

December 2024
Representative author Lim Gyeong-seok