book

Mastering PostgreSQL 13 - Fourth Edition

Name: Mastering PostgreSQL 13 - Fourth Edition
Author: Hans-Jürgen Schönig
ISBN: 9781800567498

by Hans-Jürgen Schönig

November 2020

Intermediate to advanced

476 pages

10h 4m

English

Packt Publishing

Read now

Unlock full access

Title Page
Copyright and Credits
Mastering PostgreSQL 13 Fourth Edition
About Packt
Why subscribe?
Contributors
About the authorAbout the reviewerPackt is searching for authors like you
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesConventions usedGet in touchReviews
PostgreSQL 13 Overview
What's new in PostgreSQL 13?Digging into SQL and developer-related topicsImproving psql command-line handlingImproving pgbenchGenerating random UUIDs more easilyDropping databases fasterAdding ALTER TABLE ... DROP EXPRESSION ...Making use of performance improvementsDeduplication of B-tree indexesAdding incremental sortingAdding -j 8 to reindexdbAllowing hash aggregates to spill to diskSpeeding up PL/pgSQLParallelizing VACUUM operationsAllowing skipping of WAL for full table writes Additional performance improvementsMaking monitoring more powerfulAdditional system viewsSummary
Understanding Transactions and Locking
Working with PostgreSQL transactionsHandling errors inside a transactionMaking use of SAVEPOINTTransactional DDLsUnderstanding basic lockingAvoiding typical mistakes and explicit lockingConsidering alternative solutionsMaking 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 autovacuumDigging into transaction wraparound-related issuesA word on VACUUM FULLWatching VACUUM at workLimiting transactions by making use of snapshot too oldMaking use of more VACUUM featuresSummaryQuestions
Making Use of Indexes
Understanding simple queries and the cost modelMaking use of EXPLAINDigging into the PostgreSQL cost modelDeploying simple indexesMaking use of sorted outputUsing more than one index at a timeUsing 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-treeCreating new operatorsCreating operator classesTesting custom operator classesUnderstanding PostgreSQL index typesHash indexesGiST indexesUnderstanding how GiST worksExtending GiSTGIN indexesExtending GINSP-GiST indexesBRIN indexesExtending BRIN indexesAdding additional indexesAchieving better answers with fuzzy searchingTaking advantage of pg_trgmSpeeding up LIKE queriesHandling regular expressionsUnderstanding full-text searchComparing stringsDefining GIN indexesDebugging your searchGathering word statisticsTaking advantage of exclusion operatorsSummaryQuestions
Handling Advanced SQL
Introducing 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 windowsUnderstanding the subtle difference between ROWS and RANGERemoving duplicates using EXCLUDE TIES and EXCLUDE GROUPAbstracting window clausesUsing on-board windowing functionsThe rank and dense_rank functionsThe ntile() functionThe lead() and lag() functionsThe first_value(), nth_value(), and last_value() functionsThe row_number() functionWriting your own aggregatesCreating simple aggregatesAdding support for parallel queriesImproving efficiencyWriting hypothetical aggregatesSummary
Log Files and System Statistics
Gathering runtime statisticsWorking with PostgreSQL system viewsChecking live trafficInspecting databasesInspecting tablesMaking sense of pg_stat_user_tablesDigging into indexesTracking the background workerTracking, archiving, and streamingChecking SSL connectionsInspecting transactions in real timeTracking VACUUM and CREATE INDEX progressUsing pg_stat_statementsCreating log filesConfiguring the postgresql.conf fileDefining log destination and rotationConfiguring syslogLogging slow queriesDefining what and how to logSummaryQuestions

