Mastering PostgreSQL 13 - Fourth Edition

Mastering PostgreSQL 13 - Fourth Edition

by Hans-Jurgen Schonig
Released November 2020
Publisher(s): Packt Publishing
ISBN: 9781800567498

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Explore expert techniques such as advanced indexing and high availability to build scalable, reliable, and fault-tolerant database applications using PostgreSQL 13

Key Features

  • Master advanced PostgreSQL 13 concepts with the help of real-world datasets and examples
  • Leverage PostgreSQL's indexing features to fine-tune the performance of your queries
  • Extend PostgreSQL's functionalities to suit your organization's needs with minimal effort

Book Description

Thanks to its reliability, robustness, and high performance, PostgreSQL has become one of the most advanced open source databases on the market. This updated fourth edition will help you understand PostgreSQL administration and how to build dynamic database solutions for enterprise apps with the latest release of PostgreSQL, including designing both physical and technical aspects of the system architecture with ease.

Starting with an introduction to the new features in PostgreSQL 13, this book will guide you in building efficient and fault-tolerant PostgreSQL apps. You'll explore advanced PostgreSQL features, such as logical replication, database clusters, performance tuning, advanced indexing, monitoring, 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. The book also covers transactions, locking, and indexes, and shows you how to improve performance with query optimization. You'll also focus on how to manage 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 get the most out of your database by executing advanced administrative tasks.

What you will learn

  • Get well versed with advanced SQL functions in PostgreSQL 13
  • Get to grips with administrative tasks such as log file management and monitoring
  • Work with stored procedures and manage backup and recovery
  • Employ replication and failover techniques to reduce data loss
  • Perform database migration from Oracle to PostgreSQL with ease
  • Replicate PostgreSQL database systems to create backups and scale your database
  • Manage and improve server security to protect your data
  • Troubleshoot your PostgreSQL instance to find solutions to common and not-so-common problems

Who this book is for

