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

Starting with an introduction to the newly released features of PostgreSQL 15, this updated fifth edition will help you get to grips with PostgreSQL administration and how to build dynamic database solutions for enterprise apps, including designing both physical and technical aspects of the system.

You'll explore advanced PostgreSQL features, such as logical replication, database clusters, advanced indexing, and user management to manage and maintain your database. You'll then work with the PostgreSQL optimizer, configure PostgreSQL for high speed, and move from Oracle to PostgreSQL. Among the other skills that the book will help you build, you'll cover transactions, handling recursions, working with JSON and JSONB data, and setting up a Patroni cluster. It will show you how to improve performance with query optimization. You'll also focus on managing network security and work with backups and replication while exploring useful PostgreSQL extensions that optimize the performance of large databases.

By the end of this PostgreSQL book, you'll be able to use your database to its utmost capacity by implementing advanced administrative tasks with ease.

What You Will Learn:

• Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries
• Work with stored procedures and manage backup and recovery
• Get the hang of replication and failover techniques
• Improve the security of your database server and handle encryption effectively
• Troubleshoot your PostgreSQL instance for solutions to common and not-so-common problems
• Perform database migration from Oracle to PostgreSQL with ease

Who this book is for:

This database administration book is for PostgreSQL developers, database administrators, and professionals who want to implement advanced functionalities and master complex administrative tasks with PostgreSQL 15. Prior experience in PostgreSQL and familiarity with the basics of database administration will assist with understanding key concepts covered in the book.