Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Mastering PostgreSQL 11 - Second Edition

By : Hans-Jürgen Schönig

4.1 (7)

Mastering PostgreSQL 11

4.1 (7)

By: Hans-Jürgen Schönig

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

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

PostgreSQL Overview

What's new in PostgreSQL 11.0?

Summary

Q&A

Understanding Transactions and Locking

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

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

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

Log Files and System Statistics

Gathering runtime statistics

Creating log files

Summary

Q&A

Optimizing Queries for Good Performance

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

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 PostgreSQL Security

Managing network security

Digging into RLS

Inspecting permissions

Reassigning objects and dropping users

Summary

Q&A

Handling Backup and Recovery

Handling Backup and Recovery

Performing simple dumps

Handling various formats

Replaying backups

Handling global data

Summary

Q&A

Making Sense of Backups and Replication

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

Deciding on Useful Extensions

Understanding how extensions work

Making use of contrib modules

Other useful extensions

Summary

Troubleshooting PostgreSQL

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 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

Other Books You May Enjoy

Leave a review - let other readers know what you think

Other Books You May Enjoy

If you enjoyed this book, you may be interested in these other books by Packt:

PostgreSQL 10 Administration Cookbook
Simon Riggs, Gianni Ciolli

ISBN: 978-1-78847-492-4

Get to grips with the newly released PostgreSQL 10 features to improve database performance and reliability
Manage open source PostgreSQL versions 10 on various platforms
Explore best practices for planning and designing live databases
Select and implement robust backup and recovery techniques in PostgreSQL 10
Explore concise and clear guidance on replication and high availability

PostgreSQL 10 High Performance
Ibrar Ahmed, Gregory Smith, Enrico Pirozzi

ISBN: 978-1-78847-448-1

Learn best practices for scaling PostgreSQL 10 installations
Discover the best hardware for developing high-performance PostgreSQL applications
Benchmark your whole system – from hardware to application...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Mastering PostgreSQL 11

Search

Your notes and bookmarks