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 document are to explain how each subsystem works, and to provide the whole picture of PostgreSQL.

This document covers versions 17 and earlier.

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

Contents
DateDescription
24.Nov.2024 Improved 3.4.1. How the Executor Performs.
Added 3.4.2. Aggregate Functions.
28.Oct.2024 Added the descriptions of Incremental Backup: 9.6.2. WAL Summarizer Process, 10.1.3. pg_basebackup and 10.5. Incremental Backup.
04.Jan.2024 Changed framework from Cascade to Relearn.
11.Sep.2023 Updated Section 8.2.2 and Section 8.3.2.
02.Aug.2023 Fixed bug in Section 5.9.
CheckTargetForConflictsIn -> CheckForSerializableConflictIn
CheckTargetForConflictsOut -> CheckForSerializableConflictOut
02.Aug.2023 Added Section 6.5.1 and 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.
  1. Updated Section 6.1.1.
  2. Added "WAL segment file size" in Section 9.2.
  3. Added "Removal of prior checkpoint in PostgreSQL 11" in Section 9.7.2.
  4. Updated Section 9.9.2.
  5. Updated Section 10.1.1.

Author

Hironobu SUZUKI

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

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 document 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 por unos años. Recientemente, a veces vuelve a allí.

I am looking for a new job, applying ML and AI technologies to DBMS.

      Blog

Contact

Please read the following FAQ before sending messages.

Since publishing my content, I’ve been fortunate to receive a lot of positive feedback, which is truly gratifying. However, unfortunately, I’ve encountered individuals in the past who tried to misuse my content for self-promotion 1.

These experiences have shaped the approach I’ve outlined below.

  1. Who can use this document freely?
    If you are a teacher or a student belonging to an educational organization, you can freely use this document and figures in your study. Anyone can use this document and figures with noncommercial meetings and lectures, if you state the link to this site and the copyright; otherwise, contact me.
  2. Is it available for commercial contents?
    This content can be used under two options:
    • Revenue Share: You can leverage this content after a revenue share agreement is signed. Under this agreement, you’ll share 20% of the sales generated from using this content.
    • Full Buyout: In very rare cases, I consider requests for full commercial use of all content on this site. For a complete buyout of all content rights, the cost is €5,000,000.
  3. Does the author need any help to write?
    No. I am writing for my own enjoyment. Even if you send me your article, I will never include it in my document.
  4. Why doesn’t the author use the GitBook or other e-book formats/sites?
    I am using the best tools I need and I will decide what I use. I am always trying a lot of tools for better explanations.
    Simply put, this document is optimized to read on html file and it is hard to convert to other formats.
    I do not have any plan to make PDF version and Smartphone versions because of same reason.
  5. Why doesn’t the author use RSS to notify the update?
    I keep my domain and my server to provide my document; you can read when you want to read. What do you want more?
  6. Why doesn’t the author waive the copyright of this document or use the creative commons license?
    I’d like to ask you what problems you have by that I keep on having the copyright of my document.

  1. See the source code. ↩︎

After reading, send a message to my twitter in public.

or   /    

If you use email, please provide at least two SNS addresses (e.g. LinkedIn, Twitter) for verification purposes. Due to the XZ backdoor incident, I no longer accept contact from anonymous individuals.

© Copyright ALL Right Reserved, Hironobu SUZUKI.

For any inquiries regarding the use of this document or any of its figures, please contact me.

Exception Educational institutions can use this document freely.