Mastering PostgreSQL 17 E-Book

Hans-Jürgen Schönig

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

Seitenzahl: 638

Veröffentlichungsjahr: 2024

Beschreibung

Starting with new features introduced in PostgreSQL 17, the sixth edition of this book provides comprehensive insights into advanced database management, helping you elevate your PostgreSQL skills to an expert level. Written by Hans-Jürgen Schönig, a PostgreSQL expert with over 25 years of experience and the CEO of CYBERTEC PostgreSQL International GmbH, this guide distills real-world expertise from supporting countless global customers. It guides you through crucial aspects of professional database management, including performance tuning, replication, indexing, and security strategies.
You’ll learn how to handle complex queries, optimize execution plans, and enhance user interactions with advanced SQL features such as window functions and JSON support. Hans equips you with practical approaches for managing database locks, transactions, and stored procedures to ensure peak performance. With real-world examples and expert solutions, you'll also explore replication techniques for high availability, along with troubleshooting methods to detect and resolve bottlenecks, preparing you to tackle everyday challenges in database administration.
By the end of the book, you'll be ready to deploy, secure, and maintain PostgreSQL databases efficiently in production environments.

Leseprobe

Mastering PostgreSQL 17

Elevate your database skills with advanced deployment, optimization, and security strategies

Hans-Jürgen Schönig

Mastering PostgreSQL 17

Copyright © 2024 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: Apeksha Shetty

Publishing Product Manager: Chayan Majumdar

Book Project Manager: Aparna Nair

Senior Editor: Rohit Singh

Technical Editor: Rahul Limbachiya

Copy Editor: Safis Editing

Proofreader: Rohit Singh

Indexer: Tejal Soni

Production Designer: Gokul Raj S.T

DevRel Marketing Executive: Nivedita Singh

First published: January 2018

Second edition: October 2018

Third edition: November 2019

Fourth edition: November 2020

Fifth edition: January 2023

Sixth edition: December 2024

Production reference: 1061224

Published by Packt Publishing Ltd.

Grosvenor House

11 St Paul’s Square

Birmingham

B3 1RB, UK.

ISBN 978-1-83620-597-5

www.packtpub.com

Contributors

About the author

Hans-Jürgen Schönig has 25 years of experience with PostgreSQL. He is the CEO of a PostgreSQL consulting and support company called CYBERTEC PostgreSQL International GmbH, which 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 reviewer

Rajneesh Verma is a seasoned technology leader with over 18 years of experience, currently heading IT and security at CYBERTEC PostgreSQL Services. Specializing in enterprise design and architecture, he is passionate about building reliable platforms, including database-as-a-service solutions and innovative setups for cloud and on-premises environments, IAAS, PAAS, and security. His technical stack includes PostgreSQL, SQL Server, Python, Flask, HTML, and CSS. Curious by nature, Rajneesh loves figuring out “how things work” and shares this enthusiasm through book reviews, exploring tech, leadership, and innovation with technical depth and approachable storytelling.

Table of Contents

Preface

1

What is New in PostgreSQL 17

Understanding DBA and administration features

Terminating long transactions

Improved event triggers

Inspecting wait events in PostgreSQL

Digging into checkpoints and background writing

Improving pg_stat_statements

Adding permissions for maintenance tasks

Using SQL and developer features

Teaching COPY error handling

Splitting and merging partitions

Tuning numbers into binary and octal values

Improving MERGE even more

Additional JSON functionality

Creating BRIN indexes in parallel

Making use of new replication and backup add-ons

More powerful pg_dump, again

Handling incremental base backups

Logical replication upgraded

Adding pg_createsubscriber

Considering breaking changes in PostgreSQL 17

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

Making use of more VACUUM features

Summary

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 more than one index at a time

Using bitmap scans effectively

Using indexes intelligently

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

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

Making use of JSONPath

Summary

5

Log Files and System Statistics

Gathering runtime statistics

pg_stat_activity – checking live traffic

Inspecting databases

Inspecting tables

Making sense of pg_stat_user_tables

Digging into indexes

Tracking the background writer

Inspecting I/O statistics

Tracking, archiving, and streaming

Checking SSL connections

Inspecting transactions in real time

Tracking VACUUM and CREATE INDEX progress

Using pg_stat_statements

Creating log files

Defining log destination and rotation

Configuring syslog

Logging slow queries

Defining what and how to log

Monitoring replication conflicts

Summary

6

Optimizing Queries for Good Performance

Learning what the PostgreSQL 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 17.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’s PostgreSQL able to do in parallel?

Parallelism in practice

