PostgreSQL 14 Internals

Egor Rogov
PostgreSQL 14 · 2023

Start reading · Print the whole book · Original PDF

Contents

Figures

1. Figure 1.1 — Databases
2. Figure 1.2 — Tablespaces
3. Figure 1.3 — Files and Forks
4. Figure 1.4 — 1.2 Processes and Memory
5. Figure 3.1 — Page Header
6. Figure 4.1 — 4.1 What is a Snapshot?
7. Figure 4.2 — 4.2 Row Version Visibility
8. Figure 4.3 — 4.3 Snapshot Structure
9. Figure 4.4 — 4.3 Snapshot Structure
10. Figure 4.5 — 4.5 Transaction Horizon
11. Figure 7.1 — 7.1 Transaction ID Wraparound
12. Figure 7.2 — 7.2 Tuple Freezing and Visibility Rules
13. Figure 7.3 — Age for Forced Autovacuum
14. Figure 9.1 — 9.2 Buffer Cache Design
15. Figure 9.2 — 9.3 Cache Hits
16. Figure 9.3 — Buffer Search and Eviction
17. Figure 10.1 — 10.3 Checkpoint
18. Figure 10.2 — 10.3 Checkpoint
19. Figure 10.3 — Configuring Checkpoints
20. Figure 13.1 — Exclusive Modes
21. Figure 13.2 — Exclusive Modes
22. Figure 13.3 — Exclusive Modes
23. Figure 13.4 — Shared Modes
24. Figure 13.5 — 13.6 Deadlocks
25. Figure 15.1 — Buffer Cache
26. Figure 15.2 — WAL Buffers
27. Figure 16.1 — 16.2 Simple Query Protocol
28. Figure 16.2 — Parsing
29. Figure 16.3 — Transformation
30. Figure 16.4 — Planning
31. Figure 16.5 — Planning
32. Figure 16.6 — Planning
33. Figure 16.7 — Planning
34. Figure 16.8 — Execution
35. Figure 17.1 — 17.3 Distinct Values
36. Figure 17.2 — 17.4 Most Common Values
37. Figure 17.3 — 17.5 Histogram
38. Figure 17.4 — 17.5 Histogram
39. Figure 18.1 — 18.2 Sequential Scans
40. Figure 18.2 — 18.3 Parallel Plans
41. Figure 18.3 — Cost Estimation
42. Figure 19.1 — 19.1 Indexes and Extensibility
43. Figure 20.1 — Good Scenario: High Correlation
44. Figure 20.2 — Bad Scenario: Low Correlation
45. Figure 20.3 — Bad Scenario: Low Correlation
46. Figure 20.4 — Cost Estimation
47. Figure 20.5 — Cost Estimation
48. Figure 20.6 — 20.5 Comparison of Various Access Methods
49. Figure 22.1 — One-Pass Hash Joins
50. Figure 22.2 — One-Pass Hash Joins
51. Figure 22.3 — Two-Pass Hash Joins
52. Figure 22.4 — Two-Pass Hash Joins
53. Figure 22.5 — Two-Pass Hash Joins
54. Figure 22.6 — Parallel One-Pass Hash Joins
55. Figure 22.7 — Parallel One-Pass Hash Joins
56. Figure 22.8 — Parallel Two-Pass Hash Joins
57. Figure 22.9 — Parallel Two-Pass Hash Joins
58. Figure 22.10 — Parallel Two-Pass Hash Joins
59. Figure 22.11 — Parallel Two-Pass Hash Joins
60. Figure 23.1 — External Sorting
61. Figure 23.2 — External Sorting
62. Figure 23.3 — External Sorting
63. Figure 23.4 — External Sorting
64. Figure 23.5 — 23.4 Comparison of Join Methods
65. Figure 24.1 — 24.2 Page Layout
66. Figure 24.2 — 24.2 Page Layout
67. Figure 24.3 — 24.2 Page Layout
68. Figure 25.1 — 25.1 Overview
69. Figure 25.2 — Search by Equality
70. Figure 25.3 — Search by Inequality
71. Figure 25.4 — Search by Range
72. Figure 25.5 — Insertions
73. Figure 25.6 — 25.3 Page Layout
74. Figure 26.1 — 26.2 R-Trees for Points
75. Figure 26.2 — 26.2 R-Trees for Points
76. Figure 26.3 — 26.2 R-Trees for Points
77. Figure 26.4 — Search for Contained Elements
78. Figure 26.5 — Search for Contained Elements
79. Figure 26.6 — Search for Contained Elements
80. Figure 26.7 — Search for Contained Elements
81. Figure 26.8 — Nearest Neighbor Search
82. Figure 26.9 — Nearest Neighbor Search
83. Figure 26.10 — Nearest Neighbor Search
84. Figure 26.11 — Insertion
85. Figure 26.12 — Insertion
86. Figure 26.13 — Indexing tsvector Data
87. Figure 26.14 — Indexing tsvector Data
88. Figure 26.15 — Indexing tsvector Data
89. Figure 26.16 — Indexing tsvector Data
90. Figure 27.1 — 27.2 Quadtrees for Points
91. Figure 27.2 — 27.2 Quadtrees for Points
92. Figure 27.3 — 27.2 Quadtrees for Points
93. Figure 27.4 — Operator Class
94. Figure 27.5 — Operator Class
95. Figure 27.6 — Page Layout
96. Figure 27.7 — Search
97. Figure 27.8 — Search
98. Figure 27.9 — Insertion
99. Figure 27.10 — Insertion
100. Figure 27.11 — Insertion
101. Figure 27.12 — Insertion
102. Figure 27.13 — 27.3 K-Dimensional Trees for Points
103. Figure 27.14 — 27.3 K-Dimensional Trees for Points
104. Figure 27.15 — 27.3 K-Dimensional Trees for Points
105. Figure 27.16 — 27.4 Radix Trees for Strings
106. Figure 27.17 — Search
107. Figure 27.18 — Insertion
108. Figure 27.19 — Insertion
109. Figure 27.20 — Insertion
110. Figure 28.1 — 28.2 Index for Full-Text Search
111. Figure 28.2 — Search
112. Figure 28.3 — Frequent and Rare Lexemes
113. Figure 28.4 — Insertions
114. Figure 28.5 — jsonb_ops Operator Class
115. Figure 28.6 — jsonb_path_ops Operator Class
116. Figure 29.1 — 29.3 Page Layout
117. Figure 29.2 — Value Insertion
118. Figure 29.3 — Range Size and Search Efficiency
119. Figure 29.4 — 29.7 Minmax-Multi Classes
120. Figure 29.5 — 29.7 Minmax-Multi Classes
121. Figure 29.6 — 29.9 Bloom Classes

Tables

1. Table 2.1 — Isolation levels and anomalies in the SQL standard
2. Table 2.2 — Isolation levels and anomalies in PostgreSQL
3. Table 12.1 — Transaction ID lock compatibility (× marks a conflict)
4. Table 12.2 — Relation-level lock compatibility (× marks a conflict)
5. Table 13.1 — Row-level lock compatibility (× marks a conflict)
6. Table 18.1 — Table size and number of parallel processes
7. Table 26.1 — Lexeme signatures
8. Table 26.2 — Document signatures
9. Table 28.1 — Consistency function for “everywher” or “oink”
10. Table 28.2 — Consistency function for mandatory and optional lexemes

About this edition

© Postgres Professional, 2022–2023. The English text and illustrations are reproduced from the PostgreSQL 14 · 2023 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 · Publication and credits