Mastering PostgreSQL 12

Cover
Title Page
Copyright and Credits
About Packt
Contributors
Table of Contents
Preface
Section 1: Basic Overview
Chapter 1: PostgreSQL 12 Overview
	What's new in PostgreSQL 12?
		Digging into SQL and developer-related topics
			Improving psql and database documentation
			Displaying output as CSV
			Rebuilding indexes concurrently
			Storing the result of a computation
			Improving ENUM handling
			Making use of JSONPATH
		Understanding backup and recovery related features
		Making use of performance improvements
			Optimizing common table expressions and planner support functions
			Speeding up partitions
			Creating special indexes more efficiently
		Understanding new storage-related features
	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
			Considering alternative solutions
	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
			Digging into transaction wraparound-related issues
			A word on VACUUM FULL
		Watching VACUUM at work
		Limiting transactions by making use of snapshot too old
		Making use of more VACUUM features
	Summary
	Questions
Section 2: Advanced Concepts
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 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
			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
	Questions
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
			Understanding the subtle difference between ROWS and RANGE
			Removing duplicates using EXCLUDE TIES and EXCLUDE GROUP
		Abstracting window clauses
		Using 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 and CREATE INDEX 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
	Questions
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
				Step 1: Inlining the view
				Step 2: 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 12.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 and the string format
			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 data type 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
	Questions
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
	Questions
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
	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
		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
	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
			Handling mistakes and typos
	Other useful extensions
	Summary
Chapter 12: Troubleshooting PostgreSQL
	Approaching an unknown database
	Inspecting pg_stat_activity
		Querying pg_stat_activity
			Treating Hibernate statements
			Figuring out where queries come from
	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
			Supporting lateral joins
		Using grouping sets
			Supporting grouping sets
		Using the WITH clause – common table expressions
			Supporting the WITH clause
		Using the WITH RECURSIVE clause
			Supporting the WITH RECURSIVE clause
		Using the FILTER clause
			Supporting the FILTER clause
		Using windowing functions
			Supporting windowing and analytics
		Using ordered sets – the WITHIN GROUP clause
			Supporting the WITHIN GROUP clause
		Using the TABLESAMPLE clause
			Supporting the TABLESAMPLE clause
		Using limit/offset
			Supporting the FETCH FIRST clause
		Using the OFFSET clause
			Supporting the OFFSET clause
		Using temporal tables
			Supporting 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
		Using Ora2pg to migrate from Oracle
		Common pitfalls
		Handling data in MySQL and MariaDB
			Changing column definitions
			Handling null values
			Expecting problems
		Migrating data and schema
			Using pg_chameleon
			Using FDWs
	Summary
Assessment
Other Books You May Enjoy
Index