Mastering PostgreSQL 15 E-Book

Hans-Jürgen Schönig

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Veröffentlichungsjahr: 2023

Beschreibung

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.

Leseprobe

Mastering 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

1

PostgreSQL 15 Overview

Making use of DBA-related features

Removing support for old pg_dump

Deprecating Python 2

Fixing the public schema

Adding pre-defined roles

Adding permissions to variables

Improving pg_stat_statements

New wait events

Adding logging functionality

Understanding developer-related features

Security invoker views

ICU locales

Better numeric

Handling ON DELETE

Working around NULL and UNIQUE

Adding the MERGE command to PostgreSQL

Using performance-related features

Adding multiple compression algorithms

Handling parallel queries more efficiently

Improved statistics handling

Prefetching during WAL recovery

Additional replication features

Two-phase commit for logical decoding

Adding row and column filtering

Improving ALTER SUBSCRIPTION

Supporting compressed base backups

Introducing archiving libraries

Summary

2

Understanding Transactions and Locking

Working with PostgreSQL transactions

Handling errors inside a transaction

Making use of SAVEPOINT

Transactional DDLs

Understanding basic locking

Avoiding typical mistakes and explicit locking

Making use of FOR SHARE and FOR UPDATE

Understanding transaction isolation levels

Considering serializable snapshot isolation transactions

Observing deadlocks and similar issues

Utilizing advisory locks

Optimizing storage and managing cleanup

Configuring VACUUM and autovacuum

Watching VACUUM at work

Limiting transactions by making use of snapshot too old

Making use of more VACUUM features

Summary

Questions

3

Making Use of Indexes

Understanding simple queries and the cost model

Making use of EXPLAIN

Digging into the PostgreSQL cost model

Deploying simple indexes

Making use of sorted output

Using bitmap scans effectively

Using indexes in an intelligent way

Understanding index de-duplication

Improving speed using clustered tables

Clustering tables

Making use of index-only scans

Understanding additional B-tree features

Combined indexes

Adding functional indexes

Reducing space consumption

Adding data while indexing

Introducing operator classes

Creating an operator class for a B-tree

Understanding PostgreSQL index types

Hash indexes

GiST indexes

GIN indexes

SP-GiST indexes

BRINs

Adding additional indexes

Achieving better answers with fuzzy searching

Taking advantage of pg_trgm

Speeding up LIKE queries

Handling regular expressions

Understanding full-text searches

Comparing strings

Defining GIN indexes

Debugging your search

Gathering word statistics

Taking advantage of exclusion operators

Summary

Questions

4

Handling Advanced SQL

Supporting range types

Querying ranges efficiently

Handling multirange types

When to use range types

Introducing grouping sets

Loading some sample data

Applying grouping sets

Investigating performance

Combining grouping sets with the FILTER clause

Making use of ordered sets

Understanding hypothetical aggregates

Utilizing windowing functions and analytics

Partitioning data

Ordering data inside a window

Using sliding windows

Abstracting window clauses

Using on-board windowing functions

Writing your own aggregates

Creating simple aggregates

Adding support for parallel queries

Improving efficiency

Writing hypothetical aggregates

Handling recursions

UNION versus UNION ALL

Inspecting a practical example

Working with JSON and JSONB

Displaying and creating JSON documents

Turning JSON documents into rows

Accessing a JSON document

Summary

5

Log Files and System Statistics

Gathering runtime statistics

Working with PostgreSQL system views

Creating log files

Configuring the postgresql.conf file

Summary

Questions

6

Optimizing Queries for Good Performance

Learning what the optimizer does

A practical example – how the query optimizer handles a sample query

Understanding execution plans

Approaching plans systematically

Spotting problems

Understanding and fixing joins

Getting joins right

Processing outer joins

Understanding the join_collapse_limit variable

Enabling and disabling optimizer settings

Understanding genetic query optimization

Partitioning data

Creating inherited tables

Applying table constraints

Modifying inherited structures

Moving tables in and out of partitioned structures