This database administration book is for PostgreSQL developers and database administrators and professionals who want to implement advanced functionalities and master complex administrative tasks with PostgreSQL 13. 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. Title Page
  2. Copyright and Credits
    1. Mastering PostgreSQL 13 Fourth Edition
  3. About Packt
    1. Why subscribe?
  4. Contributors
    1. About the author
    2. About the reviewer
    3. Packt is searching for authors like you
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Conventions used
    4. Get in touch
      1. Reviews
  6. PostgreSQL 13 Overview
    1. What's new in PostgreSQL 13?
      1. Digging into SQL and developer-related topics
        1. Improving psql command-line handling
        2. Improving pgbench
        3. Generating random UUIDs more easily
        4. Dropping databases faster
        5. Adding ALTER TABLE ... DROP EXPRESSION ...
      2. Making use of performance improvements
        1. Deduplication of B-tree indexes
        2. Adding incremental sorting
        3. Adding -j 8 to reindexdb
        4. Allowing hash aggregates to spill to disk
        5. Speeding up PL/pgSQL
        6. Parallelizing VACUUM operations
        7. Allowing skipping of WAL for full table writes 
        8. Additional performance improvements
      3. Making monitoring more powerful
        1. Additional system views
    2. Summary
  7. Understanding Transactions and Locking
    1. Working with PostgreSQL transactions
      1. Handling errors inside a transaction
      2. Making use of SAVEPOINT
      3. Transactional DDLs
    2. Understanding basic locking
      1. Avoiding typical mistakes and explicit locking
        1. Considering alternative solutions
    3. Making use of FOR SHARE and FOR UPDATE
    4. Understanding transaction isolation levels
      1. Considering Serializable Snapshot Isolation transactions
    5. Observing deadlocks and similar issues
    6. Utilizing advisory locks
    7. Optimizing storage and managing cleanup
      1. Configuring VACUUM and autovacuum
        1. Digging into transaction wraparound-related issues
        2. A word on VACUUM FULL
      2. Watching VACUUM at work
      3. Limiting transactions by making use of snapshot too old
      4. Making use of more VACUUM features
    8. Summary
    9. Questions
  8. Making Use of Indexes
    1. Understanding simple queries and the cost model
      1. Making use of EXPLAIN
      2. Digging into the PostgreSQL cost model
      3. Deploying simple indexes
      4. Making use of sorted output
        1. Using more than one index at a time
      5. Using bitmap scans effectively
      6. Using indexes in an intelligent way
      7. Understanding index de-duplication
    2. Improving speed using clustered tables
      1. Clustering tables
      2. Making use of index-only scans
    3. Understanding additional B-tree features
      1. Combined indexes
      2. Adding functional indexes
      3. Reducing space consumption
      4. Adding data while indexing
    4. Introducing operator classes
      1. Creating an operator class for a B-tree
        1. Creating new operators
        2. Creating operator classes
        3. Testing custom operator classes
    5. Understanding PostgreSQL index types
      1. Hash indexes
      2. GiST indexes
        1. Understanding how GiST works
        2. Extending GiST
      3. GIN indexes
        1. Extending GIN
      4. SP-GiST indexes
      5. BRIN indexes
        1. Extending BRIN indexes
      6. Adding additional indexes
    6. Achieving better answers with fuzzy searching
      1. Taking advantage of pg_trgm
      2. Speeding up LIKE queries
      3. Handling regular expressions
    7. Understanding full-text search
      1. Comparing strings
      2. Defining GIN indexes
      3. Debugging your search
      4. Gathering word statistics
      5. Taking advantage of exclusion operators
    8. Summary
    9. Questions
  9. Handling Advanced SQL
    1. Introducing grouping sets
      1. Loading some sample data
      2. Applying grouping sets
      3. Investigating performance
      4. Combining grouping sets with the FILTER clause
    2. Making use of ordered sets
    3. Understanding hypothetical aggregates
    4. Utilizing windowing functions and analytics
      1. Partitioning data
      2. Ordering data inside a window
      3. Using sliding windows
        1. Understanding the subtle difference between ROWS and RANGE
        2. Removing duplicates using EXCLUDE TIES and EXCLUDE GROUP
      4. Abstracting window clauses
      5. Using on-board windowing functions
        1. The rank and dense_rank functions
        2. The ntile() function
        3. The lead() and lag() functions
        4. The first_value(), nth_value(), and last_value() functions
        5. The row_number() function
    5. Writing your own aggregates
      1. Creating simple aggregates
      2. Adding support for parallel queries
      3. Improving efficiency
      4. Writing hypothetical aggregates
    6. Summary
  10. Log Files and System Statistics
    1. Gathering runtime statistics
      1. Working with PostgreSQL system views
        1. Checking live traffic
        2. Inspecting databases
          1. Inspecting tables
        3. Making sense of pg_stat_user_tables
        4. Digging into indexes
        5. Tracking the background worker
        6. Tracking, archiving, and streaming
        7. Checking SSL connections
        8. Inspecting transactions in real time
        9. Tracking VACUUM and CREATE INDEX progress
        10. Using pg_stat_statements
    2. Creating log files
      1. Configuring the postgresql.conf file
        1. Defining log destination and rotation
        2. Configuring syslog
        3. Logging slow queries
        4. Defining what and how to log
    3. Summary
    4. Questions
  11. Optimizing Queries for Good Performance
    1. Learning what the optimizer does
      1. A practical example – How the query optimizer handles a sample query
        1. Evaluating join options
          1. Nested loops
          2. Hash joins
          3. Merge joins
        2. Applying transformations
        3. Applying equality constraints
        4. Exhaustive searching
        5. Checking out execution plans
        6. Making the process fail
        7. Constant folding
        8. Understanding function inlining
        9. Introducing join pruning
        10. Speedup set operations
    2. Understanding execution plans
      1. Approaching plans systematically
        1. Making EXPLAIN more verbose
      2. Spotting problems
        1. Spotting changes in runtime
        2. Inspecting estimates
        3. Inspecting buffer usage
        4. Fixing high buffer usage
    3. Understanding and fixing joins
      1. Getting joins right
      2. Processing outer joins
      3. Understanding the join_collapse_limit variable
    4. Enabling and disabling optimizer settings
      1. Understanding genetic query optimization
    5. Partitioning data
      1. Creating inherited tables
      2. Applying table constraints
      3. Modifying inherited structures
      4. Moving tables in and out of partitioned structures
      5. Cleaning up data
      6. Understanding PostgreSQL 13.x partitioning
    6. Adjusting parameters for good query performance
      1. Speeding up sorting
      2. Speeding up administrative tasks
    7. Making use of parallel queries
      1. What is PostgreSQL able to do in parallel?
      2. Parallelism in practice
    8. Introducing JIT compilation
      1. Configuring JIT
      2. Running queries
    9. Summary
  12. Writing Stored Procedures
    1. Understanding stored procedure languages
      1. Understanding the fundamentals of stored procedures versus functions
      2. The anatomy of a function
        1. Introducing dollar quoting
        2. Making use of anonymous code blocks
        3. Using functions and transactions
    2. Exploring various stored procedure languages
      1. Introducing PL/pgSQL
        1. Handling quoting and the string format
        2. Managing scopes
        3. Understanding advanced error handling
        4. Making use of GET DIAGNOSTICS
        5. Using cursors to fetch data in chunks
        6. Utilizing composite types
        7. Writing triggers in PL/pgSQL
      2. Writing stored procedures in PL/pgSQL
      3. Introducing PL/Perl
        1. Using PL/Perl for data type abstraction
        2. Deciding between PL/Perl and PL/PerlU
        3. Making use of the SPI interface
        4. Using the SPI for set returning functions
        5. Escaping in PL/Perl and support functions
        6. Sharing data across function calls
        7. Writing triggers in Perl
      4. Introducing PL/Python
        1. Writing simple PL/Python code
        2. Using the SPI interface
        3. Handling errors
    3. Improving functions
      1. Reducing the number of function calls
        1. Using cached plans
        2. Assigning costs to functions
    4. Using functions for various purposes
    5. Summary
    6. Questions
  13. Managing PostgreSQL Security
    1. Managing network security
      1. Understanding bind addresses and connections
        1. Inspecting connections and performance
        2. Living in a world without TCP
      2. Managing pg_hba.conf
        1. Handling SSL
      3. Handling instance-level security
        1. Creating and modifying users
      4. Defining database-level security
      5. Adjusting schema-level permissions
      6. Working with tables
      7. Handling column-level security
      8. Configuring default privileges
    2. Digging into RLS
    3. Inspecting permissions
    4. Reassigning objects and dropping users
    5. Summary
    6. Questions
  14. Handling Backup and Recovery
    1. Performing simple dumps
      1. Running pg_dump
      2. Passing passwords and connection information
        1. Using environment variables
        2. Making use of .pgpass
        3. Using service files
      3. Extracting subsets of data
    2. Handling various formats
    3. Replaying backups
    4. Handling global data
    5. Summary
    6. Questions
  15. Making Sense of Backups and Replication
    1. Understanding the transaction log
      1. Looking at the transaction log
      2. Understanding checkpoints
      3. Optimizing the transaction log
    2. Transaction log archiving and recovery
      1. Configuring for archiving
      2. Configuring the pg_hba.conf file
      3. Creating base backups
        1. Reducing the bandwidth of backups
        2. Mapping tablespaces
        3. Using different formats
        4. Testing transaction log archiving
      4. Replaying the transaction log
        1. Finding the right timestamp
      5. Cleaning up the transaction log archive
    3. Setting up asynchronous replication
      1. Performing a basic setup
        1. Improving security
      2. Halting and resuming replication
      3. Checking replication to ensure availability
      4. Performing failovers and understanding timelines
      5. Managing conflicts
      6. Making replication more reliable
    4. Upgrading to synchronous replication
      1. Adjusting durability
    5. Making use of replication slots
      1. Handling physical replication slots
      2. Handling logical replication slots
        1. Use cases for logical replication slots
    6. Making use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commands
    7. Summary
    8. Questions
  16. Deciding on Useful Extensions
    1. Understanding how extensions work
      1. Checking for available extensions
    2. Making use of contrib modules
      1. Using the adminpack module
      2. Applying bloom filters
      3. Deploying btree_gist and btree_gin
      4. dblink – considering phasing out
      5. Fetching files with file_fdw
      6. Inspecting storage using pageinspect
      7. Investigating caching with pg_buffercache
      8. Encrypting data with pgcrypto
      9. Prewarming caches with pg_prewarm
      10. Inspecting performance with pg_stat_statements
      11. Inspecting storage with pgstattuple
      12. Fuzzy searching with pg_trgm
      13. Connecting to remote servers using postgres_fdw
        1. Handling mistakes and typos
    3. Other useful extensions
    4. Summary
  17. Troubleshooting PostgreSQL
    1. Approaching an unknown database
    2. Inspecting pg_stat_activity
      1. Querying pg_stat_activity
        1. Treating Hibernate statements
        2. Figuring out where queries come from
    3. Checking for slow queries
      1. Inspecting individual queries
      2. Digging deeper with perf
    4. Inspecting the log
    5. Checking for missing indexes
    6. Checking for memory and I/O
    7. Understanding noteworthy error scenarios
      1. Facing clog corruption
      2. Understanding checkpoint messages
      3. Managing corrupted data pages
      4. Careless connection management
      5. Fighting table bloat
    8. Summary
    9. Questions
  18. Migrating to PostgreSQL
    1. Migrating SQL statements to PostgreSQL
      1. Using lateral joins
        1. Supporting lateral joins
      2. Using grouping sets
        1. Supporting grouping sets
      3. Using the WITH clause – common table expressions
        1. Supporting the WITH clause
      4. Using the WITH RECURSIVE clause
        1. Supporting the WITH RECURSIVE clause
      5. Using the FILTER clause
        1. Supporting the FILTER clause
      6. Using windowing functions
        1. Supporting windowing and analytics
      7. Using ordered sets – the WITHIN GROUP clause
        1. Supporting the WITHIN GROUP clause
      8. Using the TABLESAMPLE clause
        1. Supporting the TABLESAMPLE clause
      9. Using limit/offset
        1. Supporting the FETCH FIRST clause
      10. Using the OFFSET clause
        1. Supporting the OFFSET clause
      11. Using temporal tables
        1. Supporting temporal tables
      12. Matching patterns in time series
    2. Moving from Oracle to PostgreSQL
      1. Using the oracle_fdw extension to move data
      2. Using ora_migrator for fast migration
      3. CYBERTEC Migrator – migration for the "big boys"
      4. Using Ora2Pg to migrate from Oracle
      5. Common pitfalls
      6. Handling data in MySQL and MariaDB
        1. Changing column definitions
        2. Handling null values
        3. Expecting problems
      7. Migrating data and schemas
        1. Using pg_chameleon
        2. Using FDWs
    3. Summary
  19. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Mastering PostgreSQL 13 - Fourth Edition
  • Author(s): Hans-Jurgen Schonig
  • Release date: November 2020
  • Publisher(s): Packt Publishing
  • ISBN: 9781800567498