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

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