Learning PostgreSQL 11

A beginner's guide to building high-performance PostgreSQL database solutions, 3rd Edition

Salahaldin Juba, Andrey Volkov

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

Leverage the power of PostgreSQL 11 to build powerful database and data warehousing applications

Key Features

• Monitor, secure, and fine-tune your PostgreSQL 11 database
• Learn client-side and server-side programming using SQL and PL/pgSQL
• Discover tips on implementing efficient database solutions

Book Description

PostgreSQL is one of the most popular open source database management systems in the world, and it supports advanced features included in SQL standards. This book will familiarize you with the latest features in PostgreSQL 11, and get you up and running with building efficient PostgreSQL database solutions from scratch.

Learning PostgreSQL, 11 begins by covering the concepts of relational databases and their core principles. You'll explore the Data Definition Language (DDL) and commonly used DDL commands supported by ANSI SQL. You'll also learn how to create tables, define integrity constraints, build indexes, and set up views and other schema objects. As you advance, you'll come to understand Data Manipulation Language (DML) and server-side programming capabilities using PL/pgSQL, giving you a robust background to develop, tune, test, and troubleshoot your database application. The book will guide you in exploring NoSQL capabilities and connecting to your database to manipulate data objects. You'll get to grips with using data warehousing in analytical solutions and reports, and scaling the database for high availability and performance.

By the end of this book, you'll have gained a thorough understanding of PostgreSQL 11 and developed the necessary skills to build efficient database solutions.

What you will learn

• Understand the basics of relational databases, relational algebra, and data modeling
• Install a PostgreSQL server, create a database, and implement your data model
• Create tables and views, define indexes and stored procedures, and implement triggers
• Make use of advanced data types such as Arrays, hstore, and JSONB
• Connect your Python applications to PostgreSQL and work with data efficiently
• Identify bottlenecks to enhance reliability and performance of database applications

Who this book is for

This book is for you if you're interested in learning about PostgreSQL from scratch. Those looking to build solid database or data warehousing applications or wanting to get up to speed with the latest features of PostgreSQL 11 will also find this book useful. No prior knowledge of database programming or administration is required to get started.

PostgreSQL Advanced Building Blocks

This chapter will introduce the remainder of the PostgreSQL building blocks, including views, indexes, functions, triggers, and rules. In addition to that, the web car portal schema will be revised. Several Data Definition Language (DDL) commands, such as CREATE and ALTER, will also be introduced. Since the lexical structure and several Data Manipulation Language (DML) commands haven't been introduced yet, we will try to use simple DML commands.

In this chapter, the following topics will be covered:

• Views: Views are an important part of database modeling because they act as an interface or as an abstraction layer. The Views section will cover view synopsis and usages, and an updatable view example will be demonstrated.
• Indexes: Indexes are the secret sauce for ensuring consistency and performance. Index types will be discussed.
• Functions: Functions can be used to perform very complex logic in the database. Also, they can be used to return scalar values or datasets. Functions will be discussed briefly here, since functions are discussed in detail in Chapter 07, Server-Side Programming with PL/pgSQL.
• User-defined data types: One big advantage of PostgreSQL is being able to define and use new, different data types; this section will show several use cases, wherein user-defined data types will be used to solve some issues.
• Triggers and rule systems: Triggers and rule systems allow developers to handle events triggered by INSERT, UPDATE, DELETE, and so on. The trigger system is used to model complex business requirements that are difficult to achieve using plain SQL.

Views

A view can be considered a named query or a wrapper around a SELECT statement. Views are the essential building blocks of relational databases from a UML modeling perspective; a view can be thought of as a method for a UML class. Views share several advantages with functions; the following benefits are shared between views and stored procedures. Views can be used for the following purposes:

• Simplifying complex queries and increasing code modularity
• Tuning performance by caching the view results for later use
• Decreasing the amount of SQL code
• Bridging the gap between relational databases and object-oriented languages, especially updatable views
• Implementing authorization at the row level, by leaving out rows that do not meet a certain predicate
• Implementing interfaces and the abstraction layer between high-level languages and relational databases
• Implementing last-minute changes

A view should meet the current business needs, instead of potential future business needs. It should be designed to provide certain functionality or service. Note that the more attributes there are in a view, the more effort will be required to refactor the view. In addition to that, when a view aggregates data from many tables and is used as an interface, there might be a degradation in performance, due to many factors (for example, bad execution plans due to outdated statistics for some tables, execution plan time generation, and so on).

When implementing complex business logic in a database using views and stored procedures, database refactoring, especially for base tables, might turn out to be very expensive. To solve this issue, consider migrating the business logic to the application business tier.

Some frameworks, such as object-relational mappers, might have specific needs, such as a unique key. This limits the usage of views in these frameworks; however, we can mitigate these issues by faking the primary keys, via window functions such as row_number.

In PostgreSQL, a view is internally modeled as a table with an _RETURN rule. So, in theory, we can create a table and convert it into a view.

However, this is not a recommended practice. The VIEW dependency tree is well maintained; this means that we cannot drop a view or amend its structure if another view depends on it, as follows:

postgres=# CREATE VIEW test AS SELECT 1 as v;
CREATE VIEW
postgres=# CREATE VIEW test2 AS SELECT v FROM test;
CREATE VIEW
postgres=# CREATE OR REPLACE VIEW test AS SELECT 1 as val;
ERROR: cannot change name of view column "v" to "val"

View synopsis

In the following view synopsis, the CREATE VIEW statement is used to create a view; if the REPLACE keyword is used, the view will be replaced (if it already exists). View attribute names can be given explicitly or they can be inherited from the SELECT statement:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
 [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
 AS query
 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

The synopses of materialized views differ from view synopsis. Please refer to the Materialized views section later in this chapter for the materialized view synopsis.

The following example shows how to create a view that only lists the user information, without the password. This might be useful for implementing data authorization to restrict applications from accessing the password. Note that the view column names are inherited from the SELECT list, as shown by the \d metacommand:

car_portal=> CREATE VIEW car_portal_app.account_information AS SELECT account_id, first_name, last_name, email FROM car_portal_app.account;
CREATE VIEW
car_portal=> \d car_portal_app.account_information
 View "car_portal_app.account_information"
 Column | Type | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 account_id | integer | | | 
 first_name | text | | | 
 last_name | text | | | 
 email | text | | |

The view column names can be assigned explicitly, as shown in the following example. This might be useful when we need to change the view column names:

CREATE OR REPLACE VIEW car_portal_app.account_information (account_id,first_name,last_name,email) AS SELECT account_id, first_name, last_name, email FROM car_portal_app.account;

When replacing the view definition using the REPLACE keyword, the column list should be identical before and after the replacement, including the column t...