Learning PostgreSQL 11

Learning PostgreSQL 11

by Salahaldin Juba, Andrey Volkov
Released January 2019
Publisher(s): Packt Publishing
ISBN: 9781789535464

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. Learning PostgreSQL 11 Third Edition
  3. About Packt
    1. Why subscribe?
    2. Packt.com
  4. Contributors
    1. About the authors
    2. About the reviewers
    3. Packt is searching for authors like you
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Get in touch
      1. Reviews
  6. Relational Databases
    1. Database management systems
      1. A brief history
      2. Database categories
        1. NoSQL databases
          1. The CAP theorem
          2. NoSQL motivation
        2. Key-value databases
        3. Columnar databases
        4. Document databases
        5. Graph databases
    2. Relational and object-relational databases
      1. ACID properties
      2. The SQL language
      3. Relational model concepts
        1. Relation
        2. Tuple
        3. The null value
        4. Attribute
        5. Constraint
        6. Domain integrity constraint
        7. Entity integrity constraint
        8. Referential integrity constraints
        9. Semantic constraints
    3. Relational algebra
      1. The SELECT and PROJECT operations
      2. The RENAME operation
      3. The set theory operations
      4. The Cartesian product operation
    4. Data modeling
      1. Data model perspectives
      2. The entity-relation model
        1. Sample application
          1. Entities, attributes, and keys
        2. Mapping ER to relations
      3. UML class diagrams
    5. Summary
    6. Questions
  7. PostgreSQL in Action
    1. An overview of PostgreSQL
      1. PostgreSQL history
      2. The advantages of PostgreSQL
        1. Business advantages of PostgreSQL
        2. PostgreSQL user advantages
      3. PostgreSQL applications
      4. Success stories
      5. Forks
      6. PostgreSQL architecture
      7. The PostgreSQL community
    2. PostgreSQL capabilities
      1. Replication
      2. Security
      3. Extensions
      4. NoSQL capabilities
      5. Foreign data wrappers
      6. Performance
    3. Installing PostgreSQL
      1. Installing PostgreSQL on Linux via source
      2. Installing PostgreSQL using Advanced Package Tool
        1. Client installation
        2. Server installation
        3. Basic server configuration
      3. Installing PostgreSQL on Windows
      4. PostgreSQL clients
        1. The psql client
          1. psql advanced settings
        2. PostgreSQL utility tools
    4. Summary
    5. Questions
  8. PostgreSQL Basic Building Blocks
    1. Database coding
      1. Database naming conventions
      2. PostgreSQL identifiers
      3. Documentation
      4. Version control systems
      5. Database-migration tools
    2. PostgreSQL object hierarchy
      1. Template databases
      2. User databases
      3. Roles
      4. Tablespaces
      5. Template procedural languages
      6. Settings
      7. PostgreSQL high-level object interaction
    3. PostgreSQL database components
      1. Schemas
        1. Schema usages
      2. Tables
    4. PostgreSQL native data types
      1. Numeric types
        1. Serial types and identity columns
      2. Character types
      3. Date and time types
    5. The car web portal database
    6. Summary
    7. Questions
  9. PostgreSQL Advanced Building Blocks
    1. Views
      1. View synopsis
      2. View categories
      3. Materialized views
      4. Updatable views
    2. Indexes
      1. Index synopses
      2. Index selectivity
      3. Index types
      4. Index categories
      5. Best practices for indexes
    3. Functions
      1. The PostgreSQL native programming language
      2. Creating a function in the C language
      3. Function usage
      4. Function dependencies
      5. PostgreSQL function categories
      6. PostgreSQL anonymous functions
    4. User-defined data types
    5. Trigger and rule systems
      1. Rule system
      2. Trigger system
        1. Triggers with arguments
        2. Triggers and updatable views
    6. Summary
    7. Questions
  10. SQL Language
    1. SQL fundamentals
      1. SQL lexical structure
    2. Querying data with SELECT statements
      1. Structure of a SELECT query
      2. SELECT-list
        1. SQL expressions
        2. DISTINCT
      3. The FROM clause
        1. Selecting from multiple tables
        2. Self-joins
      4. The WHERE clause
        1. Comparison operators
        2. Pattern matching
        3. Row and array comparison constructs
      5. Grouping and aggregation
        1. The GROUP BY clause
        2. The HAVING clause
      6. Ordering and limiting results
      7. Subqueries
      8. Set operations – UNION, EXCEPT, and INTERSECT
      9. Dealing with NULLS
    3. Changing the data in a database
      1. The INSERT statement
      2. The UPDATE statement
        1. UPDATE using sub-select
        2. UPDATE using additional tables
      3. The DELETE statement
      4. The TRUNCATE statement
    4. Summary
    5. Questions
  11. Advanced Query Writing
    1. Common table expressions
      1. Reusing SQL code with CTE
      2. Recursive and hierarchical queries
      3. Changing data in multiple tables at a time
    2. Window functions
      1. Window definitions
      2. The WINDOW clause
      3. Using window functions
      4. Window functions with grouping and aggregation
    3. Advanced SQL techniques
      1. Selecting the first records
      2. Selecting a data sample
      3. Set-returning functions
      4. Lateral subqueries
      5. Advanced grouping
      6. Advanced aggregation
    4. Summary
    5. Questions
  12. Server-Side Programming with PL/pgSQL
    1. The SQL language and PL/pgSQL – a comparison
    2. Functions and procedures – a comparison
    3. PostgreSQL function parameters
      1. Function authorization related parameters
      2. Function planner related parameters
      3. Function configuration related parameters
    4. The PostgreSQL PL/pgSQL control statements
      1. Declaration statements
      2. Assignment statements
      3. Conditional statements
      4. Iterations
        1. Loop statements
        2. The WHILE loop statement
        3. The FOR loop statement
      5. Returning from the function
        1. Returning voids
        2. Returning a single row
        3. Returning multiple rows
    5. Function predefined variables
    6. Exception handling
    7. Dynamic SQL
      1. Executing DDL statements in dynamic SQL
      2. Executing DML statements in dynamic SQL
      3. Dynamic SQL and the caching effect
      4. Recommended practices for dynamic SQL usage
    8. Summary
    9. Questions
  13. OLAP and Data Warehousing
    1. OLTP and OLAP
    2. Online analytical processing
      1. Loading data into a data warehouse
      2. Data modeling for OLAP
      3. Aggregation
    3. Partitioning
      1. Declarative partitioning
      2. Sub-partitioning
      3. Performance improvement
    4. Parallel query
    5. Index-only scans
    6. Summary
    7. Questions
  14. Beyond Conventional Data Types
    1. Arrays
      1. Common functions of arrays and their operators
      2. Modifying and accessing arrays
      3. Indexing arrays
    2. The hash store data structure
      1. Indexing an hstore
    3. The JSON data structure
      1. JSON and XML
      2. JSON data types for PostgreSQL
      3. The benefits of supporting the JSON data type
      4. Modifying and accessing JSON types
      5. Indexing a JSON data type
      6. RESTful APIs using PostgreSQL
    4. A PostgreSQL full-text search
      1. The tsquery and tsvector data types
        1. The tsvector data type
        2. The tsquery data type
      2. Pattern matching
      3. Full-text search indexing
    5. Summary
    6. Questions
  15. Transactions and Concurrency Control
    1. Transactions
      1. Transaction and ACID properties
      2. Transaction and concurrency
        1. MVCC in PostgreSQL
      3. Transaction isolation levels
    2. Explicit locking
      1. Table-level locks
        1. Table locking modes
      2. Row-level locks
        1. Row-level lock modes
      3. Deadlocks
      4. Advisory locks
    3. Transaction in functions and procedures
    4. Summary
    5. Questions
  16. PostgreSQL Security
    1. Authentication in PostgreSQL
      1. PostgreSQL pg_hba.conf
      2. Listen addresses
    2. Authentication best practices
    3. PostgreSQL default access privileges
    4. Role system and proxy authentication
    5. PostgreSQL security levels
      1. Database security level
      2. Schema security level
      3. Object-level security
      4. Column-level security
      5. Row-level security
    6. Encrypting data
      1. PostgreSQL role password encryption
      2. pgcrypto
        1. One-way encryption
        2. Two-way encryption
    7. Summary
    8. Questions
  17. The PostgreSQL Catalog
    1. The system catalog
    2. System catalog for administrators
      1. Getting the database cluster and client tools version
      2. Terminating and canceling user sessions
      3. Defining and getting database cluster settings
      4. Getting the database and database object size
    3. Cleaning up the database
      1. Data cleanup
    4. Tuning for performance
    5. Selective dump
    6. Summary
    7. Questions
  18. Optimizing Database Performance
    1. PostgreSQL configuration tuning
      1. Maximum number of connections
      2. Memory settings
      3. Disk settings
      4. Planner-related settings
      5. Benchmarking is your friend
    2. Tuning performance for write
    3. Tuning performance for read
      1. Explaining the command and execution plans
    4. Detecting problems in query plans
    5. Common mistakes in writing queries
      1. Unnecessary operations
      2. Misplaced or missing indexes
      3. Using CTE when not mandatory
      4. Using the PL/pgSQL procedural language consideration
      5. Cross-column correlation
    6. Table partitioning
      1. Constraint exclusion limitations
    7. Query rewriting
    8. Summary
    9. Questions
  19. Testing
    1. Technical requirements
    2. Unit testing
      1. Specificity of unit testing in databases
      2. Unit test frameworks
      3. Schema difference
    3. Database-abstraction interfaces
      1. Data differences
      2. Performance testing
    4. Summary
    5. Questions
  20. Using PostgreSQL in Python Applications
    1. Technical requirements
    2. Python DB-API 2.0
    3. Low-level database access with Psycopg 2
      1. Connecting to a database
      2. Connection pooling
      3. Executing SQL commands
      4. Reading data from a database
      5. The COPY command
      6. Asynchronous access
    4. Alternative drivers for PostgreSQL
      1. pg8000
      2. asyncpg
    5. Object-relational mapping with SQLAlchemy
      1. Main components of SQLAlchemy
      2. Connecting to a database and retrieving data with SQL expression language
      3. ORM
    6. Summary
    7. Questions
  21. Scalability
    1. Technical requirements
    2. The problem of scalability and the CAP theorem
    3. Data replication in PostgreSQL
      1. Transaction log
      2. Physical replication
        1. Log shipping replication
        2. Streaming replication
        3. Synchronous replication
        4. Failover procedures
      3. Logical replication
    4. Building a scalable PostgreSQL solution
      1. Scaling for heavy querying
      2. Data sharding
      3. Scaling for many numbers of connections
    5. Summary
    6. Questions
    7. Further reading
  22. What's Next?
    1. Fundamentals
      1. Normalization
      2. Denormalization
      3. NoSQL databases
    2. Working with PostgreSQL software
      1. The PostgreSQL server
      2. PostgreSQL client software
    3. Working with data in PostgreSQL databases
      1. PostgreSQL schema objects
      2. Working with data
      3. PostgreSQL as a component of a software solution
    4. Summary
  23. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Learning PostgreSQL 11
  • Author(s): Salahaldin Juba, Andrey Volkov
  • Release date: January 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781789535464