Optimizing Queries for Good Performance
Learning what the optimizer doesA practical example – How the query optimizer handles a sample queryEvaluating join optionsNested loopsHash joinsMerge joinsApplying transformationsApplying equality constraintsExhaustive searchingChecking out execution plansMaking the process failConstant foldingUnderstanding function inliningIntroducing join pruningSpeedup set operationsUnderstanding execution plansApproaching plans systematicallyMaking EXPLAIN more verboseSpotting problemsSpotting changes in runtimeInspecting estimatesInspecting buffer usageFixing high buffer usageUnderstanding 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 13.x partitioningAdjusting 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
Writing Stored Procedures
Understanding stored procedure languagesUnderstanding the fundamentals of stored procedures versus functionsThe anatomy of a functionIntroducing dollar quotingMaking use of anonymous code blocksUsing functions and transactionsExploring various stored procedure languagesIntroducing PL/pgSQLHandling quoting and the string formatManaging scopesUnderstanding advanced error handlingMaking use of GET DIAGNOSTICSUsing cursors to fetch data in chunksUtilizing composite typesWriting triggers in PL/pgSQLWriting stored procedures in PL/pgSQLIntroducing PL/PerlUsing PL/Perl for data type abstractionDeciding between PL/Perl and PL/PerlUMaking use of the SPI interfaceUsing the SPI for set returning functionsEscaping in PL/Perl and support functionsSharing data across function callsWriting triggers in PerlIntroducing PL/PythonWriting simple PL/Python codeUsing the SPI interfaceHandling errorsImproving functionsReducing the number of function callsUsing cached plansAssigning costs to functionsUsing functions for various purposesSummaryQuestions
Managing PostgreSQL Security
Managing network securityUnderstanding bind addresses and connectionsInspecting connections and performanceLiving in a world without TCPManaging pg_hba.confHandling SSLHandling instance-level securityCreating and modifying usersDefining database-level securityAdjusting schema-level permissionsWorking with tablesHandling column-level securityConfiguring default privilegesDigging into RLSInspecting permissionsReassigning objects and dropping usersSummaryQuestions
Handling Backup and Recovery
Performing simple dumpsRunning pg_dumpPassing passwords and connection informationUsing environment variablesMaking use of .pgpassUsing service filesExtracting subsets of dataHandling various formatsReplaying backupsHandling global dataSummaryQuestions
Making Sense of Backups and Replication
Understanding the transaction logLooking at the transaction logUnderstanding checkpointsOptimizing the transaction logTransaction log archiving and recoveryConfiguring for archivingConfiguring the pg_hba.conf fileCreating base backupsReducing the bandwidth of backupsMapping tablespacesUsing different formatsTesting transaction log archivingReplaying the transaction logFinding the right timestampCleaning up the transaction log archiveSetting up asynchronous replicationPerforming a basic setupImproving securityHalting 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 slotsUse cases for logical replication slotsMaking use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commandsSummaryQuestions
Deciding on Useful Extensions
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_fdwHandling mistakes and typosOther useful extensionsSummary
Troubleshooting PostgreSQL
Approaching an unknown databaseInspecting pg_stat_activityQuerying pg_stat_activityTreating Hibernate statementsFiguring out where queries come fromChecking 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 to PostgreSQL
Migrating SQL statements to PostgreSQLUsing lateral joinsSupporting lateral joinsUsing grouping setsSupporting grouping setsUsing the WITH clause – common table expressionsSupporting the WITH clauseUsing the WITH RECURSIVE clauseSupporting the WITH RECURSIVE clauseUsing the FILTER clauseSupporting the FILTER clauseUsing windowing functionsSupporting windowing and analyticsUsing ordered sets – the WITHIN GROUP clauseSupporting the WITHIN GROUP clauseUsing the TABLESAMPLE clauseSupporting the TABLESAMPLE clauseUsing limit/offsetSupporting the FETCH FIRST clauseUsing the OFFSET clauseSupporting the OFFSET clauseUsing temporal tablesSupporting 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 pitfallsHandling data in MySQL and MariaDBChanging column definitionsHandling null valuesExpecting problemsMigrating data and schemasUsing pg_chameleonUsing FDWsSummary
Other Books You May Enjoy
Leave a review - let other readers know what you think

Overview

Dive into PostgreSQL 13 with this comprehensive guide that equips you to build, manage, and optimize database applications using state-of-the-art features. With a strong focus on hands-on insights, this book covers everything from SQL functions to advanced replication, helping you to enhance your database management expertise.

What this Book will help me do

Understand and utilize advanced SQL features to increase database efficiency.
Optimize your PostgreSQL queries for improved performance in applications.
Implement robust backup, recovery, and replication strategies for data integrity.
Migrate seamlessly from Oracle to PostgreSQL using proven strategies.
Strengthen server security to safeguard sensitive data in your PostgreSQL system.

Author(s)

Hans-Jürgen Schönig is a renowned PostgreSQL expert with decades of experience in database administration and consulting. He has guided companies across the globe to leverage the power of PostgreSQL, achieving high performance and reliability in their applications. His clear, methodical, and practical approach makes complex topics accessible to database professionals.

Who is it for?

This book is crafted for PostgreSQL database administrators and developers with some prior experience. If you are looking to refine your skills and adopt advanced features in PostgreSQL 13 to enhance performance and manageability, this book is ideal for you. It is best suited for individuals who aim to make their database applications more secure and robust.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781800567498

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills