PostgreSQL 内部机制解析

I am officially open to new international opportunities! 🌍

After a temporary stay in Japan to focus on my health and personal responsibilities, I am fully recovered, ready, and excited to work abroad again.

I am highly interested in returning to Europe, as well as opportunities in the Southern Hemisphere and Southeast Asia.

Looking for:

· Challenging tasks in PostgreSQL.

· Roles offering international visa sponsorship OR Remote work that supports a digital nomad lifestyle.

If your team is hiring, let’s connect! Please feel free to reach out via e-Mail(hironobu @ interdb.jp or interdb.mx @ gmail.com) or DM.

What’s New

· 26 Sep 2026: 11th Anniversary! This content was launched on 26 September 2015.

· 28 Jun 2026: Section 8.5, “Asynchronous I/O in PostgreSQL” Published.

· 1 May 2026: Chapter 12, “Logical Replication” (Beta Version) Published.

· 14 Dec 2024: Added Conflicts in Section 11.2.4.

Section 11.4. --> Section 3.7. --> Section 10.4. -->

Change History (since 3rd June, 2018)

85,577/66,417=1.288 ==> 29% Increased.

33+16=49 figures added. -->

DateDescription

6 Oct 2026

Merged Sections 10.3 and 10.4; renumbered Section 10.5 as 10.4.

5 Oct 2026

Fixed mistakes in Section 10.4.3.

1 Oct 2026

Improved the introductions of Chapters 1, 2, 8, 9, 10, 11 and 12.

28 Sep 2026

Rewrote Section 12.7.3. Parallel Apply Worker Mode.

3 Jul 2026

Updated Section 6.6. Reclaiming Bloated Space.

28 June 2026

Added Section 8.5. Asynchronous I/O in PostgreSQL.

1 June 2026

Added Section 3.4.3 Constraints.

27 May 2026

Updated Chapter 8 to focus on version 9.6 and later, removing deprecated content for version 9.5 and older.

25 May 2026

Fixed an error in Section 8.4.1.2 by correcting the sequence of Steps (5) and (6), along with Figure 8.9.

23 May 2026

Refactor all chapters: reduced by 4,890 words (5.6%).

11 May 2026

Added Section 1.3.2 TOAST (The Oversized-Attribute Storage Technique).

1 May 2026

Added Chapter 12: Logical Replication (Beta Version).

27 Oct 2025

Improved the explanation in Section 8.6.

09 Oct 2025

Added the explanation about Cardinality Estimation in Section 3.2.4.

15 Apr 2025

Added the explanation about pg_stat_progress_vacuum view in Section 6.1.

15 Dec 2024

Added Section 9.5.1.

14 Dec 2024

Added the explanation about Conflicts in Section 11.2.4.

13 Dec 2024

Added Replication Slots in Section 11.4.

5 Dec 2024

Added the explanation about Sharing the Ring Buffer with Two Backends in Section 8.5.1 (currentry 8.4.3).

4 Dec 2024

Added Local Buffer Management in Section 8.5.2 (currentry 8.4.4.).

1 Dec 2024

Added Quorum-Based Synchronous Replication in Section 11.3.2.1.

30 Nov 2024

Corrected an error in the Checkpoint explanation.

In the checkpoint sequence described in Section 9.7.1, the timing of the checkpoint WAL record write has been moved from step (2) to step (4).

28 Nov 2024

Added 3.7. Parallel Query.

24 Nov 2024

Added 3.4.2. Aggregate Functions.

Improved 3.4.1. How the Executor Performs.

28 Oct 2024

Added the descriptions of Incremental Backup:

· 9.6.2. WAL Summarizer Process

· 10.1.3. pg_basebackup

· 10.4. Incremental Backup

04 Jan 2024

Changed framework from Cascade to Relearn.

11 Sep 2023

Updated Sections 8.2.2 and 8.3.2.

Although the original text was based on version 9.5, these sections have been updated based on versions 9.6 or later due to the significant changes made to the BufferDesc structure in version 9.6.

02 Aug 2023

Fixed bug in Section 5.9.

CheckTargetForConflictsIn -> CheckForSerializableConflictIn

CheckTargetForConflictsOut -> CheckForSerializableConflictOut

02 Aug 2023

Added "Conditions for autovacuum to run" in Section 6.5.1 and "Maintenance tips" in Section 6.5.2.

