Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications. 5 Ed

Cover....1 Title Page....2 Copyright and Credits....2 Contributors....4 Table of Contents....6 Preface....14 Chapter 1: PostgreSQL 15 Overview....20 Making use of DBA-related features....20 Removing support for old pg_dump....20 Deprecating Python 2....21 Fixing the public schema....21 Adding pre-defined roles....21 Adding permissions to variables....22 Improving pg_stat_statements....22 New wait events....23 Adding logging functionality....23 Understanding developer-related features....26 Security invoker views....26 ICU locales....26 Better numeric....27 Handling ON DELETE....28 Working around NULL and UNIQUE....28 Adding the MERGE command to PostgreSQL....29 Using performance-related features....30 Adding multiple compression algorithms....30 Handling parallel queries more efficiently....31 Improved statistics handling....31 Prefetching during WAL recovery....31 Additional replication features....31 Two-phase commit for logical decoding....31 Adding row and column filtering....32 Improving ALTER SUBSCRIPTION....32 Supporting compressed base backups....33 Introducing archiving libraries....34 Summary....34 Chapter 2: Understanding Transactions and Locking....36 Working with PostgreSQL transactions....36 Handling errors inside a transaction....40 Making use of SAVEPOINT....41 Transactional DDLs....42 Understanding basic locking....43 Avoiding typical mistakes and explicit locking....45 Making use of FOR SHARE and FOR UPDATE....49 Understanding transaction isolation levels....52 Considering serializable snapshot isolation transactions....54 Observing deadlocks and similar issues....55 Utilizing advisory locks....57 Optimizing storage and managing cleanup....58 Configuring VACUUM and autovacuum....60 Watching VACUUM at work....62 Limiting transactions by making use of snapshot too old....66 Making use of more VACUUM features....66 Summary....67 Questions....67 Chapter 3: Making Use of Indexes....68 Understanding simple queries and the cost model....69 Making use of EXPLAIN....70 Digging into the PostgreSQL cost model....72 Deploying simple indexes....74 Making use of sorted output....75 Using bitmap scans effectively....78 Using indexes in an intelligent way....78 Understanding index de-duplication....81 Improving speed using clustered tables....81 Clustering tables....85 Making use of index-only scans....86 Understanding additional B-tree features....87 Combined indexes....87 Adding functional indexes....88 Reducing space consumption....89 Adding data while indexing....91 Introducing operator classes....91 Creating an operator class for a B-tree....93 Understanding PostgreSQL index types....99 Hash indexes....100 GiST indexes....100 GIN indexes....103 SP-GiST indexes....104 BRINs....105 Adding additional indexes....107 Achieving better answers with fuzzy searching....109 Taking advantage of pg_trgm....109 Speeding up LIKE queries....111 Handling regular expressions....112 Understanding full-text searches....113 Comparing strings....114 Defining GIN indexes....114 Debugging your search....115 Gathering word statistics....117 Taking advantage of exclusion operators....117 Summary....118 Questions....119 Chapter 4: Handling Advanced SQL....120 Supporting range types....121 Querying ranges efficiently....122 Handling multirange types....124 When to use range types....126 Introducing grouping sets....126 Loading some sample data....127 Applying grouping sets....128 Investigating performance....130 Combining grouping sets with the FILTER clause....132 Making use of ordered sets....133 Understanding hypothetical aggregates....135 Utilizing windowing functions and analytics....136 Partitioning data....137 Ordering data inside a window....138 Using sliding windows....140 Abstracting window clauses....147 Using on-board windowing functions....148 Writing your own aggregates....156 Creating simple aggregates....156 Adding support for parallel queries....160 Improving efficiency....161 Writing hypothetical aggregates....163 Handling recursions....165 UNION versus UNION ALL....166 Inspecting a practical example....167 Working with JSON and JSONB....169 Displaying and creating JSON documents....169 Turning JSON documents into rows....171 Accessing a JSON document....172 Summary....176 Chapter 5: Log Files and System Statistics....178 Gathering runtime statistics....178 Working with PostgreSQL system views....179 Creating log files....203 Configuring the postgresql.conf file....203 Summary....210 Questions....210 Chapter 6: Optimizing Queries for Good Performance....212 Learning what the optimizer does....212 A practical example – how the query optimizer handles a sample query....213 Understanding execution plans....228 Approaching plans systematically....228 Spotting problems....230 Understanding and fixing joins....236 Getting joins right....236 Processing outer joins....238 Understanding the join_collapse_limit variable....239 Enabling and disabling optimizer settings....240 Understanding genetic query optimization....244 Partitioning data....245 Creating inherited tables....245 Applying table constraints....248 Modifying inherited structures....250 Moving tables in and out of partitioned structures....251 Cleaning up data....251 Understanding PostgreSQL 15.x partitioning....252 Handling partitioning strategies....252 Using range partitioning....253 Utilizing list partitioning....255 Handling hash partitions....257 Adjusting parameters for good query performance....258 Speeding up sorting....262 Speeding up administrative tasks....265 Making use of parallel queries....266 What is PostgreSQL able to do in parallel?....271 Parallelism in practice....271 Introducing JIT compilation....272 Configuring JIT....273 Running queries....274 Summary....276 Chapter 7: Writing Stored Procedures....278 Understanding stored procedure languages....278 Understanding the fundamentals of stored procedures versus functions....280 The anatomy of a function....280 Exploring various stored procedure languages....284 Introducing PL/pgSQL....286 Writing stored procedures in PL/pgSQL....309 Introducing PL/Perl....311 Introducing PL/Python....319 Improving functions....323 Reducing the number of function calls....323 Using functions for various purposes....326 Summary....328 Questions....328 Chapter 8: Managing PostgreSQL Security....330 Managing network security....330 Understanding bind addresses and connections....331 Managing the pg_hba.conf file....335 Handling instance-level security....340 Defining database-level security....345 Adjusting schema-level permissions....347 Working with tables....350 Handling column-level security....351 Configuring default privileges....353 Digging into row-level security....354 Inspecting permissions....359 Reassigning objects and dropping users....363 Summary....364 Questions....365 Chapter 9: Handling Backup and Recovery....366 Performing simple dumps....366 Running pg_dump....367 Passing passwords and connection information....368 Extracting subsets of data....371 Handling various formats....371 Replaying backups....374 Handling global data....375 Summary....376 Questions....376 Chapter 10: Making Sense of Backups and Replication....378 Understanding the transaction log....379 Looking at the transaction log....379 Understanding checkpoints....380 Optimizing the transaction log....381 Transaction log archiving and recovery....382 Configuring for archiving....383 Using archiving libraries....384 Configuring the pg_hba.conf file....384 Creating base backups....385 Replaying the transaction log....390 Cleaning up the transaction log archive....394 Setting up asynchronous replication....395 Performing a basic setup....396 Halting and resuming replication....398 Checking replication to ensure availability....399 Performing failovers and understanding timelines....402 Managing conflicts....404 Making replication more reliable....406 Upgrading to synchronous replication....407 Adjusting durability....408 Making use of replication slots....410 Handling physical replication slots....411 Handling logical replication slots....413 Making use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commands....416 Setting up an HA cluster using Patroni....419 Understand how Patroni operates....419 Installing Patroni....420 Creating Patroni templates....425 Summary....437 Questions....438 Chapter 11: Deciding on Useful Extensions....440 Understanding how extensions work....440 Checking for available extensions....442 Making use of contrib modules....445 Using the adminpack module....445 Applying bloom filters....447 Deploying btree_gist and btree_gin....450 dblink – considering phasing out....451 Fetching files with file_fdw....452 Inspecting storage using pageinspect....454 Investigating caching with pg_buffercache....456 Encrypting data with pgcrypto....458 Prewarming caches with pg_prewarm....458 Inspecting performance with pg_stat_statements....460 Inspecting storage with pgstattuple....460 Fuzzy searching with pg_trgm....462 Connecting to remote servers using postgres_fdw....462 Other useful extensions....468 Summary....468 Chapter 12: Troubleshooting PostgreSQL....470 Approaching an unknown database....470 Inspecting pg_stat_activity....471 Querying pg_stat_activity....471 Checking for slow queries....474 Inspecting individual queries....475 Digging deeper with perf....476 Inspecting the log....477 Checking for missing indexes....478 Checking for memory and I/O....479 Understanding noteworthy error scenarios....481 Facing clog corruption....481 Understanding checkpoint messages....482 Managing corrupted data pages....483 Careless connection management....484 Fighting table bloat....484 Summary....485 Questions....485 Chapter 13: Migrating to PostgreSQL....486 Migrating SQL statements to PostgreSQL....486 Using LATERAL joins....487 Using grouping sets....487 Using the WITH clause – common table expressions....488 Using the WITH RECURSIVE clause....489 Using the FILTER clause....490 Using windowing functions....491 Using ordered sets – the WITHIN GROUP clause....491 Using the TABLESAMPLE clause....492 Using limit/offset....493 Using the OFFSET clause....494 Using temporal tables....494 Matching patterns in time series....495 Moving from Oracle to PostgreSQL....495 Using the oracle_fdw extension to move data....495 Using ora_migrator for fast migration....498 CYBERTEC Migrator – migration for the “big boys”....499 Using Ora2Pg to migrate from Oracle....500 Common pitfalls....502 Summary....504 Index....506 Other Books You May Enjoy....519