Mastering PostgreSQL 10

Cover --
Title Page --
Copyright and Credits --
Packt Upsell --
Contributors --
Table of Contents --
Preface --
Chapter 1: PostgreSQL Overview --
What is new in PostgreSQL 10.0? --
Understanding new database administration functions --
Using additional information in pg_stat_activity --
Introducing SCRAM-SHA-256 --
Improving support for replication --
Understanding logical replication --
Introducing quorum COMMIT --
Partitioning data --
Making use of CREATE STATISTICS --
Improving parallelism --
Introducing ICU encodings --
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 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 --
Making use of snapshot too old --
Summary --
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. Hacking up 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 --
FTS --
Comparing strings --
Defining GIN indexes --
Debugging your search --
Gathering word statistics --
Taking advantage of exclusion operators --
Summary --
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 onboard 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 --
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 10.0 partitioning --
Adjusting parameters for good query performance --
Speeding up sorting --
Speeding up administrative tasks --
Summary --
Chapter 7: Writing Stored Procedures --
Understanding stored procedure languages --
The anatomy of a stored procedure --
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 --
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 stored procedure performance --
Reducing the number of function calls --
Using cached plans --
Assigning costs to functions --
Using stored procedures --
Summary --
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 row-level security --
RLS --
Inspecting permissions --
Reassigning objects and dropping users --
Summary --
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 --
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 --
Chapter 11: Deciding on Useful Extensions --
Understanding how extensions work --
Checking for available extensions --
Making use of contrib modules --
Using the adminpack --
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 searches 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.