Mastering PostgreSQL 11 : expert techniques to build scalable, reliable, and fault-tolerant database applications

Mastering PostgreSQL 11 expert techniques to build scalable, reliable, and fault-tolerant database applications

Master the capabilities of PostgreSQL 11 to efficiently manage and maintain your database Key Features Master advanced concepts of PostgreSQL 11 with real-world datasets and examples Explore query parallelism, data replication, and database performance while working with larger datasets Extend the f...

Descripción completa

Detalles Bibliográficos
Otros Autores: Schonig, Hans-Jurgen, author (author)
Formato: Libro electrónico
Idioma:Inglés
Publicado: Birmingham : Packt 2018.
Edición:Second edition
Materias:
PostgreSQL.
Database management.
Ver en Biblioteca Universitat Ramon Llull:https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009631846506719
Tabla de Contenidos:
  • Cover
  • Title Page
  • Copyright and Credits
  • About Packt
  • Contributors
  • Table of Contents
  • Preface
  • Chapter 1: PostgreSQL Overview
  • What's new in PostgreSQL 11.0?
  • Understanding the new database administration functions
  • Using configurable WAL-segment sizes
  • Larger queryid in pg_stat_statements
  • Improved indexing and better optimization
  • Expression index statistics
  • INCLUDE indexes or covering indexes
  • Parallel index creation
  • Better cache management
  • Improving pg_prewarm
  • Enhancing windowing functions
  • Introducing just-in-time compilation
  • Enhanced partitioning
  • Adding support for stored procedures
  • Improving ALTER TABLE
  • Summary
  • Q&amp
  • A
  • 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
  • Considering alternative solutions
  • Making use of FOR SHARE and FOR UPDATE
  • Understanding transaction isolation levels
  • Considering SSI transactions
  • Observing deadlocks and similar issues
  • Utilizing advisory locks
  • Optimizing storage and managing cleanup
  • Configuring VACUUM and autovacuum
  • Digging into transaction wraparound-related issues
  • A word on VACUUM FULL
  • Watching VACUUM at work
  • Limiting transactions by making use of snapshot too old
  • Summary
  • Q&amp
  • A
  • 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 more than one index at a time
  • Using bitmap scans effectively
  • Using indexes in an intelligent way
  • Improving speed using clustered tables
  • Clustering tables
  • Making use of index-only scans.
  • Understanding additional btree features
  • Combined indexes
  • Adding functional indexes
  • Reducing space consumption
  • Adding data while indexing
  • Introducing operator classes
  • Hacking up an operator class for a btree
  • Creating new operators
  • Creating operator classes
  • Testing custom operator classes
  • Understanding PostgreSQL index types
  • Hash indexes
  • GiST indexes
  • Understanding how GiST works
  • Extending GiST
  • GIN indexes
  • Extending GIN
  • SP-GiST indexes
  • BRIN indexes
  • Extending BRIN indexes
  • Adding additional indexes
  • Achieving better answers with fuzzy searching
  • Taking advantage of pg_trgm
  • Speeding up LIKE queries
  • Handling regular expressions
  • Understanding full-text search
  • Comparing strings
  • Defining GIN indexes
  • Debugging your search
  • Gathering word statistics
  • Taking advantage of exclusion operators
  • Summary
  • Q&amp
  • A
  • Chapter 4: Handling Advanced SQL
  • 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
  • Making use of on-board windowing functions
  • The rank and dense_rank functions
  • The ntile() function
  • The lead() and lag() functions
  • The first_value(), nth_value(), and last_value() functions
  • The row_number() function
  • Writing your own aggregates
  • Creating simple aggregates
  • Adding support for parallel queries
  • Improving efficiency
  • Writing hypothetical aggregates
  • Summary
  • Chapter 5: Log Files and System Statistics
  • Gathering runtime statistics
  • Working with PostgreSQL system views
  • Checking live traffic.
  • Inspecting databases
  • Inspecting tables
  • Making sense of pg_stat_user_tables
  • Digging into indexes
  • Tracking the background worker
  • Tracking, archiving, and streaming
  • Checking SSL connections
  • Inspecting transactions in real time
  • Tracking vacuum progress
  • Using pg_stat_statements
  • Creating log files
  • Configuring the postgresql.conf file
  • Defining log destination and rotation
  • Configuring syslog
  • Logging slow queries
  • Defining what and how to log
  • Summary
  • Q&amp
  • A
  • Chapter 6: Optimizing Queries for Good Performance
  • Learning what the optimizer does
  • Optimizations by example
  • Evaluating join options
  • Nested loops
  • Hash joins
  • Merge joins
  • Applying transformations
  • Inlining the view
  • Flattening subselects
  • Applying equality constraints
  • Exhaustive searching
  • Trying it all out
  • Making the process fail
  • Constant folding
  • Understanding function inlining
  • Join pruning
  • Speedup set operations
  • Understanding execution plans
  • Approaching plans systematically
  • Making EXPLAIN more verbose
  • Spotting problems
  • Spotting changes in runtime
  • Inspecting estimates
  • Inspecting buffer usage
  • Fixing high buffer usage
  • 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 partitions
  • Applying table constraints
  • Modifying inherited structures
  • Moving tables in and out of partitioned structures
  • Cleaning up data
  • Understanding PostgreSQL 11.0 partitioning
  • 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 fundamentals - stored procedures versus functions
  • The anatomy of a function
  • Introducing dollar quoting
  • Making use of anonymous code blocks
  • Using functions and transactions
  • Understanding various stored procedure languages
  • Introducing PL/pgSQL
  • Handling quoting
  • Managing scopes
  • Understanding advanced error handling
  • Making use of GET DIAGNOSTICS
  • Using cursors to fetch data in chunks
  • Utilizing composite types
  • Writing triggers in PL/pgSQL
  • Writing stored procedures in PL/pgSQL
  • Introducing PL/Perl
  • Using PL/Perl for datatype abstraction
  • Deciding between PL/Perl and PL/PerlU
  • Making use of the SPI interface
  • Using SPI for set returning functions
  • Escaping in PL/Perl and support functions
  • Sharing data across function calls
  • Writing triggers in Perl
  • Introducing PL/Python
  • Writing simple PL/Python code
  • Using the SPI interface
  • Handling errors
  • Improving functions
  • Reducing the number of function calls
  • Using cached plans
  • Assigning costs to functions
  • Using functions for various purposes
  • Summary
  • Q&amp
  • A
  • Chapter 8: Managing PostgreSQL Security
  • Managing network security
  • Understanding bind addresses and connections
  • Inspecting connections and performance
  • Living in a world without TCP
  • Managing pg_hba.conf
  • Handling SSL
  • Handling instance-level security
  • Creating and modifying users
  • Defining database-level security
  • Adjusting schema-level permissions
  • Working with tables
  • Handling column-level security
  • Configuring default privileges
  • Digging into RLS
  • Inspecting permissions
  • Reassigning objects and dropping users
  • Summary
  • Q&amp
  • A.
  • Chapter 9: Handling Backup and Recovery
  • Performing simple dumps
  • Running pg_dump
  • Passing passwords and connection information
  • Using environment variables
  • Making use of .pgpass
  • Using service files
  • Extracting subsets of data
  • Handling various formats
  • Replaying backups
  • Handling global data
  • Summary
  • Q&amp
  • A
  • 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
  • Configuring the pg_hba.conf file
  • Creating base backups
  • Reducing the bandwidth of a backup
  • Mapping tablespaces
  • Using different formats
  • Testing transaction log archiving
  • Replaying the transaction log
  • Finding the right timestamp
  • Cleaning up the transaction log archive
  • Setting up asynchronous replication
  • Performing a basic setup
  • Improving security
  • 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
  • Use cases of logical slots
  • Making use of CREATE PUBLICATION and CREATE SUBSCRIPTION
  • Summary
  • Q&amp
  • A
  • 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.

Ejemplares similares