02 May 2023 Splitted Chapter 3 in three files since this part was too long.

24 Feb 2023 Removed "9.9.3. WAL Segment Management in Version 9.4 or Earlier" in Section 9.9.

12 Jan 2023 Changed txid_current_snapshot() to pg_current_snapshot() in Section 5.5.

22 Oct 2022 Added "archive_library" in Section 9.10.

22 Oct 2022 Updated function names:pg_backup_start and pg_backup_stop in Chapter 10.

02 Jan 2022 Added "WAL, Backup, and Replication" in Section 9.1.3.

12 Nov 2021 Changed the description in the preface of Chapter 5.

· Concurrency Control is a mechanism that maintains consistency atomicity and isolation,...

25 Mar 2021 Added a command to delete archiving logs in Section 9.10.

28 Jun 2020 Changed the color scheme of the all figures.

19 Jun 2020 Added Section 3.5.3.3.

06 Jun 2020 Updated the table 4.1 in Section 4.2.

Added "PARALLEL option" in Section 6.1.

Added the description about the "cleaning up indexes" phase in Section 6.1.

04 Jun 2020 Added a description related to recovery.conf in Section 10.2.

27 Mar 2019 Added "Indexes Internals" in Section 1.4.2.

15 Dec 2018

Fixed Section 3.3.2.1.

· Based on the cheapest access path obtained here, a query tree a plan tree is generated.

10 Oct 2018 Improved Section 8.1.2.

01 Oct 2018

Fixed Section 3.3.2.2.

· (3) Create a path, estimate the cost of the sequential scan and add the path to the indexlist pathlist of the RelOptInfo.

24 Aug 2018 Added Section 4.1.

13 Aug 2018 Changed in Section 9.7.

13 Aug 2018 Added "How to Maintain AUTOVACUUM" in Section 6.5.

03 Aug 2018 Added "Why the checkpointer was separated from the background writer?" in Section 8.6.

3 Jun 2018

Updated for Version 11.

· Updated Section 6.1.1.

· Added "WAL segment file size" in Section 9.2.

· Added "Removal of prior checkpoint in PostgreSQL 11" in Section 9.7.2.

· Updated Section 9.9.2.

· Updated Section 10.1.1.

The Internals of PostgreSQL

PostgreSQL is a well-designed, open-source multi-purpose relational database system which is widely used throughout the world.

It is one huge system with the integrated subsystems, each of which has a particular complex feature and works cooperatively with each other. Although understanding of the internal mechanism is crucial for both administration and integration using PostgreSQL, its hugeness and complexity make it difficult.

The main purposes of this documentation are to explain how each subsystem works, and to provide the whole picture of PostgreSQL.

This documentation covers versions 19-beta (excluding the new buffer page lock mechanism) and earlier.

Some academic papers have referred to this documentation. The Chinese version of this documentation was published in June 2019.

This documentation was first published on 26 September 2015.

Contents

· Chapter 1. Database Cluster, Databases and Tables

· Chapter 2. Process and Memory Architecture

· Chapter 3. Query Processing

· Chapter 4. Foreign Data Wrappers (FDW)

· Chapter 5. Concurrency Control

· Chapter 6. VACUUM Processing

· Chapter 7. Heap Only Tuple (HOT) and Index-Only Scans

· Chapter 8. Buffer Manager

· Chapter 9. Write-Ahead Logging (WAL)

· Chapter 10. Online Backup and Point-In-Time Recovery (PITR)

· Chapter 11. Streaming Replication

· Chapter 12. Logical Replication (Beta Version)

Author

Hironobu SUZUKI

I am a software programmer/engineer, the author of:

· The Internals of PostgreSQL

· The Engineer’s Guide To Deep Learning

· pg_plan_inspector

I graduated from graduate school in information engineering (M.S. in Information Engineering), have worked for several companies as a software developer and technical manager/director. I published seven books in the fields of database and system integration (4 PostgreSQL books and 3 MySQL books). In June 2019, the Chinese book of this documentation was published.

As a director of the Japan PostgreSQL Users Group (2010-2016), I organized the largest (non-commercial) technical seminar/lecture on PostgreSQL in Japan for more than six years, and also served as the program committee chair of the Japan PostgreSQL Conference in 2013 and as a member in 2008 and 2009.

In June 2022, my interview article was published in “PostgreSQL person of the week”.

Cuando era joven, vivió en Sudamérica p