Mastering PostgreSQL 11 : expert techniques to build scalable, reliable, and fault-tolerant database applications / Hans-Jurgen Schonig.

Format:

Book

Author/Creator:

Schonig, Hans-Jurgen, author.

Language:

English

Subjects (All):

PostgreSQL.

Database management.

Physical Description:

1 online resource (450 pages)

Edition:

Second edition.

Other Title:

Mastering Postgre Structure Query Language eleven

Place of Publication:

Birmingham : Packt, 2018.

System Details:

text file

Summary:

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 functionalities of your PostgreSQL instance to suit your organization's needs with minimal effort Book Description This second edition of Mastering PostgreSQL 11 helps you build dynamic database solutions for enterprise applications using the latest release of PostgreSQL, which enables database analysts to design both the physical and technical aspects of the system architecture with ease. This book begins with an introduction to the newly released features in PostgreSQL 11 to help you build efficient and fault-tolerant PostgreSQL applications. You'll examine all of the advanced aspects of PostgreSQL in detail, including logical replication, database clusters, performance tuning, monitoring, and user management. You will also work with the PostgreSQL optimizer, configuring PostgreSQL for high speed, and see how to move from Oracle to PostgreSQL. As you progress through the chapters, you will cover transactions, locking, indexes, and optimizing queries to improve performance. Additionally, you'll learn to manage network security and explore backups and replications, while understanding the useful extensions of PostgreSQL so that you can optimize the speed and performance of large databases. By the end of this book, you will be able to use your database to its utmost capacity by implementing advanced administrative tasks with ease. What you will learn Get to grips with advanced PostgreSQL 11 features and SQL functions Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries Work with stored procedures and manage backup and recovery Master replication and failover techniques Troubleshoot your PostgreSQL instance for solutions to common and not-so-common problems Perform database migration from MySQL and Oracle to PostgreSQL with ease Who this book is for This book is for data and database professionals wanting to implement advanced functionalities and master complex administrative tasks with PostgreSQL 11. Prior experience of database administration with PostgreSQL database will aid in understanding the concepts covered in this book.

Contents:

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

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

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

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

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

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

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

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

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

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

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.

Notes:

Includes index.

Description based on online resource; title from PDF title page (ebrary, viewed February 20, 2019).

OCLC:

1086122778