Learning PostgreSQL 11 Third Edition

▶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

▶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

▶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.

▶What this book covers
⦁ Chapter 1, Relational Databases, discusses database management systems and covers relational databases as well.

⦁ Chapter 2, PostgreSQL in Action, guides the reader through the installation of PostgreSQL, establishing a connection to the server, and running very basic queries. Also, the reader will learn some general information about PostgreSQL's history and capabilities as well.

⦁ Chapter 3, PostgreSQL Basic Building Blocks, explores the basic building blocks of PostgreSQL. We'll also take a look at the hierarchy of the database objects in PostgreSQL. This will help you to understand how to configure the database cluster and tune its settings.

⦁ Chapter 4, PostgreSQL Advanced Building Blocks, discusses the more advanced PostgreSQL building blocks, including views, indexes, functions, triggers, and rules. In addition to that, the web car portal schema will be reviewed. Several Data Definition Language (DDL) commands will also be introduced in this chapter as well.

⦁ Chapter 5, SQL Language, explains the concept of SQL and the logic of SQL statements.

⦁ Chapter 6, Advanced Query Writing, discusses some more advanced SQL features supported by PostgreSQL, such as common table expressions (CTEs) and window functions. These features allow us to implement a logic that wouldn't be possible otherwise, that is, recursive queries.

⦁ Chapter 7, Server-Side Programming with PL/pgSQL, compares the SQL language and PL/pgSQL, and will also explain the function parameters of PostgreSQL.

⦁ Chapter 8, OLAP and Data Warehousing, discusses some features of PostgreSQL that will help you to implement a data warehouse solution based on a PostgreSQL database, and also covers the related theoretical concepts.

⦁ Chapter 9, Beyond Conventional Data Types, covers various data types, such as arrays, Hstore, JSON, and full-text search.

⦁ Chapter 10, Transactions and Concurrency Control, focuses on the basic concepts that guarantee the correct execution of transactions. It also discusses concurrency control problems, locking systems, deadlocks, and advisory locks.

⦁ Chapter 11, PostgreSQL Security, discusses authentication in PostgreSQL, including PostgreSQL host-based authentication and best practices.

⦁ Chapter 12, The PostgreSQL Catalog, discusses some tasks that developers and administrators need to do on a daily basis, for example, cleaning up database data and unused objects, building an index automatically, and monitoring database object access.

⦁ Chapter 13, Optimizing Database Performance, focuses on basic configuration and query rewriting. It also focuses on tuning performance for write and read, and also identifying and detecting problems in queries.

⦁ Chapter 14, Testing, discusses some techniques for testing database objects. They can be applied when implementing changes in the data structure of complex systems and when developing database interfaces.

⦁ Chapter 15, Using PostgreSQL in Python Applications, iscusses how to connect to a PostgreSQL database from applications written in Python.

⦁ Chapter 16, Scalability, discusses the problem of scalability and the CAP theorem. We will also learn about the different scaling scenarios and their implementation in PostgreSQL.

⦁ Chapter 17, What's Next?, summarizes the topics we've covered in this book, provides some tips, and points the reader in the right direction for further learning.