Learning PostgreSQL 11

545 Pages·2019·10.593 MB·English

by  Juba, Salahaldin;Volkov, Andrey

Learning PostgreSQL 11
Third Edition
A beginner's guide to building high-performance PostgreSQL
database solutions
Salahaldin Juba
Andrey Volkov
BIRMINGHAM - MUMBAI
Learning PostgreSQL 11
Third Edition
Copyright © 2019 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form
or by any means, without the prior written permission of the publisher, except in the case of brief quotations
embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented.
However, the information contained in this book is sold without warranty, either express or implied. Neither the
authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to
have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products
mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy
of this information.
Commissioning Editor: Pravin Dhandre
Acquisition Editor: Joshua Nadar
Content Development Editors: Roshan Kumar, Pooja Parvatkar
Technical Editor: Nilesh Sawakhande
Copy Editor: Safis Editing
Project Coordinator: Namrata Swetta
Proofreader: Safis Editing
Indexer: Tejal Daruwale Soni
Graphics: Jisha Chirayil
Production Coordinator: Shraddha Falebhai
First published: November 2015
Second edition: December 2017
Third edition: January 2019
Production reference: 1310119
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-78953-546-4
www.packtpub.com
mapt.io
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as
well as industry leading tools to help you plan your personal development and advance
your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos
from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
Packt.com
Did you know that Packt offers eBook versions of every book published, with PDF and
ePub files available? You can upgrade to the eBook version at www.packt.com and as a print
book customer, you are entitled to a discount on the eBook copy. Get in touch with us at
[email protected] for more details.
At www.packt.com, you can also read a collection of free technical articles, sign up for a
range of free newsletters, and receive exclusive discounts and offers on Packt books and
eBooks.
Contributors
About the authors
Salahaldin Juba has over a decade of experience in industry and academia, with a focus on
database development for large-scale and enterprise applications. He holds an MSc in
environmental management with a distinction, and a B.Eng in computer systems. He is also
a Microsoft Certified Solution Developer (MCSD).
He has worked mainly with SQL server, PostgreSQL, and Greenplum databases. As a
software engineer, he works mainly with defining ETL processes with external parties,
promoting SQL best practices, designing OLTP and OLAP applications, and providing
training and consultation services.
Andrey Volkov studied information systems in banking, and started his career as a
financial analyst at a commercial bank. He joined a data warehouse team, and after some
time, he lead the team by taking the position of the data warehouse architect.
There he worked mainly with Oracle database stack and used it to develop logical and
physical models of financial and accounting data, implement them in the database, develop
ETL processes, and perform analytical tasks.
Now Andrey works as a senior database developer at a telecommunication provider. There,
he works mainly with PostgreSQL databases, being responsible for data modeling,
developing a data warehouse, reporting, and billing systems.
About the reviewers
Alexey Lesovsky has worked in web development and worked with Linux and related
stuff in his early career as a system administrator. His favorite topics were and are KVM
virtualization, monitoring systems, and Linux itself.
Being a system administrator, he was exposed to PostgreSQL in its early years. His interests
then moved from system administration to pure database administration for PostgreSQL
databases.
He has been with Data Egret for close to five years and has been a great asset for
developing new Postgres utilities, including the famous PGCenter.
He is also a frequent speaker at various PostgreSQL events, among them PGCon, PGConf
(dot) EU, and others.
He has also authored Getting Started with oVirt 3.3 by Packt Publishing.
Darren Douglas is Chief Instructor at PostgresCourse (dot) com. He has taught PostgreSQL
for over 4 years and has trained professional DBAs from Fortune 500 companies as well as
the US and Canadian federal governments.
Since first using PostgreSQL in 2008, he has since led database development
and deployment with PostgreSQL across multiple industries including e-commerce,
mining, mobile marketing, and insurance. He also consults on full-stack software
development projects and DevOps initiatives.
He lives in Tucson, Arizona with his wife Joanah. Together they spend time in volunteer
projects and are learning the Swahili language.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com
and apply today. We have worked with thousands of developers and tech professionals,
just like you, to help them share their insight with the global tech community. You can
make a general application, apply for a specific hot topic that we are recruiting an author
for, or submit your own idea.
Table of Contents
Chapter 1: Relational Databases 7
Database management systems 7
A brief history 8
Database categories 9
NoSQL databases 9
The CAP theorem 9
NoSQL motivation 10
Key-value databases 10
Columnar databases 10
Document databases 11
Graph databases 11
Relational and object-relational databases 11
ACID properties 12
The SQL language 13
Relational model concepts 13
Relation 13
Tuple 14
The null value 15
Attribute 16
Constraint 17
Domain integrity constraint 17
Entity integrity constraint 18
Referential integrity constraints 19
Semantic constraints 21
Relational algebra 21
The SELECT and PROJECT operations 23
The RENAME operation 25
The set theory operations 25
The Cartesian product operation 26
Data modeling 27
Data model perspectives 28
The entity-relation model 28
Sample application 29
Entities, attributes, and keys 30
Mapping ER to relations 34
UML class diagrams 34
Summary 36
Questions 36
Chapter 2: PostgreSQL in Action 38
An overview of PostgreSQL 38
PostgreSQL history 39
Table of Contents
The advantages of PostgreSQL 39
Business advantages of PostgreSQL 39
PostgreSQL user advantages 40
PostgreSQL applications 41
Success stories 42
Forks 42
PostgreSQL architecture 43
The PostgreSQL community 45
PostgreSQL capabilities 45
Replication 46
Security 47
Extensions 48
NoSQL capabilities 49
Foreign data wrappers 50
Performance 53
Installing PostgreSQL 54
Installing PostgreSQL on Linux via source 55
Installing PostgreSQL using Advanced Package Tool 57
Client installation 57
Server installation 58
Basic server configuration 60
Installing PostgreSQL on Windows 62
PostgreSQL clients 65
The psql client 66
psql advanced settings 68
PostgreSQL utility tools 71
Summary 72
Questions 73
Chapter 3: PostgreSQL Basic Building Blocks 74
Database coding 74
Database naming conventions 75
PostgreSQL identifiers 76
Documentation 76
Version control systems 77
Database-migration tools 77
PostgreSQL object hierarchy 78
Template databases 78
User databases 79
Roles 81
Tablespaces 83
Template procedural languages 83
Settings 83
PostgreSQL high-level object interaction 86
PostgreSQL database components 88
Schemas 88
[ ii ]
Table of Contents
Schema usages 89
Tables 90
PostgreSQL native data types 91
Numeric types 92
Serial types and identity columns 95
Character types 97
Date and time types 100
The car web portal database 104
Summary 109
Questions 110
Chapter 4: PostgreSQL Advanced Building Blocks 111
Views 112
View synopsis 113
View categories 114
Materialized views 115
Updatable views 116
Indexes 118
Index synopses 119
Index selectivity 120
Index types 123
Index categories 124
Best practices for indexes 127
Functions 129
The PostgreSQL native programming language 129
Creating a function in the C language 129
Function usage 132
Function dependencies 132
PostgreSQL function categories 133
PostgreSQL anonymous functions 134
User-defined data types 135
Trigger and rule systems 139
Rule system 140
Trigger system 142
Triggers with arguments 145
Triggers and updatable views 147
Summary 151
Questions 152
Chapter 5: SQL Language 153
SQL fundamentals 154
SQL lexical structure 155
Querying data with SELECT statements 159
Structure of a SELECT query 160
SELECT-list 163
SQL expressions 165
[ iii ]