product iamge

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

This document was uploaded by one of our users. The uploader already confirmed that they had the permission to publish it. If you are author/publisher or own the copyright of this documents, please report to us by using this DMCA report form.

Download
Search on Amazon

Simply click on the Download Book button.

Yes, Book downloads on Ebookily are 100% Free.

Sometimes the book is free on Amazon As well, so go ahead and hit "Search on Amazon"

Master the capabilities of PostgreSQL 15 to efficiently administer and maintain your database.

Purchase of the print or Kindle book includes a free PDF eBook.

Key Features

  • Using real world examples, get to grips with expert PostgreSQL 15 concepts
  • Explore performance improvement, database replication, data replication, database administration, and more
  • Extend PostgreSQL's functionalities to suit your organization's needs with minimal effort

Book Description

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.

Table of Contents

  1. PostgreSQL 15 Overview
  2. Understanding Transactions and Locking
  3. Making Use of Indexes
  4. Handling Advanced SQL
  5. Log Files and System Statistics
  6. Optimizing Queries for Good Performance
  7. Writing Stored Procedures
  8. Managing PostgreSQL Security
  9. Handling Backup and Recovery
  10. Making Sense of Backups and Replication
  11. Deciding on Useful Extensions
  12. Troubleshooting PostgreSQL
  13. Migrating to PostgreSQL

Author(s): Hans-Jürgen Schönig
Edition: 5
Publisher: Packt Publishing
Year: 2023

Language: English
Pages: 522
City: Birmingham
Tags: PostgreSQL; Database Replication; Data Replication; Database Administration; Advanced Indexing; PostgreSQL Optimizer; Oracle; JSON; JSONB; Patroni Cluster; Query Optimization

Cover
Title Page
Copyright and Credits
Contributors
Table of Contents
Preface
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 5: Log Files and System Statistics
Gathering runtime statistics
Working with PostgreSQL system views
Creating log files
Configuring the postgresql.conf file
Summary
Questions
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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