Mastering PostgreSQL 15 advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications
Updated to keep up with the latest version of PostgreSQL, this fifth edition will help you build dynamic database solutions for enterprise applications using real-world hands-on examples, enabling you to design both the physical and technical aspects of the system architecture with ease.
| Otros Autores: | |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
Birmingham, England :
Packt Publishing, Limited
[2023]
|
| Edición: | Fifth edition |
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009720312806719 |
Tabla de Contenidos:
- Cover
- Title Page
- Copyright and Credits
- Contributors
- Table of Contents
- Preface
- 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.
