Learning PostgreSQL 11电子书

Title Page

Copyright and Credits

Learning PostgreSQL 11 Third Edition

About Packt

Why subscribe?

Packt.com

Contributors

About the authors

About the reviewers

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Get in touch

Reviews

Relational Databases

Database management systems

A brief history

Database categories

NoSQL databases

The CAP theorem

NoSQL motivation

Key-value databases

Columnar databases

Document databases

Graph databases

Relational and object-relational databases

ACID properties

The SQL language

Relational model concepts

Relation

Tuple

The null value

Attribute

Constraint

Domain integrity constraint

Entity integrity constraint

Referential integrity constraints

Semantic constraints

Relational algebra

The SELECT and PROJECT operations

The RENAME operation

The set theory operations

The Cartesian product operation

Data modeling

Data model perspectives

The entity-relation model

Sample application

Entities, attributes, and keys

Mapping ER to relations

UML class diagrams

Summary

Questions

PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL history

The advantages of PostgreSQL

Business advantages of PostgreSQL

PostgreSQL user advantages

PostgreSQL applications

Success stories

Forks

PostgreSQL architecture

The PostgreSQL community

PostgreSQL capabilities

Replication

Security

Extensions

NoSQL capabilities

Foreign data wrappers

Performance

Installing PostgreSQL

Installing PostgreSQL on Linux via source

Installing PostgreSQL using Advanced Package Tool

Client installation

Server installation

Basic server configuration

Installing PostgreSQL on Windows

PostgreSQL clients

The psql client

psql advanced settings

PostgreSQL utility tools

Summary

Questions

PostgreSQL Basic Building Blocks

Database coding

Database naming conventions

PostgreSQL identifiers

Documentation

Version control systems

Database-migration tools

PostgreSQL object hierarchy

Template databases

User databases

Roles

Tablespaces

Template procedural languages

Settings

PostgreSQL high-level object interaction

PostgreSQL database components

Schemas

Schema usages

Tables

PostgreSQL native data types

Numeric types

Serial types and identity columns

Character types

Date and time types

The car web portal database

Summary

Questions

PostgreSQL Advanced Building Blocks

Views

View synopsis

View categories

Materialized views

Updatable views

Indexes

Index synopses

Index selectivity

Index types

Index categories

Best practices for indexes

Functions

The PostgreSQL native programming language

Creating a function in the C language

Function usage

Function dependencies

PostgreSQL function categories

PostgreSQL anonymous functions

User-defined data types

Trigger and rule systems

Rule system

Trigger system

Triggers with arguments

Triggers and updatable views

Summary

Questions

SQL Language

SQL fundamentals

SQL lexical structure

Querying data with SELECT statements

Structure of a SELECT query

SELECT-list

SQL expressions

DISTINCT

The FROM clause

Selecting from multiple tables

Self-joins

The WHERE clause

Comparison operators

Pattern matching

Row and array comparison constructs

Grouping and aggregation

The GROUP BY clause

The HAVING clause

Ordering and limiting results

Subqueries

Set operations – UNION, EXCEPT, and INTERSECT

Dealing with NULLS

Changing the data in a database

The INSERT statement

The UPDATE statement

UPDATE using sub-select

UPDATE using additional tables

The DELETE statement

The TRUNCATE statement

Summary

Questions

Advanced Query Writing

Common table expressions

Reusing SQL code with CTE

Recursive and hierarchical queries

Changing data in multiple tables at a time

Window functions

Window definitions

The WINDOW clause

Using window functions

Window functions with grouping and aggregation

Advanced SQL techniques

Selecting the first records

Selecting a data sample

Set-returning functions

Lateral subqueries

Advanced grouping

Advanced aggregation

Summary

Questions

Server-Side Programming with PL/pgSQL

The SQL language and PL/pgSQL – a comparison

Functions and procedures – a comparison

PostgreSQL function parameters

Function authorization related parameters

Function planner related parameters

Function configuration related parameters

The PostgreSQL PL/pgSQL control statements

Declaration statements

Assignment statements

Conditional statements

Iterations

Loop statements

The WHILE loop statement

The FOR loop statement

