Table Of ContentMastering PostgreSQL 15
Advanced techniques to build and manage scalable, reliable,
and fault-tolerant database applications
Hans-Jürgen Schönig
BIRMINGHAM—MUMBAI
Mastering PostgreSQL 15
Copyright © 2023 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any
form or by any means, without the prior written permission of the publisher, except in the case of brief quotations
embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented.
However, the information contained in this book is sold without warranty, either express or implied. Neither the
author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged
to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products
mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the
accuracy of this information.
Group Product Manager: Reshma Raman
Publishing Product Manager: Devika Battike
Senior Editor: Nazia Shaikh
Content Development Editor: Priyanka Soam
Technical Editor: Sweety Pagaria
Copy Editor: Safis Editing
Project Coordinator: Farheen Fathima
Proofreader: Safis Editing
Indexer: Sejal Dsilva
Production Designer: Vijay Kamble
Marketing Coordinator: Nivedita Singh
First published: Jan 2018
Second edition: Oct 2018
Third edition: Nov 2019
Fourth edition: Nov 2020
Fifth published: Jan 2023
Production reference: 1270123
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-80324-834-9
www.packtpub.com
Contributors
About the author
Hans-Jürgen Schönig has 20 years’ experience with PostgreSQL. He is the CEO of a PostgreSQL
consulting and support company called CYBERTEC PostgreSQL International GmbH. It has successfully
served countless customers around the globe. Before founding CYBERTEC PostgreSQL International
GmbH in 2000, he worked as a database developer at a private research company that focused on the
Austrian labor market, where he primarily worked on data mining and forecast models. He has also
written several books about PostgreSQL.
About the reviewers
Burhan Akbulut is the co-founder of PostgresTech. It is a company that provides PostgreSQL consultancy
and support to start-ups and enterprise companies. Burhan Akbulut started his career as a PostgreSQL
consultant at CookSoft, a well-known PostgreSQL consulting firm founded by Şahap Aşçı, where he
provided consultancy and support to many international customers. Before founding PostgresTech,
he worked at Vodafone as an open source database senior specialist responsible for all PostgreSQL
databases. He has especially focused on database management with IaC, management of cloud databases,
and migration from other databases to PostgreSQL during his career.
I would like to thank my colleague Şeyma Mintaş who helped me review the book.
Marcelo Diaz is a software engineer with more than 15 years of experience, with a special focus on
PostgreSQL. He is passionate about open source software and has promoted its application in critical
and high-demand environments, working as a software developer and consultant for both private and
public companies. He currently works very happily at Cybertec and as a technical reviewer for Packt
Publishing. He enjoys spending his leisure time with his daughter, Malvina, and his wife, Romina.
He also likes playing football.
Dinesh Kumar Chemuduru works as a principal architect (OSS) at Tessell Inc. He has been working
with PostgreSQL since 2011, and he also worked as a consultant at AWS. He is also an author and
contributor to a few popular open source solutions. He co-authored PostgreSQL High Performance
Cookbook 9.6, which was released in 2016. He loves to code in Dart, Go, Angular, and C++ and loves
to deploy them in Kubernetes.
Thanks and love to my wife, Manoja Reddy, and my kids, Yashvi and Isha.
Table of Contents
Preface xiii
1
PostgreSQL 15 Overview 1
Making use of DBA-related features 1 Working around NULL and UNIQUE 9
Removing support for old pg_dump 1 Adding the MERGE command
to PostgreSQL 10
Deprecating Python 2 2
Fixing the public schema 2 Using performance-related features 11
Adding pre-defined roles 2
Adding multiple compression algorithms 11
Adding permissions to variables 3
Handling parallel queries more efficiently 12
Improving pg_stat_statements 3
Improved statistics handling 12
New wait events 4
Prefetching during WAL recovery 12
Adding logging functionality 4
Additional replication features 12
Understanding developer-related
Two-phase commit for logical decoding 12
features 7
Adding row and column filtering 13
Security invoker views 7
Improving ALTER SUBSCRIPTION 13
ICU locales 7
Supporting compressed base backups 14
Better numeric 8
Introducing archiving libraries 15
Handling ON DELETE 9
Summary 15
2
Understanding Transactions and Locking 17
Working with PostgreSQL Transactional DDLs 23
transactions 17
Understanding basic locking 24
Handling errors inside a transaction 21
Avoiding typical mistakes and explicit locking 26
Making use of SAVEPOINT 22
vi Table of Contents
Making use of FOR SHARE Optimizing storage and
and FOR UPDATE 30 managing cleanup 39
Understanding transaction Configuring VACUUM and autovacuum 41
isolation levels 33 Watching VACUUM at work 43
Considering serializable snapshot isolation Limiting transactions by making
transactions 35 use of snapshot too old 47
Making use of more VACUUM features 47
Observing deadlocks and
similar issues 36 Summary 48
Utilizing advisory locks 38 Questions 48
3
Making Use of Indexes 49
Understanding simple queries Understanding PostgreSQL
and the cost model 50 index types 80
Making use of EXPLAIN 51 Hash indexes 81
Digging into the PostgreSQL cost model 53 GiST indexes 81
Deploying simple indexes 55 GIN indexes 84
Making use of sorted output 56 SP-GiST indexes 85
Using bitmap scans effectively 59 BRINs 86
Using indexes in an intelligent way 59 Adding additional indexes 88
Understanding index de-duplication 62
Achieving better answers
Improving speed using with fuzzy searching 90
clustered tables 62 Taking advantage of pg_trgm 90
Clustering tables 66 Speeding up LIKE queries 92
Making use of index-only scans 67 Handling regular expressions 93
Understanding additional Understanding full-text searches 94
B-tree features 68
Comparing strings 95
Combined indexes 68 Defining GIN indexes 95
Adding functional indexes 69 Debugging your search 96
Reducing space consumption 70 Gathering word statistics 98
Adding data while indexing 72 Taking advantage of exclusion operators 98
Introducing operator classes 72 Summary 99
Creating an operator class for a B-tree 74 Questions 100
Table of Contents vii
4
Handling Advanced SQL 101
Supporting range types 102 Using sliding windows 121
Querying ranges efficiently 103 Abstracting window clauses 128
Handling multirange types 105 Using on-board windowing functions 129
When to use range types 107 Writing your own aggregates 137
Introducing grouping sets 107 Creating simple aggregates 137
Loading some sample data 108 Adding support for parallel queries 141
Applying grouping sets 109 Improving efficiency 142
Investigating performance 111 Writing hypothetical aggregates 144
Combining grouping sets with Handling recursions 146
the FILTER clause 113
UNION versus UNION ALL 147
Making use of ordered sets 114 Inspecting a practical example 148
Understanding hypothetical
Working with JSON and JSONB 150
aggregates 116
Displaying and creating JSON documents 150
Utilizing windowing functions
Turning JSON documents into rows 152
and analytics 117
Accessing a JSON document 153
Partitioning data 118
Summary 157
Ordering data inside a window 119
5
Log Files and System Statistics 159
Gathering runtime statistics 159 Configuring the postgresql.conf file 184
Working with PostgreSQL system views 160 Summary 191
Creating log files 184 Questions 191
6
Optimizing Queries for Good Performance 193
Learning what the optimizer does 193 Understanding execution plans 209
A practical example – how the query Approaching plans systematically 209
optimizer handles a sample query 194 Spotting problems 211
viii Table of Contents
Understanding and fixing joins 217 Handling partitioning strategies 233
Getting joins right 217 Using range partitioning 234
Processing outer joins 219 Utilizing list partitioning 236
Understanding the join_collapse_limit Handling hash partitions 238
variable 220
Adjusting parameters for
Enabling and disabling good query performance 239
optimizer settings 221 Speeding up sorting 243
Understanding genetic query optimization 225 Speeding up administrative tasks 246
Partitioning data 226 Making use of parallel queries 247
Creating inherited tables 226 What is PostgreSQL able to do in parallel? 252
Applying table constraints 229 Parallelism in practice 252
Modifying inherited structures 231
Introducing JIT compilation 253
Moving tables in and out of partitioned
Configuring JIT 254
structures 232
Running queries 255
Cleaning up data 232
Understanding PostgreSQL 15.x partitioning 233 Summary 257
7
Writing Stored Procedures 259
Understanding stored Introducing PL/Perl 292
procedure languages 259 Introducing PL/Python 300
Understanding the fundamentals of stored Improving functions 304
procedures versus functions 261
Reducing the number of function calls 304
The anatomy of a function 261
Using functions for
Exploring various stored
various purposes 307
procedure languages 265
Summary 309
Introducing PL/pgSQL 267
Questions 309
Writing stored procedures in PL/pgSQL 290
8
Managing PostgreSQL Security 311
Managing network security 311 Managing the pg_hba.conf file 316
Understanding bind addresses Handling instance-level security 321
and connections 312 Defining database-level security 326
Table of Contents ix
Adjusting schema-level permissions 328 Inspecting permissions 340
Working with tables 331 Reassigning objects and dropping
Handling column-level security 332 users 344
Configuring default privileges 334
Summary 345
Digging into row-level security 335 Questions 346
9
Handling Backup and Recovery 347
Performing simple dumps 347 Replaying backups 355
Running pg_dump 348 Handling global data 356
Passing passwords and Summary 357
connection information 349
Questions 357
Extracting subsets of data 352
Handling various formats 352
10
Making Sense of Backups and Replication 359
Understanding the transaction log 360 Performing failovers and
understanding timelines 383
Looking at the transaction log 360
Managing conflicts 385
Understanding checkpoints 361
Making replication more reliable 387
Optimizing the transaction log 362
Upgrading to synchronous
Transaction log archiving
replication 388
and recovery 363
Adjusting durability 389
Configuring for archiving 364
Using archiving libraries 365 Making use of replication slots 391
Configuring the pg_hba.conf file 365
Handling physical replication slots 392
Creating base backups 366
Handling logical replication slots 394
Replaying the transaction log 371
Making use of the CREATE
Cleaning up the transaction log archive 375
PUBLICATION and CREATE
Setting up asynchronous replication 376 SUBSCRIPTION commands 397
Performing a basic setup 377 Setting up an HA cluster
Halting and resuming replication 379 using Patroni 400
Checking replication to ensure availability 380
Understand how Patroni operates 400