Cleaning up data

Understanding PostgreSQL 15.x partitioning

Handling partitioning strategies

Using range partitioning

Utilizing list partitioning

Handling hash partitions

Adjusting parameters for good query performance

Speeding up sorting

Speeding up administrative tasks

Making use of parallel queries

What is PostgreSQL able to do in parallel?

Parallelism in practice

Introducing JIT compilation

Configuring JIT

Running queries

Summary

7

Writing Stored Procedures

Understanding stored procedure languages

Understanding the fundamentals of stored procedures versus functions

The anatomy of a function

Exploring various stored procedure languages

Introducing PL/pgSQL

Writing stored procedures in PL/pgSQL

Introducing PL/Perl

Introducing PL/Python

Improving functions

Reducing the number of function calls

Using functions for various purposes

Summary

Questions

8

Managing PostgreSQL Security

Managing network security

Understanding bind addresses and connections

Managing the pg_hba.conf file

Handling instance-level security

Defining database-level security

Adjusting schema-level permissions

Working with tables

Handling column-level security

Configuring default privileges

Digging into row-level security

Inspecting permissions

Reassigning objects and dropping users

Summary

Questions

9

Handling Backup and Recovery

Performing simple dumps

Running pg_dump

Passing passwords and connection information

Extracting subsets of data

Handling various formats

Replaying backups

Handling global data

Summary

Questions

10

Making Sense of Backups and Replication

Understanding the transaction log

Looking at the transaction log

Understanding checkpoints

Optimizing the transaction log

Transaction log archiving and recovery

Configuring for archiving

Using archiving libraries

Configuring the pg_hba.conf file

Creating base backups

Replaying the transaction log

Cleaning up the transaction log archive

Setting up asynchronous replication

Performing a basic setup

Halting and resuming replication

Checking replication to ensure availability

Performing failovers and understanding timelines

Managing conflicts

Making replication more reliable

Upgrading to synchronous replication

Adjusting durability

Making use of replication slots

Handling physical replication slots

Handling logical replication slots

Making use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commands

Setting up an HA cluster using Patroni

Understand how Patroni operates

Installing Patroni

Creating Patroni templates

Summary

Questions

11

Deciding on Useful Extensions

Understanding how extensions work

Checking for available extensions

Making use of contrib modules

Using the adminpack module

Applying bloom filters

Deploying btree_gist and btree_gin

dblink – considering phasing out

Fetching files with file_fdw

Inspecting storage using pageinspect

Investigating caching with pg_buffercache

Encrypting data with pgcrypto

Prewarming caches with pg_prewarm

Inspecting performance with pg_stat_statements

Inspecting storage with pgstattuple

Fuzzy searching with pg_trgm

Connecting to remote servers using postgres_fdw

Other useful extensions

Summary

12

Troubleshooting PostgreSQL

Approaching an unknown database

Inspecting pg_stat_activity

Querying pg_stat_activity

Checking for slow queries

Inspecting individual queries

Digging deeper with perf

Inspecting the log

Checking for missing indexes

Checking for memory and I/O

Understanding noteworthy error scenarios

Facing clog corruption

Understanding checkpoint messages

Managing corrupted data pages

Careless connection management

Fighting table bloat

Summary

Questions

13

Migrating to PostgreSQL

Migrating SQL statements to PostgreSQL

Using LATERAL joins

Using grouping sets

Using the WITH clause – common table expressions

Using the WITH RECURSIVE clause

Using the FILTER clause

Using windowing functions

Using ordered sets – the WITHIN GROUP clause

Using the TABLESAMPLE clause

Using limit/offset

Using the OFFSET clause

Using temporal tables

Matching patterns in time series

Moving from Oracle to PostgreSQL

Using the oracle_fdw extension to move data

Using ora_migrator for fast migration

CYBERTEC Migrator – migration for the “big boys”

Using Ora2Pg to migrate from Oracle

Common pitfalls

Summary

Index

Other Books You May Enjoy