Mastering PostgreSQL 12

Advanced techniques to build and administer scalable and reliable PostgreSQL database applications, 3rd Edition

Hans-Jürgen Schönig

• Publisher: Packt Publishing
• Year: 2019
• ISBN:
  9781838985271
• Edition: 3
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Master PostgreSQL 12 features such as advanced indexing, high availability, monitoring, and much more to efficiently manage and maintain your database

Key Features

• Grasp advanced PostgreSQL 12 concepts with real-world examples and sample datasets
• Explore query parallelism, data replication, database administration, and more
• Extend PostgreSQL functionalities to suit your organization's needs with minimal effort

Book Description

Thanks to its reliability, robustness, and high performance, PostgreSQL has become the most advanced open source database on the market. This third edition of Mastering PostgreSQL helps you build dynamic database solutions for enterprise applications using the latest release of PostgreSQL, which enables database analysts to design both physical and technical aspects of system architecture with ease.

Starting with an introduction to the newly released features in PostgreSQL 12, this book will help you build efficient and fault-tolerant PostgreSQL applications. You'll thoroughly examine the advanced features of PostgreSQL, including logical replication, database clusters, performance tuning, monitoring, and user management. You'll also work with the PostgreSQL optimizer, configure PostgreSQL for high speed, and understand how to move from Oracle to PostgreSQL. As you progress through the chapters, you'll cover transactions, locking, indexes, and how to optimize queries for improved performance. Additionally, you'll learn how to manage network security and explore backups and replications while understanding useful PostgreSQL extensions to help you in optimizing the performance of large databases.

By the end of this PostgreSQL book, you'll be able to get the most out of your database by implementing advanced administrative tasks effortlessly.

What you will learn

• Understand the advanced SQL functions in PostgreSQL 12
• Use indexing features in PostgreSQL to fine-tune the performance of queries
• Work with stored procedures and manage backup and recovery
• Master replication and failover techniques to reduce data loss
• Replicate PostgreSQL database systems to create backups and to scale your database
• Manage and improve the security of your server to protect your data
• Troubleshoot your PostgreSQL instance for solutions to common and not-so-common problems

Who this book is for

This book is for PostgreSQL developers and administrators and database professionals who want to implement advanced functionalities and master complex administrative tasks with PostgreSQL 12. Prior exposure to PostgreSQL as well as familiarity with the basics of database administration is expected.

Section 1: Basic Overview

This introductory section aims to provide an overview of the latest version of PostgreSQL and ensuring that you understand some of the basic concepts around it.

This section contains the following chapters:

• Chapter 1, PostgreSQL 12 Overview
• Chapter 2, Understanding Transactions and Locking

PostgreSQL 12 Overview

After the usual development cycle, PostgreSQL 12 was released to the public in autumn 2019. The new release offers a variety of new features and provides a rich set of functionality to users, which will make application development faster, improve performance, and generally make PostgreSQL even more usable than it previously was. Many of the new features open the door for further development in the future and will enable developers to implement cutting edge technologies in the decades to come. In this chapter, you will be introduced to those new features and will get an overview of what has been improved, added, and even changed.

The following topics will be covered:

• What's new in PostgreSQL 12?
• SQL and developer-related features
• Backup, recovery, and replication
• Performance-related topics
• Storage-related topics

All relevant features will be covered. Of course, there is always more, and thousands of tiny changes have made it into PostgreSQL 12. What you will see in this chapter are the highlights of the new release.

What's new in PostgreSQL 12?

PostgreSQL 12 is a major milestone and a lot of infrastructure has made it into the core this time. This is especially important in the long run. In this chapter, you will be introduced to the most important developments in the PostgreSQL world. Let's get started and see what the developers have come up with.

Digging into SQL and developer-related topics

PostgreSQL 12 provides some new features that are especially important to developers.

Improving psql and database documentation

In PostgreSQL, two major improvements have been made:

• Adding figures to the documentation
• Adding links to psql

The PostgreSQL documentation has traditionally been very good. However, in the past, the documentation did not contain any figures or graphical explanations; it was purely text, which made it hard for some people to quickly understand some aspects of the database. PostgreSQL has finally put an end to this and images have been introduced. This might sound like a minor thing, but it required some changes to the way documentation is handled in general. More figures will be added in the future.

The second change that is immediately visible to psql users is that \h does not point to the documentation directly. The following listing shows an example:

test=# \h SHOW
Command:  SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW name
SHOW ALL

URL: https://www.postgresql.org/docs/12/sql-show.html

The URL is really useful here. It avoids a lot of unnecessary searching and digging.

Displaying output as CSV

For those of you using psql to work with PostgreSQL, there is more—psql can't display data in CSV format, as shown in the next listing:

test=# \pset format csv
Output format is csv.
test=# SELECT id, id FROM generate_series(1, 4) AS id;
id,id
1,1
2,2
3,3
4,4

This is especially useful if you are using psql in a shell script and if you are piping data from one program to the next. If you always want to get data in CSV format, you can add the pset command to your .psqlrc file.

Rebuilding indexes concurrently

Once in a while, it can be necessary (in rare circumstances) to recreate an index. For quite some time, PostgreSQL has provided CREATE INDEX CONCURRENTLY, which allows end users to create an index while a table is under a heavy write load. A normal CREATE INDEX statement blocks the table while the index is created and, therefore, it is hardly possible to create large indexes in a 24 x 7 OLTP database.

However, in some cases, it might be necessary to recreate all indexes in a database or a specific schema. PostgreSQL 12 allows you to run REINDEX CONCURRENTLY on an entire database, schema, or table:

test=# \h REINDEX
Command:  REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name

URL: https://www.postgresql.org/docs/12/sql-reindex.html

REINDEX CONCURRENTLY will dramatically reduce the pain caused by REINDEX and achieve results with minimal locking.

Storing the result of a computation

PostgreSQL 12 has more features that can simplify development. In many cases, the content of a column has to be derived from some other column. Traditionally, this has been implemented using a trigger. However, writing a trigger needs some coding and manual work. PostgreSQL has a better solution to the problem.

Suppose we want to store data in kilometers and nautical miles. One nautical mile translates to 1.852 km.

For those of you who know nothing about nautical miles: A nautical mile is 1/60^th of a degree on the equator.

To make sure that nautical miles are always generated, the following syntax can be used:

test=# CREATE TABLE t_measurement (
 t timestamp, 
 km numeric, 
 nm numeric GENERATED ALWAYS AS (km * 1.852) STORED
);
CREATE TABLE

GENERATED ALWAYS AS is an elegant way to pre-calculate the content of a column. As you can see, the definition does exactly what we expect:

test=# INSERT INTO t_measurement (t, km) VALUES (now(), 100) RETURNING *;
 t | km |  nm  
----------------------------+-----+---------
 2019-09-30 15:02:31.004481 | 100 | 185.200
(1 row)

INSERT 0 1

However, there is more than instantly meets the eye—GENERATED ALWAYS AS also ensures that the content of the column cannot be changed to ensure that the value is always correct:

test=# INSERT INTO t_measurement (t, km, nm) VALUES (now(), 100, 1000) RETURNING *;
ERROR: cannot insert into column "nm"
DETAIL: Column "nm" is a generated column.

Performance-wise, the new feature is also faster than the traditional method of using a tr...