book

Mastering PostgreSQL 15 - Fifth Edition

by Hans-Jürgen Schönig

January 2023

Intermediate to advanced

522 pages

11h 26m

English

Packt Publishing

Read now

Unlock full access

ContributorsAbout the authorAbout the reviewers
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesConventions usedGet in touchShare Your ThoughtsDownload a free PDF copy of this book
Making use of DBA-related featuresRemoving support for old pg_dumpDeprecating Python 2Fixing the public schemaAdding pre-defined rolesAdding permissions to variablesImproving pg_stat_statementsNew wait eventsAdding logging functionalityUnderstanding developer-related featuresSecurity invoker viewsICU localesBetter numericHandling ON DELETEWorking around NULL and UNIQUEAdding the MERGE command to PostgreSQLUsing performance-related featuresAdding multiple compression algorithmsHandling parallel queries more efficientlyImproved statistics handlingPrefetching during WAL recoveryAdditional replication featuresTwo-phase commit for logical decodingAdding row and column filteringImproving ALTER SUBSCRIPTIONSupporting compressed base backupsIntroducing archiving librariesSummary
Working with PostgreSQL transactionsHandling errors inside a transactionMaking use of SAVEPOINTTransactional DDLsUnderstanding basic lockingAvoiding typical mistakes and explicit lockingMaking use of FOR SHARE and FOR UPDATEUnderstanding transaction isolation levelsConsidering serializable snapshot isolation transactionsObserving deadlocks and similar issuesUtilizing advisory locksOptimizing storage and managing cleanupConfiguring VACUUM and autovacuumWatching VACUUM at workLimiting transactions by making use of snapshot too oldMaking use of more VACUUM featuresSummaryQuestions
Understanding simple queries and the cost modelMaking use of EXPLAINDigging into the PostgreSQL cost modelDeploying simple indexesMaking use of sorted outputUsing bitmap scans effectivelyUsing indexes in an intelligent wayUnderstanding index de-duplicationImproving speed using clustered tablesClustering tablesMaking use of index-only scansUnderstanding additional B-tree featuresCombined indexesAdding functional indexesReducing space consumptionAdding data while indexingIntroducing operator classesCreating an operator class for a B-treeUnderstanding PostgreSQL index typesHash indexesGiST indexesGIN indexesSP-GiST indexesBRINsAdding additional indexesAchieving better answers with fuzzy searchingTaking advantage of pg_trgmSpeeding up LIKE queriesHandling regular expressionsUnderstanding full-text searchesComparing stringsDefining GIN indexesDebugging your searchGathering word statisticsTaking advantage of exclusion operatorsSummaryQuestions
Supporting range typesQuerying ranges efficientlyHandling multirange typesWhen to use range typesIntroducing grouping setsLoading some sample dataApplying grouping setsInvestigating performanceCombining grouping sets with the FILTER clauseMaking use of ordered setsUnderstanding hypothetical aggregatesUtilizing windowing functions and analyticsPartitioning dataOrdering data inside a windowUsing sliding windowsAbstracting window clausesUsing on-board windowing functionsWriting your own aggregatesCreating simple aggregatesAdding support for parallel queriesImproving efficiencyWriting hypothetical aggregatesHandling recursionsUNION versus UNION ALLInspecting a practical exampleWorking with JSON and JSONBDisplaying and creating JSON documentsTurning JSON documents into rowsAccessing a JSON documentSummary
Gathering runtime statisticsWorking with PostgreSQL system viewsCreating log filesConfiguring the postgresql.conf fileSummaryQuestions
Learning what the optimizer doesA practical example – how the query optimizer handles a sample queryUnderstanding execution plansApproaching plans systematicallySpotting problemsUnderstanding and fixing joinsGetting joins rightProcessing outer joinsUnderstanding the join_collapse_limit variableEnabling and disabling optimizer settingsUnderstanding genetic query optimizationPartitioning dataCreating inherited tablesApplying table constraintsModifying inherited structuresMoving tables in and out of partitioned structuresCleaning up dataUnderstanding PostgreSQL 15.x partitioningHandling partitioning strategiesUsing range partitioningUtilizing list partitioningHandling hash partitionsAdjusting parameters for good query performanceSpeeding up sortingSpeeding up administrative tasksMaking use of parallel queriesWhat is PostgreSQL able to do in parallel?Parallelism in practiceIntroducing JIT compilationConfiguring JITRunning queriesSummary
Understanding stored procedure languagesUnderstanding the fundamentals of stored procedures versus functionsThe anatomy of a functionExploring various stored procedure languagesIntroducing PL/pgSQLWriting stored procedures in PL/pgSQLIntroducing PL/PerlIntroducing PL/PythonImproving functionsReducing the number of function callsUsing functions for various purposesSummaryQuestions
Managing network securityUnderstanding bind addresses and connectionsManaging the pg_hba.conf fileHandling instance-level securityDefining database-level securityAdjusting schema-level permissionsWorking with tablesHandling column-level securityConfiguring default privilegesDigging into row-level securityInspecting permissionsReassigning objects and dropping usersSummaryQuestions

