Learn PostgreSQL

Build and manage high-performance database solutions using PostgreSQL 12 and 13

Luca Ferrari, Enrico Pirozzi

• Publisher: Packt Publishing
• Year: 2020
• Print ISBN: 9781838985288
• eBook ISBN: 9781838986896
• Edition: 1
• Topic: Informatique
• Subtopic: Extraction de données

A comprehensive guide to building, managing, and securing scalable and reliable database and data warehousing applications using Postgres 12 and 13

Key Features

• Set up your database cluster and monitor, secure, and fine-tune it for optimal performance
• Learn the fundamentals of database management and implement client- and server-side programming using SQL and PL/pgSQL
• Explore useful tips to develop efficient PostgreSQL database solutions from scratch

Book Description

PostgreSQL is one of the fastest-growing open source object-relational database management systems (DBMS) in the world. As well as being easy to use, it's scalable and highly efficient. In this book, you'll explore PostgreSQL 12 and 13 and learn how to build database solutions using it. Complete with hands-on tutorials, this guide will teach you how to achieve the right database design required for a reliable environment.

You'll learn how to install and configure a PostgreSQL server and even manage users and connections. The book then progresses to key concepts of relational databases, before taking you through the Data Definition Language (DDL) and commonly used DDL commands. To build on your skills, you'll understand how to interact with the live cluster, create database objects, and use tools to connect to the live cluster. You'll then get to grips with creating tables, building indexes, and designing your database schema. Later, you'll explore the Data Manipulation Language (DML) and server-side programming capabilities of PostgreSQL using PL/pgSQL, before learning how to monitor, test, and troubleshoot your database application to ensure high-performance and reliability.

By the end of this book, you'll be well-versed with the Postgres database and be able to set up your own PostgreSQL instance and use it to build robust solutions.

What you will learn

• Understand how users and connections are managed by running a PostgreSQL instance
• Interact with transaction boundaries using server-side programming
• Identify bottlenecks to maintain your database efficiently
• Create and manage extensions to add new functionalities to your cluster
• Choose the best index type for each situation
• Use online tools to set up a memory configuration that will suit most databases
• Explore how Postgres can be used in multi-instance environments to provide high-availability, redundancy, and scalability

Who this book is for

This Postgres book is for anyone interested in learning about the PostgreSQL database from scratch. Anyone looking to build robust data warehousing applications and scale the database for high-availability and performance using the latest features of PostgreSQL will also find this book useful. Although prior knowledge of PostgreSQL is not required, familiarity with databases is expected.

Section 1: Getting Started

In this section, you will learn what PostgreSQL is, what is new in version 12 (and version 13), and how to install and run this great open source database.

This section contains the following chapters:

• Chapter 1, Introduction to PostgreSQL
• Chapter 2, Getting to Know Your Cluster
• Chapter 3, Managing Users and Connections

Introduction to PostgreSQL

PostgreSQL is a well-known open-source relational database, and its motto states what the project intends to be: the most advanced open-source database in the world.

The main qualities that attract masses of new users every year and keep current users enthusiastic about their projects are its rock-solid stability, scalability, and safeness, as well as the features that an enterprise-level database management system provides.

But PostgreSQL is not just a database; it has grown to be a whole ecosystem of extensions, tools, and languages tied together by communities spread around the world.

PostgreSQL is an open-source project and is fully developed in the open-source world. That means that there is no single entity in charge of the project and the result is that PostgreSQL is not a commercial product. In other words, PostgreSQL belongs to everyone, and anyone can contribute to it. Thanks to a very permissive BSD-style license, PostgreSQL can be used in any project or scenario, either open or closed source.

Of course, contributing to a project of that size requires experience in software development, database concepts, and, of course, a positive attitude to open source and collaborative efforts. But it does also mean that PostgreSQL will continue to live pretty much forever without the risk of a single company going out of business and sinking with the database.

PostgreSQL 12 is the latest release of this great database, and at the time of writing, efforts for PostgreSQL 13 have already begun. This book will focus on PostgreSQL, starting from the basics and moving toward the most exciting and complex tasks (such as replicating your datasets to prevent disasters). Of course, given that PostgreSQL is a project of this size with so many features, a single book cannot cover it all in detail, so our aim is to introduce the whole set of qualities PostgreSQL provides to you, giving practical use cases and examples, as well as external resources to help you learn more about particular aspects.

This book covers PostgreSQL 12 and 13, but the concepts explained in this book can apply also to later versions (as well as to previous ones when the same features are present). At the time of writing, PostgreSQL 12 is the stable release, while PostgreSQL 13 is in its second beta public release and is expected, therefore, to be stable enough for testing its features.

This chapter will introduce you to this great open source database starting from the project history and goals, which is very important to help you decide whether you want to use PostgreSQL in the first place. You will learn basic PostgreSQL terminology, which is very important to help you search the documentation and understand the main error messages, in case you need to. Finally, you will see how to install PostgreSQL in different ways so that you will get a basic knowledge of how to install it on different platforms and contexts.

The following topics are covered in this chapter:

• PostgreSQL at a glance
• Exploring PostgreSQL terminology
• Installing PostgreSQL 12 or higher

