Book Overview & Buying
Table Of Contents

Mastering PostgreSQL 11 - Second Edition

By : Hans-Jürgen Schönig

4.1 (7)

Buy this Book

Mastering PostgreSQL 11

4.1 (7)

By: Hans-Jürgen Schönig

Buy this Book

Overview of this book

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.

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Free Chapter

PostgreSQL Overview

What's new in PostgreSQL 11.0?

Summary

Q&A

Understanding Transactions and Locking

Working with PostgreSQL transactions

Understanding basic locking

Making use of FOR SHARE and FOR UPDATE

Understanding transaction isolation levels

Observing deadlocks and similar issues

Utilizing advisory locks

Optimizing storage and managing cleanup

Summary

Q&A

Making Use of Indexes

Understanding simple queries and the cost model

Improving speed using clustered tables

Understanding additional btree features

Introducing operator classes

Understanding PostgreSQL index types

Achieving better answers with fuzzy searching

Understanding full-text search

Summary

Q&A

Handling Advanced SQL

Introducing grouping sets

Making use of ordered sets

Understanding hypothetical aggregates

Utilizing windowing functions and analytics

Writing your own aggregates

Summary

Log Files and System Statistics

Gathering runtime statistics

Creating log files

Summary

Q&A

Optimizing Queries for Good Performance

Learning what the optimizer does

Understanding execution plans

Understanding and fixing joins

Enabling and disabling optimizer settings

Partitioning data

Adjusting parameters for good query performance

Making use of parallel queries

Introducing JIT compilation

Summary

Writing Stored Procedures

Understanding stored procedure languages

Understanding various stored procedure languages

Improving functions

Using functions for various purposes

Summary

Q&A

Managing PostgreSQL Security

Managing network security

Digging into RLS

Inspecting permissions

Reassigning objects and dropping users

Summary

Q&A

Handling Backup and Recovery

Performing simple dumps

Handling various formats

Replaying backups

Handling global data

Summary

Q&A

Making Sense of Backups and Replication

Understanding the transaction log

Transaction log archiving and recovery

Setting up asynchronous replication

Upgrading to synchronous replication

Making use of replication slots

Making use of CREATE PUBLICATION and CREATE SUBSCRIPTION

Summary

Q&A

Deciding on Useful Extensions

Understanding how extensions work

Making use of contrib modules

Other useful extensions

Summary

Troubleshooting PostgreSQL

Approaching an unknown database

Inspecting pg_stat_activity

Checking for slow queries

Inspecting the log

Checking for missing indexes

Checking for memory and I/O

Understanding noteworthy error scenarios

Summary

Q&A

Migrating to PostgreSQL

Migrating SQL statements to PostgreSQL

Moving from Oracle to PostgreSQL

Moving from MySQL or MariaDB to PostgreSQL

Summary

Other Books You May Enjoy

Leave a review - let other readers know what you think

Optimizing Queries for Good Performance

In Chapter 5, Log Files and System Statistics, you learned how to read system statistics and how to make use of what PostgreSQL provides. Armed with this knowledge, this chapter is all about good query performance. You will learn more about the following topics:

Optimizer internals
Execution plans
Partitioning data
Enabling and disabling optimizer settings
Parameters for good query performance
Parallel queries
JIT compilation

By the end of this chapter, we will be able to write better and faster queries. If the queries still aren't very good, we should be able to understand why this is the case. We will also be able to use the new techniques we have learned about to partition data.

Tech Concepts

Programming languages

Tech Tools