# PostgreSQL 14 Internals

> Look inside the database. Explore MVCC, the buffer cache, WAL, locking, the planner, and every major index type.

---

LLMS index: [llms.txt](/en/llms.txt)

---

![Original cover of PostgreSQL 14 Internals](/images/internal/cover.webp)
{width=252 height=360}

**Egor Rogov**  
PostgreSQL 14 · 2023

[Start reading](/en/about-this-book/) · [Print the whole book](/en/_print/) · [Original PDF](/downloads/postgresql_internals-14_en.pdf)

## Contents {#contents}

- [About This Book](/en/about-this-book/)
  - [For Whom Is This Book?](/en/about-this-book/#internal-p15-for-whom-is-this-book)
  - [What This Book Will Not Provide](/en/about-this-book/#internal-p15-what-this-book-will-not-provide)
  - [What This Book Does Provide](/en/about-this-book/#internal-p16-what-this-book-does-provide)
  - [Conventions](/en/about-this-book/#internal-p17-conventions)
  - [Acknowledgments](/en/about-this-book/#internal-p18-acknowledgments)
- [1 Introduction](/en/01-introduction/)
  - [1.1 Data Organization](/en/01-introduction/#internal-section-1-1)
    - [Databases](/en/01-introduction/#internal-section-star-9)
    - [System Catalog](/en/01-introduction/#internal-section-star-11)
    - [Schemas](/en/01-introduction/#internal-section-star-12)
    - [Tablespaces](/en/01-introduction/#internal-section-star-13)
    - [Relations](/en/01-introduction/#internal-section-star-14)
    - [Files and Forks](/en/01-introduction/#internal-section-star-15)
    - [Pages](/en/01-introduction/#internal-section-star-16)
    - [TOAST](/en/01-introduction/#internal-section-star-17)
  - [1.2 Processes and Memory](/en/01-introduction/#internal-section-1-2)
  - [1.3 Clients and the Client-Server Protocol](/en/01-introduction/#internal-section-1-3)
- [Part I Isolation and MVCC](/en/part-i-isolation-and-mvcc/)
- [2 Isolation](/en/02-isolation/)
  - [2.1 Consistency](/en/02-isolation/#internal-section-2-1)
  - [2.2 Isolation Levels and Anomalies in SQL Standard](/en/02-isolation/#internal-section-2-2)
    - [Lost Update](/en/02-isolation/#internal-section-star-19)
    - [Dirty Reads and Read Uncommitted](/en/02-isolation/#internal-section-star-20)
    - [Non-Repeatable Reads and Read Committed](/en/02-isolation/#internal-section-star-21)
    - [Phantom Reads and Repeatable Read](/en/02-isolation/#internal-section-star-22)
    - [No Anomalies and Serializable](/en/02-isolation/#internal-section-star-23)
    - [Why These Anomalies?](/en/02-isolation/#internal-section-star-24)
  - [2.3 Isolation Levels in PostgreSQL](/en/02-isolation/#internal-section-2-3)
    - [Read Committed](/en/02-isolation/#internal-section-star-25)
    - [Repeatable Read](/en/02-isolation/#internal-section-star-31)
    - [Serializable](/en/02-isolation/#internal-section-star-36)
  - [2.4 Which Isolation Level to Use?](/en/02-isolation/#internal-section-2-4)
- [3 Pages and Tuples](/en/03-pages-and-tuples/)
  - [3.1 Page Structure](/en/03-pages-and-tuples/#internal-section-3-1)
    - [Page Header](/en/03-pages-and-tuples/#internal-section-star-39)
    - [Special Space](/en/03-pages-and-tuples/#internal-section-star-40)
    - [Tuples](/en/03-pages-and-tuples/#internal-section-star-41)
    - [Item Pointers](/en/03-pages-and-tuples/#internal-section-star-42)
    - [Free Space](/en/03-pages-and-tuples/#internal-section-star-43)
  - [3.2 Row Version Layout](/en/03-pages-and-tuples/#internal-section-3-2)
  - [3.3 Operations on Tuples](/en/03-pages-and-tuples/#internal-section-3-3)
    - [Insert](/en/03-pages-and-tuples/#internal-section-star-44)
    - [Commit](/en/03-pages-and-tuples/#internal-section-star-45)
    - [Delete](/en/03-pages-and-tuples/#internal-section-star-46)
    - [Abort](/en/03-pages-and-tuples/#internal-section-star-47)
    - [Update](/en/03-pages-and-tuples/#internal-section-star-48)
  - [3.4 Indexes](/en/03-pages-and-tuples/#internal-section-3-4)
  - [3.5 TOAST](/en/03-pages-and-tuples/#internal-section-3-5)
  - [3.6 Virtual Transactions](/en/03-pages-and-tuples/#internal-section-3-6)
  - [3.7 Subtransactions](/en/03-pages-and-tuples/#internal-section-3-7)
    - [Savepoints](/en/03-pages-and-tuples/#internal-section-star-49)
    - [Errors and Atomicity](/en/03-pages-and-tuples/#internal-section-star-50)
- [4 Snapshots](/en/04-snapshots/)
  - [4.1 What is a Snapshot?](/en/04-snapshots/#internal-section-4-1)
  - [4.2 Row Version Visibility](/en/04-snapshots/#internal-section-4-2)
  - [4.3 Snapshot Structure](/en/04-snapshots/#internal-section-4-3)
  - [4.4 Visibility of Transactions’ Own Changes](/en/04-snapshots/#internal-section-4-4)
  - [4.5 Transaction Horizon](/en/04-snapshots/#internal-section-4-5)
  - [4.6 System Catalog Snapshots](/en/04-snapshots/#internal-section-4-6)
  - [4.7 Exporting Snapshots](/en/04-snapshots/#internal-section-4-7)
- [5 Page Pruning and HOT Updates](/en/05-page-pruning-and-hot-updates/)
  - [5.1 Page Pruning](/en/05-page-pruning-and-hot-updates/#internal-section-5-1)
  - [5.2 HOT Updates](/en/05-page-pruning-and-hot-updates/#internal-section-5-2)
  - [5.3 Page Pruning for HOT Updates](/en/05-page-pruning-and-hot-updates/#internal-section-5-3)
  - [5.4 HOT Chain Splits](/en/05-page-pruning-and-hot-updates/#internal-section-5-4)
  - [5.5 Page Pruning for Indexes](/en/05-page-pruning-and-hot-updates/#internal-section-5-5)
- [6 Vacuum and Autovacuum](/en/06-vacuum-and-autovacuum/)
  - [6.1 Vacuum](/en/06-vacuum-and-autovacuum/#internal-section-6-1)
  - [6.2 Database Horizon Revisited](/en/06-vacuum-and-autovacuum/#internal-section-6-2)
  - [6.3 Vacuum Phases](/en/06-vacuum-and-autovacuum/#internal-section-6-3)
    - [Heap Scan](/en/06-vacuum-and-autovacuum/#internal-section-star-53)
    - [Index Vacuuming](/en/06-vacuum-and-autovacuum/#internal-section-star-54)
    - [Heap Vacuuming](/en/06-vacuum-and-autovacuum/#internal-section-star-55)
    - [Heap Truncation](/en/06-vacuum-and-autovacuum/#internal-section-star-56)
  - [6.4 Analysis](/en/06-vacuum-and-autovacuum/#internal-section-6-4)
  - [6.5 Automatic Vacuum and Analysis](/en/06-vacuum-and-autovacuum/#internal-section-6-5)
    - [About the Autovacuum Mechanism](/en/06-vacuum-and-autovacuum/#internal-section-star-57)
    - [Which Tables Need to be Vacuumed?](/en/06-vacuum-and-autovacuum/#internal-section-star-58)
    - [Which Tables Need to Be Analyzed?](/en/06-vacuum-and-autovacuum/#internal-section-star-61)
    - [Autovacuum in Action](/en/06-vacuum-and-autovacuum/#internal-section-star-62)
  - [6.6 Managing the Load](/en/06-vacuum-and-autovacuum/#internal-section-6-6)
    - [Vacuum Throttling](/en/06-vacuum-and-autovacuum/#internal-section-star-63)
    - [Autovacuum Throttling](/en/06-vacuum-and-autovacuum/#internal-section-star-64)
  - [6.7 Monitoring](/en/06-vacuum-and-autovacuum/#internal-section-6-7)
    - [Monitoring Vacuum](/en/06-vacuum-and-autovacuum/#internal-section-star-65)
    - [Monitoring Autovacuum](/en/06-vacuum-and-autovacuum/#internal-section-star-66)
- [7 Freezing](/en/07-freezing/)
  - [7.1 Transaction ID Wraparound](/en/07-freezing/#internal-section-7-1)
  - [7.2 Tuple Freezing and Visibility Rules](/en/07-freezing/#internal-section-7-2)
  - [7.3 Managing Freezing](/en/07-freezing/#internal-section-7-3)
    - [Minimal Freezing Age](/en/07-freezing/#internal-section-star-67)
    - [Age for Aggressive Freezing](/en/07-freezing/#internal-section-star-68)
    - [Age for Forced Autovacuum](/en/07-freezing/#internal-section-star-69)
    - [Age for Failsafe Freezing](/en/07-freezing/#internal-section-star-70)
  - [7.4 Manual Freezing](/en/07-freezing/#internal-section-7-4)
    - [Freezing by Vacuum](/en/07-freezing/#internal-section-star-71)
    - [Freezing Data at the Initial Loading](/en/07-freezing/#internal-section-star-72)
- [8 Rebuilding Tables and Indexes](/en/08-rebuilding-tables-and-indexes/)
  - [8.1 Full Vacuuming](/en/08-rebuilding-tables-and-indexes/#internal-section-8-1)
    - [Why is Routine Vacuuming not Enough?](/en/08-rebuilding-tables-and-indexes/#internal-section-star-73)
    - [Estimating Data Density](/en/08-rebuilding-tables-and-indexes/#internal-section-star-74)
    - [Freezing](/en/08-rebuilding-tables-and-indexes/#internal-section-star-75)
  - [8.2 Other Rebuilding Methods](/en/08-rebuilding-tables-and-indexes/#internal-section-8-2)
    - [Alternatives to Full Vacuuming](/en/08-rebuilding-tables-and-indexes/#internal-section-star-76)
    - [Reducing Downtime During Rebuilding](/en/08-rebuilding-tables-and-indexes/#internal-section-star-77)
  - [8.3 Precautions](/en/08-rebuilding-tables-and-indexes/#internal-section-8-3)
    - [Read-Only Queries](/en/08-rebuilding-tables-and-indexes/#internal-section-star-78)
    - [Data Updates](/en/08-rebuilding-tables-and-indexes/#internal-section-star-79)
- [Part II Buffer Cache and WAL](/en/part-ii-buffer-cache-and-wal/)
- [9 Buffer Cache](/en/09-buffer-cache/)
  - [9.1 Caching](/en/09-buffer-cache/#internal-section-9-1)
  - [9.2 Buffer Cache Design](/en/09-buffer-cache/#internal-section-9-2)
  - [9.3 Cache Hits](/en/09-buffer-cache/#internal-section-9-3)
  - [9.4 Cache Misses](/en/09-buffer-cache/#internal-section-9-4)
    - [Buffer Search and Eviction](/en/09-buffer-cache/#internal-section-star-81)
  - [9.5 Bulk Eviction](/en/09-buffer-cache/#internal-section-9-5)
  - [9.6 Choosing the Buffer Cache Size](/en/09-buffer-cache/#internal-section-9-6)
  - [9.7 Cache Warming](/en/09-buffer-cache/#internal-section-9-7)
  - [9.8 Local Cache](/en/09-buffer-cache/#internal-section-9-8)
- [10 Write-Ahead Log](/en/10-write-ahead-log/)
  - [10.1 Logging](/en/10-write-ahead-log/#internal-section-10-1)
  - [10.2 WAL Structure](/en/10-write-ahead-log/#internal-section-10-2)
    - [Logical Structure](/en/10-write-ahead-log/#internal-section-star-83)
    - [Physical Structure](/en/10-write-ahead-log/#internal-section-star-84)
  - [10.3 Checkpoint](/en/10-write-ahead-log/#internal-section-10-3)
  - [10.4 Recovery](/en/10-write-ahead-log/#internal-section-10-4)
  - [10.5 Background Writing](/en/10-write-ahead-log/#internal-section-10-5)
  - [10.6 WAL Setup](/en/10-write-ahead-log/#internal-section-10-6)
    - [Configuring Checkpoints](/en/10-write-ahead-log/#internal-section-star-85)
    - [Configuring Background Writing](/en/10-write-ahead-log/#internal-section-star-86)
    - [Monitoring](/en/10-write-ahead-log/#internal-section-star-87)
- [11 WAL Modes](/en/11-wal-modes/)
  - [11.1 Performance](/en/11-wal-modes/#internal-section-11-1)
  - [11.2 Fault Tolerance](/en/11-wal-modes/#internal-section-11-2)
    - [Caching](/en/11-wal-modes/#internal-section-star-88)
    - [Data Corruption](/en/11-wal-modes/#internal-section-star-89)
    - [Non-Atomic Writes](/en/11-wal-modes/#internal-section-star-90)
  - [11.3 WAL Levels](/en/11-wal-modes/#internal-section-11-3)
    - [Minimal](/en/11-wal-modes/#internal-section-star-91)
    - [Replica](/en/11-wal-modes/#internal-section-star-92)
    - [Logical](/en/11-wal-modes/#internal-section-star-93)
- [Part III Locks](/en/part-iii-locks/)
- [12 Relation-Level Locks](/en/12-relation-level-locks/)
  - [12.1 About Locks](/en/12-relation-level-locks/#internal-section-12-1)
  - [12.2 Heavyweight Locks](/en/12-relation-level-locks/#internal-section-12-2)
  - [12.3 Locks on Transaction IDs](/en/12-relation-level-locks/#internal-section-12-3)
  - [12.4 Relation-Level Locks](/en/12-relation-level-locks/#internal-section-12-4)
  - [12.5 Wait Queue](/en/12-relation-level-locks/#internal-section-12-5)
- [13 Row-Level Locks](/en/13-row-level-locks/)
  - [13.1 Lock Design](/en/13-row-level-locks/#internal-section-13-1)
  - [13.2 Row-Level Locking Modes](/en/13-row-level-locks/#internal-section-13-2)
    - [Exclusive Modes](/en/13-row-level-locks/#internal-section-star-94)
    - [Shared Modes](/en/13-row-level-locks/#internal-section-star-95)
  - [13.3 Multitransactions](/en/13-row-level-locks/#internal-section-13-3)
  - [13.4 Wait Queue](/en/13-row-level-locks/#internal-section-13-4)
    - [Exclusive Modes](/en/13-row-level-locks/#internal-section-star-96)
    - [Shared Modes](/en/13-row-level-locks/#internal-section-star-97)
  - [13.5 No-Wait Locks](/en/13-row-level-locks/#internal-section-13-5)
  - [13.6 Deadlocks](/en/13-row-level-locks/#internal-section-13-6)
    - [Deadlocks by Row Updates](/en/13-row-level-locks/#internal-section-star-99)
    - [Deadlocks Between Two UPDATE Statements](/en/13-row-level-locks/#internal-section-star-100)
- [14 Miscellaneous Locks](/en/14-miscellaneous-locks/)
  - [14.1 Non-Object Locks](/en/14-miscellaneous-locks/#internal-section-14-1)
  - [14.2 Relation Extension Locks](/en/14-miscellaneous-locks/#internal-section-14-2)
  - [14.3 Page Locks](/en/14-miscellaneous-locks/#internal-section-14-3)
  - [14.4 Advisory Locks](/en/14-miscellaneous-locks/#internal-section-14-4)
  - [14.5 Predicate Locks](/en/14-miscellaneous-locks/#internal-section-14-5)
- [15 Locks on Memory Structures](/en/15-locks-on-memory-structures/)
  - [15.1 Spinlocks](/en/15-locks-on-memory-structures/#internal-section-15-1)
  - [15.2 Lightweight Locks](/en/15-locks-on-memory-structures/#internal-section-15-2)
  - [15.3 Examples](/en/15-locks-on-memory-structures/#internal-section-15-3)
    - [Buffer Cache](/en/15-locks-on-memory-structures/#internal-section-star-101)
    - [WAL Buffers](/en/15-locks-on-memory-structures/#internal-section-star-102)
  - [15.4 Monitoring Waits](/en/15-locks-on-memory-structures/#internal-section-15-4)
  - [15.5 Sampling](/en/15-locks-on-memory-structures/#internal-section-15-5)
- [Part IV Query Execution](/en/part-iv-query-execution/)
- [16 Query Execution Stages](/en/16-query-execution-stages/)
  - [16.1 Demo Database](/en/16-query-execution-stages/#internal-section-16-1)
  - [16.2 Simple Query Protocol](/en/16-query-execution-stages/#internal-section-16-2)
    - [Parsing](/en/16-query-execution-stages/#internal-section-star-104)
    - [Transformation](/en/16-query-execution-stages/#internal-section-star-107)
    - [Planning](/en/16-query-execution-stages/#internal-section-star-108)
    - [Execution](/en/16-query-execution-stages/#internal-section-star-117)
  - [16.3 Extended Query Protocol](/en/16-query-execution-stages/#internal-section-16-3)
    - [Preparation](/en/16-query-execution-stages/#internal-section-star-119)
    - [Parameter Binding](/en/16-query-execution-stages/#internal-section-star-120)
    - [Planning and Execution](/en/16-query-execution-stages/#internal-section-star-121)
    - [Getting the Results](/en/16-query-execution-stages/#internal-section-star-122)
- [17 Statistics](/en/17-statistics/)
  - [17.1 Basic Statistics](/en/17-statistics/#internal-section-17-1)
  - [17.2 NULL Values](/en/17-statistics/#internal-section-17-2)
  - [17.3 Distinct Values](/en/17-statistics/#internal-section-17-3)
  - [17.4 Most Common Values](/en/17-statistics/#internal-section-17-4)
  - [17.5 Histogram](/en/17-statistics/#internal-section-17-5)
  - [17.6 Statistics for Non-Scalar Data Types](/en/17-statistics/#internal-section-17-6)
  - [17.7 Average Field Width](/en/17-statistics/#internal-section-17-7)
  - [17.8 Correlation](/en/17-statistics/#internal-section-17-8)
  - [17.9 Expression Statistics](/en/17-statistics/#internal-section-17-9)
    - [Extended Expression Statistics](/en/17-statistics/#internal-section-star-124)
    - [Statistics for Expression Indexes](/en/17-statistics/#internal-section-star-125)
  - [17.10 Multivariate Statistics](/en/17-statistics/#internal-section-17-10)
    - [Functional Dependencies Between Columns](/en/17-statistics/#internal-section-star-126)
    - [Multivariate Number of Distinct Values](/en/17-statistics/#internal-section-star-127)
    - [Multivariate MCV Lists](/en/17-statistics/#internal-section-star-128)
- [18 Table Access Methods](/en/18-table-access-methods/)
  - [18.1 Pluggable Storage Engines](/en/18-table-access-methods/#internal-section-18-1)
  - [18.2 Sequential Scans](/en/18-table-access-methods/#internal-section-18-2)
    - [Cost Estimation](/en/18-table-access-methods/#internal-section-star-129)
  - [18.3 Parallel Plans](/en/18-table-access-methods/#internal-section-18-3)
  - [18.4 Parallel Sequential Scans](/en/18-table-access-methods/#internal-section-18-4)
    - [Cost Estimation](/en/18-table-access-methods/#internal-section-star-130)
  - [18.5 Parallel Execution Limitations](/en/18-table-access-methods/#internal-section-18-5)
    - [Number of Background Workers](/en/18-table-access-methods/#internal-section-star-131)
    - [Non-Parallelizable Queries](/en/18-table-access-methods/#internal-section-star-132)
    - [Parallel Restricted Queries](/en/18-table-access-methods/#internal-section-star-133)
- [19 Index Access Methods](/en/19-index-access-methods/)
  - [19.1 Indexes and Extensibility](/en/19-index-access-methods/#internal-section-19-1)
  - [19.2 Operator Classes and Families](/en/19-index-access-methods/#internal-section-19-2)
    - [Operator Classes](/en/19-index-access-methods/#internal-section-star-137)
    - [Operator Families](/en/19-index-access-methods/#internal-section-star-138)
  - [19.3 Indexing Engine Interface](/en/19-index-access-methods/#internal-section-19-3)
    - [Access Method Properties](/en/19-index-access-methods/#internal-section-star-139)
    - [Index-Level Properties](/en/19-index-access-methods/#internal-section-star-140)
    - [Column-Level Properties](/en/19-index-access-methods/#internal-section-star-141)
- [20 Index Scans](/en/20-index-scans/)
  - [20.1 Regular Index Scans](/en/20-index-scans/#internal-section-20-1)
    - [Cost Estimation](/en/20-index-scans/#internal-section-star-142)
    - [Good Scenario: High Correlation](/en/20-index-scans/#internal-section-star-143)
    - [Bad Scenario: Low Correlation](/en/20-index-scans/#internal-section-star-144)
  - [20.2 Index-Only Scans](/en/20-index-scans/#internal-section-20-2)
    - [Indexes with the Include Clause](/en/20-index-scans/#internal-section-star-145)
  - [20.3 Bitmap Scans](/en/20-index-scans/#internal-section-20-3)
    - [Bitmap Accuracy](/en/20-index-scans/#internal-section-star-146)
    - [Operations on Bitmaps](/en/20-index-scans/#internal-section-star-147)
    - [Cost Estimation](/en/20-index-scans/#internal-section-star-148)
  - [20.4 Parallel Index Scans](/en/20-index-scans/#internal-section-20-4)
  - [20.5 Comparison of Various Access Methods](/en/20-index-scans/#internal-section-20-5)
- [21 Nested Loop](/en/21-nested-loop/)
  - [21.1 Join Types and Methods](/en/21-nested-loop/#internal-section-21-1)
  - [21.2 Nested Loop Joins](/en/21-nested-loop/#internal-section-21-2)
    - [Cartesian Product](/en/21-nested-loop/#internal-section-star-149)
    - [Parameterized Joins](/en/21-nested-loop/#internal-section-star-152)
    - [Caching Rows (Memoization)](/en/21-nested-loop/#internal-section-star-155)
    - [Outer Joins](/en/21-nested-loop/#internal-section-star-157)
    - [Anti- and Semi-joins](/en/21-nested-loop/#internal-section-star-158)
    - [Non-Equi-joins](/en/21-nested-loop/#internal-section-star-161)
    - [Parallel Mode](/en/21-nested-loop/#internal-section-star-162)
- [22 Hashing](/en/22-hashing/)
  - [22.1 Hash Joins](/en/22-hashing/#internal-section-22-1)
    - [One-Pass Hash Joins](/en/22-hashing/#internal-section-star-163)
    - [Two-Pass Hash Joins](/en/22-hashing/#internal-section-star-165)
    - [Dynamic Adjustments](/en/22-hashing/#internal-section-star-166)
    - [Using Hash Joins in Parallel Plans](/en/22-hashing/#internal-section-star-168)
    - [Parallel One-Pass Hash Joins](/en/22-hashing/#internal-section-star-169)
    - [Parallel Two-Pass Hash Joins](/en/22-hashing/#internal-section-star-170)
    - [Modifications](/en/22-hashing/#internal-section-star-171)
  - [22.2 Distinct Values and Grouping](/en/22-hashing/#internal-section-22-2)
- [23 Sorting and Merging](/en/23-sorting-and-merging/)
  - [23.1 Merge Joins](/en/23-sorting-and-merging/#internal-section-23-1)
    - [Merging Sorted Sets](/en/23-sorting-and-merging/#internal-section-star-172)
    - [Parallel Mode](/en/23-sorting-and-merging/#internal-section-star-174)
    - [Modifications](/en/23-sorting-and-merging/#internal-section-star-175)
  - [23.2 Sorting](/en/23-sorting-and-merging/#internal-section-23-2)
    - [Quicksort](/en/23-sorting-and-merging/#internal-section-star-176)
    - [Top-N Heapsort](/en/23-sorting-and-merging/#internal-section-star-178)
    - [External Sorting](/en/23-sorting-and-merging/#internal-section-star-180)
    - [Incremental Sorting](/en/23-sorting-and-merging/#internal-section-star-182)
    - [Parallel Mode](/en/23-sorting-and-merging/#internal-section-star-184)
  - [23.3 Distinct Values and Grouping](/en/23-sorting-and-merging/#internal-section-23-3)
  - [23.4 Comparison of Join Methods](/en/23-sorting-and-merging/#internal-section-23-4)
- [Part V Types of Indexes](/en/part-v-types-of-indexes/)
- [24 Hash](/en/24-hash/)
  - [24.1 Overview](/en/24-hash/#internal-section-24-1)
  - [24.2 Page Layout](/en/24-hash/#internal-section-24-2)
  - [24.3 Operator Class](/en/24-hash/#internal-section-24-3)
  - [24.4 Properties](/en/24-hash/#internal-section-24-4)
    - [Access Method Properties](/en/24-hash/#internal-section-star-186)
    - [Index-Level Properties](/en/24-hash/#internal-section-star-187)
    - [Column-Level Properties](/en/24-hash/#internal-section-star-188)
- [25 B-tree](/en/25-b-tree/)
  - [25.1 Overview](/en/25-b-tree/#internal-section-25-1)
  - [25.2 Search and Insertions](/en/25-b-tree/#internal-section-25-2)
    - [Search by Equality](/en/25-b-tree/#internal-section-star-189)
    - [Search by Inequality](/en/25-b-tree/#internal-section-star-190)
    - [Search by Range](/en/25-b-tree/#internal-section-star-191)
    - [Insertions](/en/25-b-tree/#internal-section-star-192)
  - [25.3 Page Layout](/en/25-b-tree/#internal-section-25-3)
    - [Deduplication](/en/25-b-tree/#internal-section-star-193)
    - [Compact Storage of Inner Index Entries](/en/25-b-tree/#internal-section-star-194)
  - [25.4 Operator Class](/en/25-b-tree/#internal-section-25-4)
    - [Comparison Semantics](/en/25-b-tree/#internal-section-star-195)
    - [Multicolumn Indexes and Sorting](/en/25-b-tree/#internal-section-star-196)
  - [25.5 Properties](/en/25-b-tree/#internal-section-25-5)
    - [Access Method Properties](/en/25-b-tree/#internal-section-star-197)
    - [Index-Level Properties](/en/25-b-tree/#internal-section-star-198)
    - [Column-Level Properties](/en/25-b-tree/#internal-section-star-199)
- [26 GiST](/en/26-gist/)
  - [26.1 Overview](/en/26-gist/#internal-section-26-1)
  - [26.2 R-Trees for Points](/en/26-gist/#internal-section-26-2)
    - [Page Layout](/en/26-gist/#internal-section-star-200)
    - [Operator Class](/en/26-gist/#internal-section-star-201)
    - [Search for Contained Elements](/en/26-gist/#internal-section-star-202)
    - [Nearest Neighbor Search](/en/26-gist/#internal-section-star-203)
    - [Insertion](/en/26-gist/#internal-section-star-204)
    - [Exclusion Constraints](/en/26-gist/#internal-section-star-205)
    - [Properties](/en/26-gist/#internal-section-star-206)
  - [26.3 RD-Trees for Full-Text Search](/en/26-gist/#internal-section-26-3)
    - [About Full-Text Search](/en/26-gist/#internal-section-star-210)
    - [Indexing tsvector Data](/en/26-gist/#internal-section-star-211)
    - [Properties](/en/26-gist/#internal-section-star-212)
  - [26.4 Other Data Types](/en/26-gist/#internal-section-26-4)
- [27 SP-GiST](/en/27-sp-gist/)
  - [27.1 Overview](/en/27-sp-gist/#internal-section-27-1)
  - [27.2 Quadtrees for Points](/en/27-sp-gist/#internal-section-27-2)
    - [Operator Class](/en/27-sp-gist/#internal-section-star-213)
    - [Page Layout](/en/27-sp-gist/#internal-section-star-214)
    - [Search](/en/27-sp-gist/#internal-section-star-215)
    - [Insertion](/en/27-sp-gist/#internal-section-star-216)
    - [Properties](/en/27-sp-gist/#internal-section-star-217)
  - [27.3 K-Dimensional Trees for Points](/en/27-sp-gist/#internal-section-27-3)
  - [27.4 Radix Trees for Strings](/en/27-sp-gist/#internal-section-27-4)
    - [Operator Class](/en/27-sp-gist/#internal-section-star-221)
    - [Search](/en/27-sp-gist/#internal-section-star-222)
    - [Insertion](/en/27-sp-gist/#internal-section-star-223)
    - [Properties](/en/27-sp-gist/#internal-section-star-225)
  - [27.5 Other Data Types](/en/27-sp-gist/#internal-section-27-5)
- [28 GIN](/en/28-gin/)
  - [28.1 Overview](/en/28-gin/#internal-section-28-1)
  - [28.2 Index for Full-Text Search](/en/28-gin/#internal-section-28-2)
    - [Page Layout](/en/28-gin/#internal-section-star-226)
    - [Operator Class](/en/28-gin/#internal-section-star-227)
    - [Search](/en/28-gin/#internal-section-star-228)
    - [Frequent and Rare Lexemes](/en/28-gin/#internal-section-star-229)
    - [Insertions](/en/28-gin/#internal-section-star-230)
    - [Limiting Result Set Size](/en/28-gin/#internal-section-star-231)
    - [Properties](/en/28-gin/#internal-section-star-232)
    - [GIN Limitations and RUM Index](/en/28-gin/#internal-section-star-236)
  - [28.3 Trigrams](/en/28-gin/#internal-section-28-3)
  - [28.4 Indexing Arrays](/en/28-gin/#internal-section-28-4)
  - [28.5 Indexing JSON](/en/28-gin/#internal-section-28-5)
    - [jsonb_ops Operator Class](/en/28-gin/#internal-section-star-237)
    - [jsonb_path_ops Operator Class](/en/28-gin/#internal-section-star-238)
  - [28.6 Indexing Other Data Types](/en/28-gin/#internal-section-28-6)
- [29 BRIN](/en/29-brin/)
  - [29.1 Overview](/en/29-brin/#internal-section-29-1)
  - [29.2 Example](/en/29-brin/#internal-section-29-2)
  - [29.3 Page Layout](/en/29-brin/#internal-section-29-3)
  - [29.4 Search](/en/29-brin/#internal-section-29-4)
  - [29.5 Summary Information Updates](/en/29-brin/#internal-section-29-5)
    - [Value Insertion](/en/29-brin/#internal-section-star-239)
    - [Range Summarization](/en/29-brin/#internal-section-star-240)
  - [29.6 Minmax Classes](/en/29-brin/#internal-section-29-6)
    - [Choosing Columns to be Indexed](/en/29-brin/#internal-section-star-241)
    - [Range Size and Search Efficiency](/en/29-brin/#internal-section-star-242)
    - [Properties](/en/29-brin/#internal-section-star-243)
  - [29.7 Minmax-Multi Classes](/en/29-brin/#internal-section-29-7)
  - [29.8 Inclusion Classes](/en/29-brin/#internal-section-29-8)
  - [29.9 Bloom Classes](/en/29-brin/#internal-section-29-9)
- [Conclusion](/en/conclusion/)
- [Index](/en/index/)
  - [A](/en/index/#index-a)
  - [B](/en/index/#index-b)
  - [C](/en/index/#index-c)
  - [D](/en/index/#index-d)
  - [E](/en/index/#index-e)
  - [F](/en/index/#index-f)
  - [G](/en/index/#index-g)
  - [H](/en/index/#index-h)
  - [I](/en/index/#index-i)
  - [J](/en/index/#index-j)
  - [K](/en/index/#index-k)
  - [L](/en/index/#index-l)
  - [M](/en/index/#index-m)
  - [N](/en/index/#index-n)
  - [O](/en/index/#index-o)
  - [P](/en/index/#index-p)
  - [Q](/en/index/#index-q)
  - [R](/en/index/#index-r)
  - [S](/en/index/#index-s)
  - [T](/en/index/#index-t)
- [Publication and Credits](/en/publication/)

## Figures {#figures}

- [Figure 1.1](/en/01-introduction/#internal-fig-1-1) — Databases
- [Figure 1.2](/en/01-introduction/#internal-fig-1-2) — Tablespaces
- [Figure 1.3](/en/01-introduction/#internal-fig-1-3) — Files and Forks
- [Figure 1.4](/en/01-introduction/#internal-fig-1-4) — 1\.2 Processes and Memory
- [Figure 3.1](/en/03-pages-and-tuples/#internal-fig-3-1) — Page Header
- [Figure 4.1](/en/04-snapshots/#internal-fig-4-1) — 4\.1 What is a Snapshot\?
- [Figure 4.2](/en/04-snapshots/#internal-fig-4-2) — 4\.2 Row Version Visibility
- [Figure 4.3](/en/04-snapshots/#internal-fig-4-3) — 4\.3 Snapshot Structure
- [Figure 4.4](/en/04-snapshots/#internal-fig-4-4) — 4\.3 Snapshot Structure
- [Figure 4.5](/en/04-snapshots/#internal-fig-4-5) — 4\.5 Transaction Horizon
- [Figure 7.1](/en/07-freezing/#internal-fig-7-1) — 7\.1 Transaction ID Wraparound
- [Figure 7.2](/en/07-freezing/#internal-fig-7-2) — 7\.2 Tuple Freezing and Visibility Rules
- [Figure 7.3](/en/07-freezing/#internal-fig-7-3) — Age for Forced Autovacuum
- [Figure 9.1](/en/09-buffer-cache/#internal-fig-9-1) — 9\.2 Buffer Cache Design
- [Figure 9.2](/en/09-buffer-cache/#internal-fig-9-2) — 9\.3 Cache Hits
- [Figure 9.3](/en/09-buffer-cache/#internal-fig-9-3) — Buffer Search and Eviction
- [Figure 10.1](/en/10-write-ahead-log/#internal-fig-10-1) — 10\.3 Checkpoint
- [Figure 10.2](/en/10-write-ahead-log/#internal-fig-10-2) — 10\.3 Checkpoint
- [Figure 10.3](/en/10-write-ahead-log/#internal-fig-10-3) — Configuring Checkpoints
- [Figure 13.1](/en/13-row-level-locks/#internal-fig-13-1) — Exclusive Modes
- [Figure 13.2](/en/13-row-level-locks/#internal-fig-13-2) — Exclusive Modes
- [Figure 13.3](/en/13-row-level-locks/#internal-fig-13-3) — Exclusive Modes
- [Figure 13.4](/en/13-row-level-locks/#internal-fig-13-4) — Shared Modes
- [Figure 13.5](/en/13-row-level-locks/#internal-fig-13-5) — 13\.6 Deadlocks
- [Figure 15.1](/en/15-locks-on-memory-structures/#internal-fig-15-1) — Buffer Cache
- [Figure 15.2](/en/15-locks-on-memory-structures/#internal-fig-15-2) — WAL Buffers
- [Figure 16.1](/en/16-query-execution-stages/#internal-fig-16-1) — 16\.2 Simple Query Protocol
- [Figure 16.2](/en/16-query-execution-stages/#internal-fig-16-2) — Parsing
- [Figure 16.3](/en/16-query-execution-stages/#internal-fig-16-3) — Transformation
- [Figure 16.4](/en/16-query-execution-stages/#internal-fig-16-4) — Planning
- [Figure 16.5](/en/16-query-execution-stages/#internal-fig-16-5) — Planning
- [Figure 16.6](/en/16-query-execution-stages/#internal-fig-16-6) — Planning
- [Figure 16.7](/en/16-query-execution-stages/#internal-fig-16-7) — Planning
- [Figure 16.8](/en/16-query-execution-stages/#internal-fig-16-8) — Execution
- [Figure 17.1](/en/17-statistics/#internal-fig-17-1) — 17\.3 Distinct Values
- [Figure 17.2](/en/17-statistics/#internal-fig-17-2) — 17\.4 Most Common Values
- [Figure 17.3](/en/17-statistics/#internal-fig-17-3) — 17\.5 Histogram
- [Figure 17.4](/en/17-statistics/#internal-fig-17-4) — 17\.5 Histogram
- [Figure 18.1](/en/18-table-access-methods/#internal-fig-18-1) — 18\.2 Sequential Scans
- [Figure 18.2](/en/18-table-access-methods/#internal-fig-18-2) — 18\.3 Parallel Plans
- [Figure 18.3](/en/18-table-access-methods/#internal-fig-18-3) — Cost Estimation
- [Figure 19.1](/en/19-index-access-methods/#internal-fig-19-1) — 19\.1 Indexes and Extensibility
- [Figure 20.1](/en/20-index-scans/#internal-fig-20-1) — Good Scenario\: High Correlation
- [Figure 20.2](/en/20-index-scans/#internal-fig-20-2) — Bad Scenario\: Low Correlation
- [Figure 20.3](/en/20-index-scans/#internal-fig-20-3) — Bad Scenario\: Low Correlation
- [Figure 20.4](/en/20-index-scans/#internal-fig-20-4) — Cost Estimation
- [Figure 20.5](/en/20-index-scans/#internal-fig-20-5) — Cost Estimation
- [Figure 20.6](/en/20-index-scans/#internal-fig-20-6) — 20\.5 Comparison of Various Access Methods
- [Figure 22.1](/en/22-hashing/#internal-fig-22-1) — One\-Pass Hash Joins
- [Figure 22.2](/en/22-hashing/#internal-fig-22-2) — One\-Pass Hash Joins
- [Figure 22.3](/en/22-hashing/#internal-fig-22-3) — Two\-Pass Hash Joins
- [Figure 22.4](/en/22-hashing/#internal-fig-22-4) — Two\-Pass Hash Joins
- [Figure 22.5](/en/22-hashing/#internal-fig-22-5) — Two\-Pass Hash Joins
- [Figure 22.6](/en/22-hashing/#internal-fig-22-6) — Parallel One\-Pass Hash Joins
- [Figure 22.7](/en/22-hashing/#internal-fig-22-7) — Parallel One\-Pass Hash Joins
- [Figure 22.8](/en/22-hashing/#internal-fig-22-8) — Parallel Two\-Pass Hash Joins
- [Figure 22.9](/en/22-hashing/#internal-fig-22-9) — Parallel Two\-Pass Hash Joins
- [Figure 22.10](/en/22-hashing/#internal-fig-22-10) — Parallel Two\-Pass Hash Joins
- [Figure 22.11](/en/22-hashing/#internal-fig-22-11) — Parallel Two\-Pass Hash Joins
- [Figure 23.1](/en/23-sorting-and-merging/#internal-fig-23-1) — External Sorting
- [Figure 23.2](/en/23-sorting-and-merging/#internal-fig-23-2) — External Sorting
- [Figure 23.3](/en/23-sorting-and-merging/#internal-fig-23-3) — External Sorting
- [Figure 23.4](/en/23-sorting-and-merging/#internal-fig-23-4) — External Sorting
- [Figure 23.5](/en/23-sorting-and-merging/#internal-fig-23-5) — 23\.4 Comparison of Join Methods
- [Figure 24.1](/en/24-hash/#internal-fig-24-1) — 24\.2 Page Layout
- [Figure 24.2](/en/24-hash/#internal-fig-24-2) — 24\.2 Page Layout
- [Figure 24.3](/en/24-hash/#internal-fig-24-3) — 24\.2 Page Layout
- [Figure 25.1](/en/25-b-tree/#internal-fig-25-1) — 25\.1 Overview
- [Figure 25.2](/en/25-b-tree/#internal-fig-25-2) — Search by Equality
- [Figure 25.3](/en/25-b-tree/#internal-fig-25-3) — Search by Inequality
- [Figure 25.4](/en/25-b-tree/#internal-fig-25-4) — Search by Range
- [Figure 25.5](/en/25-b-tree/#internal-fig-25-5) — Insertions
- [Figure 25.6](/en/25-b-tree/#internal-fig-25-6) — 25\.3 Page Layout
- [Figure 26.1](/en/26-gist/#internal-fig-26-1) — 26\.2 R\-Trees for Points
- [Figure 26.2](/en/26-gist/#internal-fig-26-2) — 26\.2 R\-Trees for Points
- [Figure 26.3](/en/26-gist/#internal-fig-26-3) — 26\.2 R\-Trees for Points
- [Figure 26.4](/en/26-gist/#internal-fig-26-4) — Search for Contained Elements
- [Figure 26.5](/en/26-gist/#internal-fig-26-5) — Search for Contained Elements
- [Figure 26.6](/en/26-gist/#internal-fig-26-6) — Search for Contained Elements
- [Figure 26.7](/en/26-gist/#internal-fig-26-7) — Search for Contained Elements
- [Figure 26.8](/en/26-gist/#internal-fig-26-8) — Nearest Neighbor Search
- [Figure 26.9](/en/26-gist/#internal-fig-26-9) — Nearest Neighbor Search
- [Figure 26.10](/en/26-gist/#internal-fig-26-10) — Nearest Neighbor Search
- [Figure 26.11](/en/26-gist/#internal-fig-26-11) — Insertion
- [Figure 26.12](/en/26-gist/#internal-fig-26-12) — Insertion
- [Figure 26.13](/en/26-gist/#internal-fig-26-13) — Indexing tsvector Data
- [Figure 26.14](/en/26-gist/#internal-fig-26-14) — Indexing tsvector Data
- [Figure 26.15](/en/26-gist/#internal-fig-26-15) — Indexing tsvector Data
- [Figure 26.16](/en/26-gist/#internal-fig-26-16) — Indexing tsvector Data
- [Figure 27.1](/en/27-sp-gist/#internal-fig-27-1) — 27\.2 Quadtrees for Points
- [Figure 27.2](/en/27-sp-gist/#internal-fig-27-2) — 27\.2 Quadtrees for Points
- [Figure 27.3](/en/27-sp-gist/#internal-fig-27-3) — 27\.2 Quadtrees for Points
- [Figure 27.4](/en/27-sp-gist/#internal-fig-27-4) — Operator Class
- [Figure 27.5](/en/27-sp-gist/#internal-fig-27-5) — Operator Class
- [Figure 27.6](/en/27-sp-gist/#internal-fig-27-6) — Page Layout
- [Figure 27.7](/en/27-sp-gist/#internal-fig-27-7) — Search
- [Figure 27.8](/en/27-sp-gist/#internal-fig-27-8) — Search
- [Figure 27.9](/en/27-sp-gist/#internal-fig-27-9) — Insertion
- [Figure 27.10](/en/27-sp-gist/#internal-fig-27-10) — Insertion
- [Figure 27.11](/en/27-sp-gist/#internal-fig-27-11) — Insertion
- [Figure 27.12](/en/27-sp-gist/#internal-fig-27-12) — Insertion
- [Figure 27.13](/en/27-sp-gist/#internal-fig-27-13) — 27\.3 K\-Dimensional Trees for Points
- [Figure 27.14](/en/27-sp-gist/#internal-fig-27-14) — 27\.3 K\-Dimensional Trees for Points
- [Figure 27.15](/en/27-sp-gist/#internal-fig-27-15) — 27\.3 K\-Dimensional Trees for Points
- [Figure 27.16](/en/27-sp-gist/#internal-fig-27-16) — 27\.4 Radix Trees for Strings
- [Figure 27.17](/en/27-sp-gist/#internal-fig-27-17) — Search
- [Figure 27.18](/en/27-sp-gist/#internal-fig-27-18) — Insertion
- [Figure 27.19](/en/27-sp-gist/#internal-fig-27-19) — Insertion
- [Figure 27.20](/en/27-sp-gist/#internal-fig-27-20) — Insertion
- [Figure 28.1](/en/28-gin/#internal-fig-28-1) — 28\.2 Index for Full\-Text Search
- [Figure 28.2](/en/28-gin/#internal-fig-28-2) — Search
- [Figure 28.3](/en/28-gin/#internal-fig-28-3) — Frequent and Rare Lexemes
- [Figure 28.4](/en/28-gin/#internal-fig-28-4) — Insertions
- [Figure 28.5](/en/28-gin/#internal-fig-28-5) — jsonb\_ops Operator Class
- [Figure 28.6](/en/28-gin/#internal-fig-28-6) — jsonb\_path\_ops Operator Class
- [Figure 29.1](/en/29-brin/#internal-fig-29-1) — 29\.3 Page Layout
- [Figure 29.2](/en/29-brin/#internal-fig-29-2) — Value Insertion
- [Figure 29.3](/en/29-brin/#internal-fig-29-3) — Range Size and Search Efficiency
- [Figure 29.4](/en/29-brin/#internal-fig-29-4) — 29\.7 Minmax\-Multi Classes
- [Figure 29.5](/en/29-brin/#internal-fig-29-5) — 29\.7 Minmax\-Multi Classes
- [Figure 29.6](/en/29-brin/#internal-fig-29-6) — 29\.9 Bloom Classes

## Tables {#tables}

- [Table 2.1](/en/02-isolation/#internal-table-2-1) — Isolation levels and anomalies in the SQL standard
- [Table 2.2](/en/02-isolation/#internal-table-2-2) — Isolation levels and anomalies in PostgreSQL
- [Table 12.1](/en/12-relation-level-locks/#internal-table-12-1) — Transaction ID lock compatibility \(× marks a conflict\)
- [Table 12.2](/en/12-relation-level-locks/#internal-table-12-2) — Relation\-level lock compatibility \(× marks a conflict\)
- [Table 13.1](/en/13-row-level-locks/#internal-table-13-1) — Row\-level lock compatibility \(× marks a conflict\)
- [Table 18.1](/en/18-table-access-methods/#internal-table-18-1) — Table size and number of parallel processes
- [Table 26.1](/en/26-gist/#internal-table-26-1) — Lexeme signatures
- [Table 26.2](/en/26-gist/#internal-table-26-2) — Document signatures
- [Table 28.1](/en/28-gin/#internal-table-28-1) — Consistency function for “everywher” or “oink”
- [Table 28.2](/en/28-gin/#internal-table-28-2) — Consistency function for mandatory and optional lexemes

## About this edition {#edition}

© Postgres Professional, 2022–2023. The English text and illustrations are reproduced from the [PostgreSQL 14 · 2023 PDF](/downloads/postgresql_internals-14_en.pdf). This web edition keeps the original chapter order, examples, and version-specific behavior. Figure and table numbers are added for web navigation; the PDF’s diagrams and tables are unnumbered. Original page references link to their corresponding locations in the chapters.

[Publisher and original editions](https://postgrespro.com/community/books/internals) · [Publication and credits](/en/publication/)

---

Section pages:

- [About This Book](/en/about-this-book/)
- [Introduction](/en/01-introduction/)
- [Part I Isolation and MVCC](/en/part-i-isolation-and-mvcc/)
- [Isolation](/en/02-isolation/)
- [Pages and Tuples](/en/03-pages-and-tuples/)
- [Snapshots](/en/04-snapshots/)
- [Page Pruning and HOT Updates](/en/05-page-pruning-and-hot-updates/)
- [Vacuum and Autovacuum](/en/06-vacuum-and-autovacuum/)
- [Freezing](/en/07-freezing/)
- [Rebuilding Tables and Indexes](/en/08-rebuilding-tables-and-indexes/)
- [Part II Buffer Cache and WAL](/en/part-ii-buffer-cache-and-wal/)
- [Buffer Cache](/en/09-buffer-cache/)
- [Write-Ahead Log](/en/10-write-ahead-log/)
- [WAL Modes](/en/11-wal-modes/)
- [Part III Locks](/en/part-iii-locks/)
- [Relation-Level Locks](/en/12-relation-level-locks/)
- [Row-Level Locks](/en/13-row-level-locks/)
- [Miscellaneous Locks](/en/14-miscellaneous-locks/)
- [Locks on Memory Structures](/en/15-locks-on-memory-structures/)
- [Part IV Query Execution](/en/part-iv-query-execution/)
- [Query Execution Stages](/en/16-query-execution-stages/)
- [Statistics](/en/17-statistics/)
- [Table Access Methods](/en/18-table-access-methods/)
- [Index Access Methods](/en/19-index-access-methods/)
- [Index Scans](/en/20-index-scans/)
- [Nested Loop](/en/21-nested-loop/)
- [Hashing](/en/22-hashing/)
- [Sorting and Merging](/en/23-sorting-and-merging/)
- [Part V Types of Indexes](/en/part-v-types-of-indexes/)
- [Hash](/en/24-hash/)
- [B-tree](/en/25-b-tree/)
- [GiST](/en/26-gist/)
- [SP-GiST](/en/27-sp-gist/)
- [GIN](/en/28-gin/)
- [BRIN](/en/29-brin/)
- [Conclusion](/en/conclusion/)
- [Index](/en/index/)
- [Publication and Credits](/en/publication/)