PostgreSQL 14 Internals

About This Book\nIntroduction\n	Data Organization\n		Databases\n		System Catalog\n		Schemas\n		Tablespaces\n		Relations\n		Files and Forks\n		Pages\n		TOAST\n	Processes and Memory\n	Clients and the Client-Server Protocol\nPart I Isolation and MVCC\n	Isolation\n		Consistency\n		Isolation Levels and Anomalies in SQL Standard\n			Lost Update\n			Dirty Reads and Read Uncommitted\n			Non-Repeatable Reads and Read Committed\n			Phantom Reads and Repeatable Read\n			No Anomalies and Serializable\n			Why These Anomalies?\n		Isolation Levels in PostgreSQL\n			Read Committed\n			Repeatable Read\n			Serializable\n		Which Isolation Level to Use?\n	Pages and Tuples\n		Page Structure\n			Page Header\n			Special Space\n			Tuples\n			Item Pointers\n			Free Space\n		Row Version Layout\n		Operations on Tuples\n			Insert\n			Commit\n			Delete\n			Abort\n			Update\n		Indexes\n		TOAST\n		Virtual Transactions\n		Subtransactions\n			Savepoints\n			Errors and Atomicity\n	Snapshots\n		What is a Snapshot?\n		Row Version Visibility\n		Snapshot Structure\n		Visibility of Transactions\' Own Changes\n		Transaction Horizon\n		System Catalog Snapshots\n		Exporting Snapshots\n	Page Pruning and HOT Updates\n		Page Pruning\n		HOT Updates\n		Page Pruning for HOT Updates\n		HOT Chain Splits\n		Page Pruning for Indexes\n	Vacuum and Autovacuum\n		Vacuum\n		Database Horizon Revisited\n		Vacuum Phases\n			Heap Scan\n			Index Vacuuming\n			Heap Vacuuming\n			Heap Truncation\n		Analysis\n		Automatic Vacuum and Analysis\n			About the Autovacuum Mechanism\n			Which Tables Need to be Vacuumed?\n			Which Tables Need to Be Analyzed?\n			Autovacuum in Action\n		Managing the Load\n			Vacuum Throttling\n			Autovacuum Throttling\n		Monitoring\n			Monitoring Vacuum\n			Monitoring Autovacuum\n	Freezing\n		Transaction ID Wraparound\n		Tuple Freezing and Visibility Rules\n		Managing Freezing\n			Minimal Freezing Age\n			Age for Aggressive Freezing\n			Age for Forced Autovacuum\n			Age for Failsafe Freezing\n		Manual Freezing\n			Freezing by Vacuum\n			Freezing Data at the Initial Loading\n	Rebuilding Tables and Indexes\n		Full Vacuuming\n			Why is Routine Vacuuming not Enough?\n			Estimating Data Density\n			Freezing\n		Other Rebuilding Methods\n			Alternatives to Full Vacuuming\n			Reducing Downtime During Rebuilding\n		Precautions\n			Read-Only Queries\n			Data Updates\nPart II Buffer Cache and WAL\n	Buffer Cache\n		Caching\n		Buffer Cache Design\n		Cache Hits\n		Cache Misses\n			Buffer Search and Eviction\n		Bulk Eviction\n		Choosing the Buffer Cache Size\n		Cache Warming\n		Local Cache\n	Write-Ahead Log\n		Logging\n		WAL Structure\n			Logical Structure\n			Physical Structure\n		Checkpoint\n		Recovery\n		Background Writing\n		WAL Setup\n			Configuring Checkpoints\n			Configuring Background Writing\n			Monitoring\n	WAL Modes\n		Performance\n		Fault Tolerance\n			Caching\n			Data Corruption\n			Non-Atomic Writes\n		WAL Levels\n			Minimal\n			Replica\n			Logical\nPart III Locks\n	Relation-Level Locks\n		About Locks\n		Heavyweight Locks\n		Locks on Transaction IDs\n		Relation-Level Locks\n		Wait Queue\n	Row-Level Locks\n		Lock Design\n		Row-Level Locking Modes\n			Exclusive Modes\n			Shared Modes\n		Multitransactions\n		Wait Queue\n			Exclusive Modes\n			Shared Modes\n		No-Wait Locks\n		Deadlocks\n			Deadlocks by Row Updates\n			Deadlocks Between Two UPDATE Statements\n	Miscellaneous Locks\n		Non-Object Locks\n		Relation Extension Locks\n		Page Locks\n		Advisory Locks\n		Predicate Locks\n	Locks on Memory Structures\n		Spinlocks\n		Lightweight Locks\n		Examples\n			Buffer Cache\n			WAL Buffers\n		Monitoring Waits\n		Sampling\nPart IV Query Execution\n	Query Execution Stages\n		Demo Database\n		Simple Query Protocol\n			Parsing\n			Transformation\n			Planning\n			Execution\n		Extended Query Protocol\n			Preparation\n			Parameter Binding\n			Planning and Execution\n			Getting the Results\n	Statistics\n		Basic Statistics\n		NULL Values\n		Distinct Values\n		Most Common Values\n		Histogram\n		Statistics for Non-Scalar Data Types\n		Average Field Width\n		Correlation\n		Expression Statistics\n			Extended Expression Statistics\n			Statistics for Expression Indexes\n		Multivariate Statistics\n			Functional Dependencies Between Columns\n			Multivariate Number of Distinct Values\n			Multivariate MCV Lists\n	Table Access Methods\n		Pluggable Storage Engines\n		Sequential Scans\n			Cost Estimation\n		Parallel Plans\n		Parallel Sequential Scans\n			Cost Estimation\n		Parallel Execution Limitations\n			Number of Background Workers\n			Non-Parallelizable Queries\n			Parallel Restricted Queries\n	Index Access Methods\n		Indexes and Extensibility\n		Operator Classes and Families\n			Operator Classes\n			Operator Families\n		Indexing Engine Interface\n			Access Method Properties\n			Index-Level Properties\n			Column-Level Properties\n	Index Scans\n		Regular Index Scans\n			Cost Estimation\n			Good Scenario: High Correlation\n			Bad Scenario: Low Correlation\n		Index-Only Scans\n			Indexes with the Include Clause\n		Bitmap Scans\n			Bitmap Accuracy\n			Operations on Bitmaps\n			Cost Estimation\n		Parallel Index Scans\n		Comparison of Various Access Methods\n	Nested Loop\n		Join Types and Methods\n		Nested Loop Joins\n			Cartesian Product\n			Parameterized Joins\n			Caching Rows (Memoization)\n			Outer Joins\n			Anti- and Semi-joins\n			Non-Equi-joins\n			Parallel Mode\n	Hashing\n		Hash Joins\n			One-Pass Hash Joins\n			Two-Pass Hash Joins\n			Dynamic Adjustments\n			Hash Joins in Parallel Plans\n			Parallel One-Pass Hash Joins\n			Parallel Two-Pass Hash Joins\n			Modifications\n		Distinct Values and Grouping\n	Sorting and Merging\n		Merge Joins\n			Merging Sorted Sets\n			Parallel Mode\n			Modifications\n		Sorting\n			Quicksort\n			Top-N Heapsort\n			External Sorting\n			Incremental Sorting\n			Parallel Mode\n		Distinct Values and Grouping\n		Comparison of Join Methods\nPart V Types of Indexes\n	Hash\n		Overview\n		Page Layout\n		Operator Class\n		Properties\n			Access Method Properties\n			Index-Level Properties\n			Column-Level Properties\n	B-tree\n		Overview\n		Search and Insertions\n			Search by Equality\n			Search by Inequality\n			Search by Range\n			Insertions\n		Page Layout\n			Deduplication\n			Compact Storage of Inner Index Entries\n		Operator Class\n			Comparison Semantics\n			Multicolumn Indexes and Sorting\n		Properties\n			Access Method Properties\n			Index-Level Properties\n			Column-Level Properties\n	GiST\n		Overview\n		R-Trees for Points\n			Page Layout\n			Operator Class\n			Search for Contained Elements\n			Nearest Neighbor Search\n			Insertion\n			Exclusion Constraints\n			Properties\n		RD-Trees for Full-Text Search\n			About Full-Text Search\n			Indexing tsvector Data\n			Properties\n		Other Data Types\n	SP-GiST\n		Overview\n		Quadtrees for Points\n			Operator Class\n			Page Layout\n			Search\n			Insertion\n			Properties\n		K-Dimensional Trees for Points\n		Radix Trees for Strings\n			Operator Class\n			Search\n			Insertion\n			Properties\n		Other Data Types\n	GIN\n		Overview\n		Index for Full-Text Search\n			Page Layout\n			Operator Class\n			Search\n			Frequent and Rare Lexemes\n			Insertions\n			Limiting Result Set Size\n			Properties\n			GIN Limitations and RUM Index\n		Trigrams\n		Indexing Arrays\n		Indexing JSON\n			jsonb_ops Operator Class\n			jsonb_path_ops Operator Class\n		Indexing Other Data Types\n	BRIN\n		Overview\n		Example\n		Page Layout\n		Search\n		Summary Information Updates\n			Value Insertion\n			Range Summarization\n		Minmax Classes\n			Choosing Columns to be Indexed\n			Range Size and Search Efficiency\n			Properties\n		Minmax-Multi Classes\n		Inclusion Classes\n		Bloom Classes\nConclusion\nIndex