Book Overview & Buying
Table Of Contents
Feedback & Rating

Mastering PostgreSQL 9.6

By : Hans-Jürgen Schönig

2.7 (3)

Buy this Book

Mastering PostgreSQL 9.6

2.7 (3)

By: Hans-Jürgen Schönig

Buy this Book

Overview of this book

PostgreSQL is an open source database used for handling large datasets (Big Data) and as a JSON document database. It also has applications in the software and web domains. This book will enable you to build better PostgreSQL applications and administer databases more efficiently. We begin by explaining the advanced database design concepts in PostgreSQL 9.6, along with indexing and query optimization. You will also see how to work with event triggers and perform concurrent transactions and table partitioning, along with exploring SQL and server tuning. We will walk you through implementing advanced administrative tasks such as server maintenance and monitoring, replication, recovery and high availability, and much more. You will understand the common and not-so-common troubleshooting problems and how you can overcome them. By the end of this book, you will have an expert-level command of the advanced database functionalities and will be able to implement advanced administrative tasks with PostgreSQL.

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

PostgreSQL Overview

What is new in PostgreSQL 9.6?

Summary

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

Making Use of Indexes

Understanding simple queries and the cost model

Improving speed using clustered tables

Understanding additional B-tree features

Introducing operator classes

Understanding PostgreSQL index types

Achieving better answers with fuzzy searching

Understanding full-text search - FTS

Summary

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

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

Summary

Writing Stored Procedures

Understanding stored procedure languages

Understanding various stored procedure languages

Improving stored procedure performance

Using stored procedures

Summary

Managing PostgreSQL Security

Managing network security

Digging into row-level security - RLS

Inspecting permissions

Reassigning objects and dropping users

Summary

Handling Backup and Recovery

Performing simple dumps

Replaying backups

Handling global data

Summary

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

Summary

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

Migrating to PostgreSQL

Migrating SQL statements to PostgreSQL

Moving from Oracle to PostgreSQL

Moving from MySQL or MariaDB to PostgreSQL

Summary

Customer Reviews

2.7 (3)

5 star

33.3%

4 star

3 star

2 star

33.3%

1 star

33.3%

Optimizing Queries for Good Performance

In previous chapters, you have 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

At the end of the chapter, I hope that you will be able to write better and faster queries. And if your queries still happen to be bad, you should be able to understand why this is the case.