Python Games Development using Pygame: Guide to creating your own games with Pygame.

This book is for those who will not settle for a black-box approach when working with a database. If you are eager to learn, prefer not to take expert advice for granted, and would like to figure out everything yourself, follow along. I expect the readers to be familiar with PostgreSQL and have at least some general understanding of what it is all about. I hope that this book will also be useful for those who are familiar with another database system, but switch over to PostgreSQL and would like to understand how they differ. You will not find any quick-fix solutions here. It is impossible to provide one for every occasion, but if you understand inner mechanics of a system, you will be able to critically evaluate other people’s experience and come to your own conclusions. This is exactly why I explain some technicalities in great detail, which may at first seem to be of no practical use. Most of the information in this book is likely to remain up-to-date for quite a long time, but some details will inevitably change very fast—hence the version number on the cover page. Contemplate, experiment, verify the facts: PostgreSQL provides all the tools you might need for it, and I tried to show how to use them. About This Book Introduction Data Organization Databases System Catalog Schemas Tablespaces Relations Files and Forks Pages TOAST Processes and Memory Clients and the Client-Server Protocol Part I Isolation and MVCC Isolation Consistency Isolation Levels and Anomalies in SQL Standard Lost Update Dirty Reads and Read Uncommitted Non-Repeatable Reads and Read Committed Phantom Reads and Repeatable Read No Anomalies and Serializable Why These Anomalies? Isolation Levels in PostgreSQL Read Committed Repeatable Read Serializable Which Isolation Level to Use? Pages and Tuples Page Structure Page Header Special Space Tuples Item Pointers Free Space Row Version Layout Operations on Tuples Insert Commit Delete Abort Update Indexes TOAST Virtual Transactions Subtransactions Savepoints Errors and Atomicity Snapshots What is a Snapshot? Row Version Visibility Snapshot Structure Visibility of Transactions' Own Changes Transaction Horizon System Catalog Snapshots Exporting Snapshots Page Pruning and HOT Updates Page Pruning HOT Updates Page Pruning for HOT Updates HOT Chain Splits Page Pruning for Indexes Vacuum and Autovacuum Vacuum Database Horizon Revisited Vacuum Phases Heap Scan Index Vacuuming Heap Vacuuming Heap Truncation Analysis Automatic Vacuum and Analysis About the Autovacuum Mechanism Which Tables Need to be Vacuumed? Which Tables Need to Be Analyzed? Autovacuum in Action Managing the Load Vacuum Throttling Autovacuum Throttling Monitoring Monitoring Vacuum Monitoring Autovacuum Freezing Transaction ID Wraparound Tuple Freezing and Visibility Rules Managing Freezing Minimal Freezing Age Age for Aggressive Freezing Age for Forced Autovacuum Age for Failsafe Freezing Manual Freezing Freezing by Vacuum Freezing Data at the Initial Loading Rebuilding Tables and Indexes Full Vacuuming Why is Routine Vacuuming not Enough? Estimating Data Density Freezing Other Rebuilding Methods Alternatives to Full Vacuuming Reducing Downtime During Rebuilding Precautions Read-Only Queries Data Updates Part II Buffer Cache and WAL Buffer Cache Caching Buffer Cache Design Cache Hits Cache Misses Buffer Search and Eviction Bulk Eviction Choosing the Buffer Cache Size Cache Warming Local Cache Write-Ahead Log Logging WAL Structure Logical Structure Physical Structure Checkpoint Recovery Background Writing WAL Setup Configuring Checkpoints Configuring Background Writing Monitoring WAL Modes Performance Fault Tolerance Caching Data Corruption Non-Atomic Writes WAL Levels Minimal Replica Logical Part III Locks Relation-Level Locks About Locks Heavyweight Locks Locks on Transaction IDs Relation-Level Locks Wait Queue Row-Level Locks Lock Design Row-Level Locking Modes Exclusive Modes Shared Modes Multitransactions Wait Queue Exclusive Modes Shared Modes No-Wait Locks Deadlocks Deadlocks by Row Updates Deadlocks Between Two UPDATE Statements Miscellaneous Locks Non-Object Locks Relation Extension Locks Page Locks Advisory Locks Predicate Locks Locks on Memory Structures Spinlocks Lightweight Locks Examples Buffer Cache WAL Buffers Monitoring Waits Sampling Part IV Query Execution Query Execution Stages Demo Database Simple Query Protocol Parsing Transformation Planning Execution Extended Query Protocol Preparation Parameter Binding Planning and Execution Getting the Results Statistics Basic Statistics NULL Values Distinct Values Most Common Values Histogram Statistics for Non-Scalar Data Types Average Field Width Correlation Expression Statistics Extended Expression Statistics Statistics for Expression Indexes Multivariate Statistics Functional Dependencies Between Columns Multivariate Number of Distinct Values Multivariate MCV Lists Table Access Methods Pluggable Storage Engines Sequential Scans Cost Estimation Parallel Plans Parallel Sequential Scans Cost Estimation Parallel Execution Limitations Number of Background Workers Non-Parallelizable Queries Parallel Restricted Queries Index Access Methods Indexes and Extensibility Operator Classes and Families Operator Classes Operator Families Indexing Engine Interface Access Method Properties Index-Level Properties Column-Level Properties Index Scans Regular Index Scans Cost Estimation Good Scenario: High Correlation Bad Scenario: Low Correlation Index-Only Scans Indexes with the Include Clause Bitmap Scans Bitmap Accuracy Operations on Bitmaps Cost Estimation Parallel Index Scans Comparison of Various Access Methods Nested Loop Join Types and Methods Nested Loop Joins Cartesian Product Parameterized Joins Caching Rows (Memoization) Outer Joins Anti- and Semi-joins Non-Equi-joins Parallel Mode Hashing Hash Joins One-Pass Hash Joins Two-Pass Hash Joins Dynamic Adjustments Hash Joins in Parallel Plans Parallel One-Pass Hash Joins Parallel Two-Pass Hash Joins Modifications Distinct Values and Grouping Sorting and Merging Merge Joins Merging Sorted Sets Parallel Mode Modifications Sorting Quicksort Top-N Heapsort External Sorting Incremental Sorting Parallel Mode Distinct Values and Grouping Comparison of Join Methods Part V Types of Indexes Hash Overview Page Layout Operator Class Properties Access Method Properties Index-Level Properties Column-Level Properties B-tree Overview Search and Insertions Search by Equality Search by Inequality Search by Range Insertions Page Layout Deduplication Compact Storage of Inner Index Entries Operator Class Comparison Semantics Multicolumn Indexes and Sorting Properties Access Method Properties Index-Level Properties Column-Level Properties GiST Overview R-Trees for Points Page Layout Operator Class Search for Contained Elements Nearest Neighbor Search Insertion Exclusion Constraints Properties RD-Trees for Full-Text Search About Full-Text Search Indexing tsvector Data Properties Other Data Types SP-GiST Overview Quadtrees for Points Operator Class Page Layout Search Insertion Properties K-Dimensional Trees for Points Radix Trees for Strings Operator Class Search Insertion Properties Other Data Types GIN Overview Index for Full-Text Search Page Layout Operator Class Search Frequent and Rare Lexemes Insertions Limiting Result Set Size Properties GIN Limitations and RUM Index Trigrams Indexing Arrays Indexing JSON jsonb_ops Operator Class jsonb_path_ops Operator Class Indexing Other Data Types BRIN Overview Example Page Layout Search Summary Information Updates Value Insertion Range Summarization Minmax Classes Choosing Columns to be Indexed Range Size and Search Efficiency Properties Minmax-Multi Classes Inclusion Classes Bloom Classes Conclusion Index