Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications, 5th Edition
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.
- 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
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.
Mastering PostgreSQL 15 Contributors About the author About the reviewers Preface Who this book is for What this book covers To get the most out of this book Download the example code files Conventions used Get in touch Share Your Thoughts Download a free PDF copy of this book 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 Why subscribe? Other Books You May Enjoy Packt is searching for authors like you Share Your Thoughts Download a free PDF copy of this book
How to download source code?
1. Go to:
2. In the Find a repository… box, search the book title:
Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications, 5th Edition, sometime you may not get the results, please search the main title.
3. Click the book title in the search results.
3. Click Code to download.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.