Technical requirements

You can find the code for this chapter at the following GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL.

PostgreSQL at a glance

As a relational database, PostgreSQL provides a lot of features, and it is quite difficult to "scare" a PostgreSQL instance. In fact, a single instance can contain more than 4 billion individual databases, each with unlimited total size and capacity for more than 1 billion tables, each containing 32 TB of data. Moreover, if there's any concern that those upper limits won't suffice, please consider that a single table can have 1,600 columns, each 1 GB in size, with an unlimited number of multi-column (up to 32 columns) indexes. In short, PostgreSQL can store much more data than you can possibly think of!

Therefore, there is no amount of data that PostgreSQL cannot handle, but of course, in order to perform well with certain big databases, you need to understand PostgreSQL and its features.

PostgreSQL is fully ACID-compliant and has a very strong foundation in data integrity and concurrency. It ships with a procedural language, named PL/pgSQL, which can be used to write reusable pieces of code, such as functions and routines, and it supports before and after triggers, views, materialized views, and partitioned tables.

ACID is an acronym of the properties used to indicate that the database engine provides atomicity, consistency, isolation, and durability. Atomicity means that a complex database operation is processed as a single instruction even when it is made up of different operations. Consistency means that the data within the database is always kept consistent and that is it is not corrupted due to partially performed operations. Isolation allows the database to handle concurrency in the "right way"—that is, without having corrupted data from interleaved changes. Lastly, durability means that the database engine is supposed to protect the data it contains, even in the case of software and hardware failures, as much as it can.

PostgreSQL can be extended with other embedded languages, such as Perl, Python, Java, and even Bash! And if you think the database does not provide you with enough features, you can plug in extensions to obtain different behaviors and enhancements—for instance, geospatial references (GIS), scheduled jobs, esoteric data types, and utilities in general.

PostgreSQL runs on pretty much every operating system out there, including Linux, Unix, Mac OS X, and Microsoft Windows, and can even run on commodity hardware such as Raspberry Pi boards. There are also several cloud computing providers that list PostgreSQL in their software catalog.

Thanks to its extensive tuning mechanism, it can be adapted very well to the hosting platform. The community is responsible for keeping the database and documentation at a very high-quality level, and also the mailing lists and IRC channels are very responsive and a valuable source for solutions and ideas.

In the experience of the authors, there has never been a case where PostgreSQL has not been able to adapt to an application scenario.

The PostgreSQL project has a very rich and extensive set of a mailing lists that range from general topics to very specific details. It is a good habit to search for problems and solutions on the mailing list archives; see the web page at https://www.postgresql.org/list/ to get a better idea.

A brief history of PostgreSQL

PostgreSQL takes its name from its ancestor: Ingres. Ingres was a relational database developed by professor Michael Stonebraker. In 1986, Professor Stonebraker started a post-Ingres project to develop new cool features in the database landscape and named this project POSTGRES (POST-Ingres). The project aimed to develop an object-relational database, where "object" means the user would have the capability to extend the database with their own objects, such as data types, functions, and so on.

In 1994, POSTGRES was released with version 4.2 and an MIT license, which opened up collaboration from other developers around the world. At that time, POSTGRES was using an internal query language named QUEL.

Two Berkeley students, Andrew Yu and Jolly Chen, replaced the QUEL query language with the hot and cool SQL language, and the feature was so innovative that the project changed its name to Postgre95 to emphasize the difference compared to other preceding versions.

Eventually, in 1996, the project gained a public server to host the code, and five developers, including Marc G. Fournier, Tom Lane, and Bruce Momjan, started the development of the new branded project named PostgreSQL. Since then, the project has been kept in good shape and up to date.

This also means that PostgreSQL has been developed for over 30 years, again emphasizing the solidity and openness of the project itself. If you are curious, it is also possible to dig into the source code down to the initial commit in the open source world:

$ git log `git rev-list --max-parents=0 HEAD`
commit d31084e9d1118b25fd16580d9d8c2924b5740dff
 Author: Marc G. Fournier <[email protected]>
 Date: Tue Jul 9 06:22:35 1996 +0000
 
 Postgres95 1.01 Distribution - Virgin Sources

What's new in PostgreSQL 12?

PostgreSQL 12 was released on October 3, 2019. It includes a rich set of new features wit...

Table of contents

• Title Page
• Copyright and Credits
• Dedication
• About Packt
• Contributors
• Preface
• Section 1: Getting Started
• Introduction to PostgreSQL
• Getting to Know Your Cluster
• Managing Users and Connections
• Section 2: Interacting with the Database
• Basic Statements
• Advanced Statements
• Window Functions
• Server-Side Programming
• Triggers and Rules
• Partitioning
• Section 3: Administering the Cluster
• Users, Roles, and Database Security
• Transactions, MVCC, WALs, and Checkpoints
• Extending the Database - the Extension Ecosystem
• Indexes and Performance Optimization
• Logging and Auditing
• Backup and Restore
• Configuration and Monitoring
• Section 4: Replication
• Physical Replication
• Logical Replication
• Section 5: The PostegreSQL Ecosystem
• Useful Tools and Extensions
• Toward PostgreSQL 13
• Other Books You May Enjoy