Introducing just-in-time (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

8

Managing PostgreSQL Security

Managing network security

Understanding bind addresses and connections

Managing the pg_hba.conf file

Inspecting the content of pg_hba.conf via SQL

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 and handling permissions

Reassigning objects and dropping users

Summary

9

Handling Backup and Recovery

Performing simple dumps

Running pg_dump

Passing passwords and using the service file

Extracting subsets of data

Handling various formats

Replaying backups

Handling global data

Summary

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

Making use of incremental backups

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

Summary

11

Deciding on Useful Extensions

Understanding how extensions work

Checking for available extensions

Making use of contrib modules

Applying bloom filters

Deploying btree_gist and btree_gin

dblink – make sure it is outphased

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

Classical cloud and Kubernetes problems

CPU throttling – capacity control unleashed

Summary

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 the FETCH FIRST clause

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

CYBERTEC Migrator – large-scale migrations

Using Ora2Pg to migrate from Oracle

Common pitfalls

Summary

Index

Other Books You May Enjoy

Preface

Welcome to Mastering PostgreSQL 17, the ultimate guide to unlocking the full potential of one of the world’s most popular open source relational databases – PostgreSQL. With decades of history and a community-driven development process, PostgreSQL has become the go-to choice for organizations seeking a robust, scalable, and reliable database solution. This has been true for many years and this will be the case for many years to come.

In this book, we’ll take you on a comprehensive journey through the latest features and enhancements in PostgreSQL 17, the newest major release of the database system. Whether you’re a seasoned DBA looking to expand your skillset or a developer seeking to improve your application’s performance and scalability, this book is designed to help you master the art of working with PostgreSQL and it will hopefully be an enjoyable thing to read that helps you to understand things better, be more productive, and simply have a better time.

Mastering the art of handling data is an ever more important skill that is important to have. In a digital world, “data” is more or less the “new oil” – an important asset that drives the world and the importance of data is growing as we speak. Every sector of IT is data-driven. It does not matter whether you are at the forefront of machine learning or whether you are working on bookkeeping software – at the end of the day, IT is all about data.

PostgreSQL has become a hot technology in the area of open source, and it is an excellent technology to store and process data in the most efficient way possible. This book will teach you how to use PostgreSQL in the most professional way and explain how to operate, optimize, and monitor this core technology, which has become so popular over the years.

By the end of the book, you will be able to use PostgreSQL to its utmost capacity by applying advanced technology and cutting-edge features.

Who this book is for

This book is tailored for database administrators, PostgreSQL developers, and IT professionals aiming to implement advanced functionalities and tackle complex administrative tasks using PostgreSQL 17. A foundational understanding of PostgreSQL and core database concepts is essential, along with familiarity with SQL. Prior experience in database administration will enhance your ability to leverage the advanced techniques discussed throughout the book.

What this book covers

Chapter 1, What is New in PostgreSQL 17, guides you through the most important features that have made it into the new release of PostgreSQL and explains how those features can be used.

Chapter 2, Understanding Transactions and Locking, explains the fundamental concepts of transactions and locking. Both topics are key requirements to understand storage management in PostgreSQL.

Chapter 3, Making Use of Indexes, introduces the concept of indexes, which are the key ingredient when dealing with performance in general. You will learn about simple indexes as well as more sophisticated concepts.

Chapter 4, Handling Advanced SQL, unleashes the full power of SQL and outlines the most advanced functionality a query language has to offer. You will learn about windowing functions, ordered sets, hypothetical aggregates, and a lot more. All those techniques will open a totally new world of functionality.

Chapter 5, Log Files and System Statistics, explains how you can use runtime statistics collected by PostgreSQL to make operations easier and to debug the database. You will be guided through the internal information-gathering infrastructure.

Chapter 6, Optimizing Queries for Good Performance, is all about good query performance and outlines optimization techniques that are essential to bringing your database up to speed to handle even bigger workloads.

Chapter 7, Writing Stored Procedures, introduces you to the concept of server-side code such as functions, stored procedures, and a lot more. You will learn how to write triggers and dive into server-side logic.

Chapter 8, Managing PostgreSQL Security, helps you to make your database more secure and explains what can be done to ensure safety and data protection at all levels.

Chapter 9, Handling Backup and Recovery, helps you to make copies of your database to protect yourself against crashes and database failure.

Chapter 10, Making Sense of Backups and Replication, follows up on backups and recovery and explains additional techniques, such as streaming replication, redundancy, and a lot more. It covers the most advanced topics.

Chapter 11, Deciding on Useful Extensions, explores extensions and additional useful features that can be added to PostgreSQL.

Chapter 12, Troubleshooting PostgreSQL, completes the circle of topics and explains what can be done if things don’t work as expected. You will learn how to find the most common issues and understand how problems can be fixed.

Chapter 13, Migrating to PostgreSQL, teaches you how to move your databases to PostgreSQL efficiently and quickly. It covers the most common database systems people will migrate from.

To get the most out of this book

This book has been written for a broad audience. However, some basic knowledge of SQL is necessary to follow along and make full use of the examples presented. In general, it is also a good idea to familiarize yourself with basic Unix commands as most of the book has been produced on Linux and macOS.

Software/hardware covered in the book

Operating system requirements

pgAdmin4

Windows, macOS, or Linux

PostgreSQL 17

SQL Shell (psql)

Note

Some parts of chapters, that is, 8, 9, 10, 11, 12, and 13 are mostly dedicated to Unix/Linux and macOS users, and the rest run fine on Windows.

Conventions used

There are a number of text conventions used throughout this book.

Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: “The community has removed this feature and introduced a new variable called transaction_timeout, which can be set per session.”

A block of code is set as follows:

CREATE OR REPLACE FUNCTION on_login_proc() RETURNS event_trigger AS $$ BEGIN     INSERT INTO user_lo (w) VALUES (SESSION_USER);     RAISE NOTICE 'You are welcome!'; END; $$ LANGUAGE plpgsql;

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

test=# SHOW event_triggers; event_triggers

Any command-line input or output is written as follows:

test=# CREATE TABLE t_data (     id    int,     data  text );

Bold: Indicates a new term, an important word, or words that you see onscreen. For instance, words in menus or dialog boxes appear in bold. Here is an example: “A new process called summarizer was added to PostgreSQL.”

Tips or important notes

Appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, email us at [email protected] and mention the book title in the subject of your message.

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata and fill in the form.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Share Your Thoughts

Once you’ve read Mastering PostgreSQL 17 – Sixth Edition, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Download a free PDF copy of this book

Thanks for purchasing this book!

Do you like to read on the go but are unable to carry your print books everywhere?

Is your eBook purchase not compatible with the device of your choice?

Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.

Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application.

The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily

Follow these simple steps to get the benefits:

Scan the QR code or visit the link below

https://packt.link/free-ebook/978-1-83620-597-5

Submit your proof of purchaseThat’s it! We’ll send your free PDF and other benefits to your email directly