Book Image

Learning PostgreSQL 10 - Second Edition

Book Image

Learning PostgreSQL 10 - Second Edition

Overview of this book

PostgreSQL is one of the most popular open source databases in the world, supporting the most advanced features included in SQL standards. This book will familiarize you with the latest features released in PostgreSQL 10. We’ll start with a thorough introduction to PostgreSQL and the new features introduced in PostgreSQL 10. We’ll cover the Data Definition Language (DDL) with an emphasis on PostgreSQL, and the common DDL commands supported by ANSI SQL. You’ll learn to create tables, define integrity constraints, build indexes, and set up views and other schema objects. Moving on, we’ll cover the concepts of Data Manipulation Language (DML) and PostgreSQL server-side programming capabilities using PL/pgSQL. We’ll also explore the NoSQL capabilities of PostgreSQL and connect to your PostgreSQL database to manipulate data objects. By the end of this book, you’ll have a thorough understanding of the basics of PostgreSQL 10 and will have the necessary skills to build efficient database solutions.

Related Content you might be interested in

Current Title:

Small book image
Learning PostgreSQL 10 - Second Edition
Dec, 2017 | 488 pages
Small book image
Learn PostgreSQL
Luca Ferrari | Enrico Pirozzi
Oct, 2020 | 650 pages
Small book image
PostgreSQL 14 Administration Cookbook
Simon Riggs | Gianni Ciolli
Mar, 2022 | 608 pages
Small book image
PostgreSQL 16 Administration Cookbook
Boris Mejias | Simon Riggs | Gianni Ciolli | Vibhor Kumar | Dimitrios Angelakos
Dec, 2023 | 636 pages
Table of Contents (23 chapters)
Title Page
Title Page
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Customer Feedback
Customer Feedback
Preface
Preface
Free Chapter
1
Relational Databases
Relational Databases
Database management systems
Relational and object relational databases
Relational algebra
Data modeling
Summary
2
PostgreSQL in Action
PostgreSQL in Action
An overview of PostgreSQL
PostgreSQL capabilities
Installing PostgreSQL
Summary
3
PostgreSQL Basic Building Blocks
PostgreSQL Basic Building Blocks
Database coding
PostgreSQL objects hierarchy
PostgreSQL database components
The car web portal database
Summary
4
PostgreSQL Advanced Building Blocks
PostgreSQL Advanced Building Blocks
Views
View synopsis
View categories
Updatable views
Indexes
Functions
User-defined data types
Triggers and rule systems
Summary
5
SQL Language
SQL Language
SQL fundamentals
Querying data with SELECT statements
Changing the data in the database
Summary
6
Advanced Query Writing
Advanced Query Writing
Common table expressions
Window functions
Advanced SQL techniques
Summary
7
Server-Side Programming with PL/pgSQL
Server-Side Programming with PL/pgSQL
SQL language and PL/pgSQL – a comparison
PostgreSQL function parameters
The PostgreSQL PL/pgSQL control statements
Function predefined variables
Exception handling
Dynamic SQL
Summary
8
OLAP and Data Warehousing
OLAP and Data Warehousing
Online analytical processing
Partitioning
Parallel query
Index-only scans
Summary
9
Beyond Conventional Data Types
Beyond Conventional Data Types
Arrays
The hash store data structure
The JSON data structure
A PostgreSQL full text search
Summary
10
Transactions and Concurrency Control
Transactions and Concurrency Control
Transactions
Explicit locking
Summary
11
PostgreSQL Security
PostgreSQL Security
Authentication in PostgreSQL
Authentication best practices
PostgreSQL default access privileges
Role system and proxy authentication
PostgreSQL security levels
Encrypting data
Summary
12
The PostgreSQL Catalog
The PostgreSQL Catalog
The system catalog
System catalog for administrators
Cleaning up the database
Tuning for performance 
Selective dump
Summary 
13
Optimizing Database Performance
Optimizing Database Performance
PostgreSQL configuration tuning
Tuning performance for write
Tuning performance for read 
Detecting problems in query plans
Common mistakes in writing queries
Table partitioning
Query rewriting
Summary
14
Testing
Testing
Unit testing
Database abstraction interfaces
Summary
15
Using PostgreSQL in Python Applications
Using PostgreSQL in Python Applications
Python DB API 2.0
Low-level database access with psycopg2
Alternative drivers for PostgreSQL
Object relational mapping with SQLAlchemy
Summary
16
Scalability
Scalability
The problem of scalability and the CAP theorem
Data replication in PostgreSQL
Using replication to scale PostgreSQL
Summary

Selective dump

When refactoring a certain view, including adding a new column or changing the column type, one needs to refactor all the views that depend on this particular view. Unfortunately, PostgreSQL does not provide the means to create a logical dump of a dependent object.

PostgreSQL provides pg_dump to dump a certain database or a specific set of objects in a database. Also, in development, it is recommended that you keep the code in a Git repository.

Note

Plan your development cycle in advance, including development, testing, staging, and production. Use code versioning tools such as Git and database migration tools such as flyway to define your processes. This will save you a lot of time.

Unfortunately, often the SQL code for legacy applications is not maintained in the version control system and in migration tools such as flyway. In this case, if you need to change a certain view definition or even a column type, it is necessary to identify the affected views, dump them, and then...

Previous Section
End of Section 6
Next Section