Returning from the function

Returning voids

Returning a single row

Returning multiple rows

Function predefined variables

Exception handling

Dynamic SQL

Executing DDL statements in dynamic SQL

Executing DML statements in dynamic SQL

Dynamic SQL and the caching effect

Recommended practices for dynamic SQL usage

Summary

Questions

OLAP and Data Warehousing

OLTP and OLAP

Online analytical processing

Loading data into a data warehouse

Data modeling for OLAP

Aggregation

Partitioning

Declarative partitioning

Sub-partitioning

Performance improvement

Parallel query

Index-only scans

Summary

Questions

Beyond Conventional Data Types

Arrays

Common functions of arrays and their operators

Modifying and accessing arrays

Indexing arrays

The hash store data structure

Indexing an hstore

The JSON data structure

JSON and XML

JSON data types for PostgreSQL

The benefits of supporting the JSON data type

Modifying and accessing JSON types

Indexing a JSON data type

RESTful APIs using PostgreSQL

A PostgreSQL full-text search

The tsquery and tsvector data types

The tsvector data type

The tsquery data type

Pattern matching

Full-text search indexing

Summary

Questions

Transactions and Concurrency Control

Transactions

Transaction and ACID properties

Transaction and concurrency

MVCC in PostgreSQL

Transaction isolation levels

Explicit locking

Table-level locks

Table locking modes

Row-level locks

Row-level lock modes

Deadlocks

Advisory locks

Transaction in functions and procedures

Summary

Questions

PostgreSQL Security

Authentication in PostgreSQL

PostgreSQL pg_hba.conf

Listen addresses

Authentication best practices

PostgreSQL default access privileges

Role system and proxy authentication

PostgreSQL security levels

Database security level

Schema security level

Object-level security

Column-level security

Row-level security

Encrypting data

PostgreSQL role password encryption

pgcrypto

One-way encryption

Two-way encryption

Summary

Questions

The PostgreSQL Catalog

The system catalog

System catalog for administrators

Getting the database cluster and client tools version

Terminating and canceling user sessions

Defining and getting database cluster settings

Getting the database and database object size

Cleaning up the database

Data cleanup

Tuning for performance

Selective dump

Summary

Questions

Optimizing Database Performance

PostgreSQL configuration tuning

Maximum number of connections

Memory settings

Disk settings

Planner-related settings

Benchmarking is your friend

Tuning performance for write

Tuning performance for read

Explaining the command and execution plans

Detecting problems in query plans

Common mistakes in writing queries

Unnecessary operations

Misplaced or missing indexes

Using CTE when not mandatory

Using the PL/pgSQL procedural language consideration

Cross-column correlation

Table partitioning

Constraint exclusion limitations

Query rewriting

Summary

Questions

Testing

Technical requirements

Unit testing

Specificity of unit testing in databases

Unit test frameworks

Schema difference

Database-abstraction interfaces

Data differences

Performance testing

Summary

Questions

Using PostgreSQL in Python Applications

Technical requirements

Python DB-API 2.0

Low-level database access with Psycopg 2

Connecting to a database

Connection pooling

Executing SQL commands

Reading data from a database

The COPY command

Asynchronous access

Alternative drivers for PostgreSQL

pg8000

asyncpg

Object-relational mapping with SQLAlchemy

Main components of SQLAlchemy

Connecting to a database and retrieving data with SQL expression language

ORM

Summary

Questions

Scalability

Technical requirements

The problem of scalability and the CAP theorem

Data replication in PostgreSQL

Transaction log

Physical replication

Log shipping replication

Streaming replication

Synchronous replication

Failover procedures

Logical replication

Building a scalable PostgreSQL solution

Scaling for heavy querying

Data sharding

Scaling for many numbers of connections

Summary

Questions

Further reading

What's Next?

Fundamentals

Normalization

Denormalization

NoSQL databases

Working with PostgreSQL software

The PostgreSQL server

PostgreSQL client software

Working with data in PostgreSQL databases

PostgreSQL schema objects

Working with data

PostgreSQL as a component of a software solution

Summary

Other Books You May Enjoy

Leave a review - let other readers know what you think