Performing simple dumpsRunning pg_dumpPassing passwords and connection informationExtracting subsets of dataHandling various formatsReplaying backupsHandling global dataSummaryQuestions
Understanding the transaction logLooking at the transaction logUnderstanding checkpointsOptimizing the transaction logTransaction log archiving and recoveryConfiguring for archivingUsing archiving librariesConfiguring the pg_hba.conf fileCreating base backupsReplaying the transaction logCleaning up the transaction log archiveSetting up asynchronous replicationPerforming a basic setupHalting and resuming replicationChecking replication to ensure availabilityPerforming failovers and understanding timelinesManaging conflictsMaking replication more reliableUpgrading to synchronous replicationAdjusting durabilityMaking use of replication slotsHandling physical replication slotsHandling logical replication slotsMaking use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commandsSetting up an HA cluster using PatroniUnderstand how Patroni operatesInstalling PatroniCreating Patroni templatesSummaryQuestions
Understanding how extensions workChecking for available extensionsMaking use of contrib modulesUsing the adminpack moduleApplying bloom filtersDeploying btree_gist and btree_gindblink – considering phasing outFetching files with file_fdwInspecting storage using pageinspectInvestigating caching with pg_buffercacheEncrypting data with pgcryptoPrewarming caches with pg_prewarmInspecting performance with pg_stat_statementsInspecting storage with pgstattupleFuzzy searching with pg_trgmConnecting to remote servers using postgres_fdwOther useful extensionsSummary
Approaching an unknown databaseInspecting pg_stat_activityQuerying pg_stat_activityChecking for slow queriesInspecting individual queriesDigging deeper with perfInspecting the logChecking for missing indexesChecking for memory and I/OUnderstanding noteworthy error scenariosFacing clog corruptionUnderstanding checkpoint messagesManaging corrupted data pagesCareless connection managementFighting table bloatSummaryQuestions
Migrating SQL statements to PostgreSQLUsing LATERAL joinsUsing grouping setsUsing the WITH clause – common table expressionsUsing the WITH RECURSIVE clauseUsing the FILTER clauseUsing windowing functionsUsing ordered sets – the WITHIN GROUP clauseUsing the TABLESAMPLE clauseUsing limit/offsetUsing the OFFSET clauseUsing temporal tablesMatching patterns in time seriesMoving from Oracle to PostgreSQLUsing the oracle_fdw extension to move dataUsing ora_migrator for fast migrationCYBERTEC Migrator – migration for the “big boys”Using Ora2Pg to migrate from OracleCommon pitfallsSummary
Other Books You May EnjoyPackt is searching for authors like youShare Your ThoughtsDownload a free PDF copy of this book

Overview

Mastering PostgreSQL 15 provides a deep dive into advanced topics of PostgreSQL database systems, covering installation, configuration, optimization, and administration. By reading this book, you will gain the skills to manage, secure, and optimize PostgreSQL database instances for complex applications.

What this Book will help me do

Understand and utilize advanced indexing techniques in PostgreSQL to improve query performance.
Perform efficient database backup and recovery processes to ensure data safety and integrity.
Master replication and failover methods to achieve high availability and data consistency.
Implement authentication and encryption to enhance database server security.
Troubleshoot PostgreSQL to identify and resolve common operational issues.

Author(s)

Hans-Jürgen Schönig, the author of Mastering PostgreSQL 15, is a leading expert in PostgreSQL with decades of experience in the field. His passion for databases has driven him to deliver solutions in high-performance database management and large-scale data systems. With a thorough and approachable style, Hans-Jürgen imparts complex technical knowledge effectively to his readers.

Who is it for?

This book is ideal for experienced PostgreSQL developers and database administrators looking to master PostgreSQL 15. Readers should have a foundational understanding of database systems and are seeking to implement advanced features and best practices in enterprise environments. It's tailored for those aspiring to enhance their operational and architectural skills in managing PostgreSQL systems.