Updated April 10, 2023
Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications, 5th Edition
- Schonig, Hans-Jurgen (Author)
- English (Publication Language)
- 522 Pages - 01/31/2023 (Publication Date) - Packt Publishing (Publisher)
by Hans-Jurgen Schonig. Starting with an introduction to the newly released features of PostgreSQL 15, this updated fifth edition will help you get to grips with PostgreSQL administration and how to build dynamic database solutions for enterprise apps, including designing both physical and technical aspects of the system.
You’ll explore advanced PostgreSQL features, such as logical replication, database clusters, advanced indexing, and user management to manage and maintain your database. You’ll then work with the PostgreSQL optimizer, configure PostgreSQL for high speed, and move from Oracle to PostgreSQL. Among the other skills that the book will help you build, you’ll cover transactions, handling recursions, working with JSON and JSONB data, and setting up a Patroni cluster. It will show you how to improve performance with query optimization. You’ll also focus on managing network security and work with backups and replication while exploring useful PostgreSQL extensions that optimize the performance of large databases.
By the end of this PostgreSQL book, you’ll be able to use your database to its utmost capacity by implementing advanced administrative tasks with ease.
What you will learn:
Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries
Work with stored procedures and manage backup and recovery
Get the hang of replication and failover techniques
Improve the security of your database server and handle encryption effectively
Troubleshoot your PostgreSQL instance for solutions to common and not-so-common problems
Perform database migration from Oracle to PostgreSQL with ease
PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries
- Dombrovskaya, Henrietta (Author)
- English (Publication Language)
- 344 Pages - 04/23/2021 (Publication Date) - Apress (Publisher)
Write optimized queries. This book helps you write queries that perform fast and deliver results on time. You will learn that query optimization is not a dark art practiced by a small, secretive cabal of sorcerers. Any motivated professional can learn to write efficient queries from the get-go and capably optimize existing queries. You will learn to look at the process of writing a query from the database engine’s point of view, and know how to think like the database optimizer.
The book begins with a discussion of what a performant system is and progresses to measuring performance and setting performance goals. It introduces different classes of queries and optimization techniques suitable to each, such as the use of indexes and specific join algorithms. You will learn to read and understand query execution plans along with techniques for influencing those plans for better performance. The book also covers advanced topics such as the use of functions and procedures, dynamic SQL, and generated queries. All of these techniques are then used together to produce performant applications, avoiding the pitfalls of object-relational mappers.
What You Will Learn
Identify optimization goals in OLTP and OLAP systems
Read and understand PostgreSQL execution plans
Distinguish between short queries and long queries
Choose the right optimization technique for each query type
Identify indexes that will improve query performance
Optimize full table scans
Avoid the pitfalls of object-relational mapping systems
Optimize the entire application rather than just database queries
PostgreSQL 14 Administration Cookbook: Over 175 proven recipes for database administrators to manage enterprise databases effectively
by Simon Riggs and Gianni Ciolli. PostgreSQL is a powerful, open-source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 14 allows you to scale up your PostgreSQL infrastructure. With this book, you’ll take a step-by-step, recipe-based approach to effective PostgreSQL administration.
This book will get you up and running with all the latest features of PostgreSQL 14 while helping you explore the entire database ecosystem. You’ll learn how to tackle a variety of problems and pain points you may face as a database administrator such as creating tables, managing views, improving performance, and securing your database. As you make progress, the book will draw attention to important topics such as monitoring roles, validating backups, regular maintenance, and recovery of your PostgreSQL 14 database. This will help you understand roles, ensuring high availability, concurrency, and replication. Along with updated recipes, this book touches upon important areas like using generated columns, TOAST compression, PostgreSQL on the cloud, and much more.
By the end of this PostgreSQL book, you’ll have gained the knowledge you need to manage your PostgreSQL 14 database efficiently, both in the cloud and on-premise.
What you will learn
Plan, manage, and maintain PostgreSQL databases in production
Work with the newly introduced features of PostgreSQL 14
Use pgAdmin or OmniDB to perform database administrator (DBA) tasks
Use psql to write accurate and repeatable scripts
Understand how to tackle real-world data issues with the help of examples
Select and implement robust backup and recovery techniques in PostgreSQL 14
Deploy best practices for planning and designing live databases
PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database
- Obe, Regina (Author)
- English (Publication Language)
- 312 Pages - 11/28/2017 (Publication Date) - O'Reilly Media (Publisher)
by Regina Obe, Leo Hsu aids in the understanding and application of this open source database system Not only will you learn about the enterprise-class capabilities in versions 9.5 through 10, but you’ll also learn how to use them. This book explains you how to accomplish activities that are difficult or impossible in other databases, using examples throughout. This third edition includes new features such as foreign data wrapper (FDW) enhancements, new full text functions and operator syntax introduced in version 9.6, XML constructs introduced in version 10, query parallelization features introduced in 9.6 and enhanced in 10, and built-in logical replication introduced in Version 10.e. You will:
Learn basic administration tasks such as role management, database creation, backup, and restore
Apply the psql command-line utility and the pgAdmin graphical administration tool
Explore PostgreSQL tables, constraints, and indexes
Learn powerful SQL constructs not generally found in other databases
Use several different languages to write database functions
Tune your queries to run as fast as your hardware will allow
Query external and variegated data sources with foreign data wrappers
Learn how to use built-in replication to replicate data
Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13
- Ferrari, Luca (Author)
- English (Publication Language)
- 650 Pages - 10/09/2020 (Publication Date) - Packt Publishing (Publisher)
by Luca Ferrari and Enrico Pirozzi. PostgreSQL is one of the fastest-growing open source object-relational database management systems (DBMS) in the world. As well as being easy to use, it’s scalable and highly efficient. In this book, you’ll explore PostgreSQL 12 and 13 and learn how to build database solutions using it. Complete with hands-on tutorials, this guide will teach you how to achieve the right database design required for a reliable environment.
You’ll learn how to install and configure a PostgreSQL server and even manage users and connections. The book then progresses to key concepts of relational databases, before taking you through the Data Definition Language (DDL) and commonly used DDL commands. To build on your skills, you’ll understand how to interact with the live cluster, create database objects, and use tools to connect to the live cluster. You’ll then get to grips with creating tables, building indexes, and designing your database schema. Later, you’ll explore the Data Manipulation Language (DML) and server-side programming capabilities of PostgreSQL using PL/pgSQL, before learning how to monitor, test, and troubleshoot your database application to ensure high-performance and reliability.
By the end of this book, you’ll be well-versed with the Postgres database and be able to set up your own PostgreSQL instance and use it to build robust solutions.
What you will learn
Understand how users and connections are managed by running a PostgreSQL instance
Interact with transaction boundaries using server-side programming
Identify bottlenecks to maintain your database efficiently
Create and manage extensions to add new functionalities to your cluster
Choose the best index type for each situation
Use online tools to set up a memory configuration that will suit most databases
Explore how Postgres can be used in multi-instance environments to provide high-availability, redundancy, and scalability
This is the best PostgreSQL book in 2023.
PostgreSQL Server Programming Second Edition
- Used Book in Good Condition
- Douglas, Korry (Author)
- English (Publication Language)
PostgreSQL Server Programming by Usama Dar, Hannu Krosing, Jim Mlodgenski and Kirk Roybal is intended for moderate to advanced PostgreSQL database professionals who wish to extend PostgreSQL, using the latest PostgreSQL 9.4 functionality. For a better understanding of this best books, some knowledge of SQL writing, a basic idea of query tuning, and some experience coding in your preferred language is expected. You will learn:
Write functions in the built-in PL / pgSQL language or the language of your choice
Define and use user-defined functions (UDF)
Update your data with PL / Proxy
Extend PostgreSQL to overcome the limitations of traditional SQL servers
Debugging using NOTIFY and using the PL / pgSQL debugging extensions
Learn about new features in PostgreSQL 9.4, such as event triggers and other relevant enhancements for database server side programming.
Fully integrate the database layer into your development
PostgreSQL 13 Cookbook
- Kumar, Vallarapu Naga Avinash (Author)
- English (Publication Language)
- 344 Pages - 02/26/2021 (Publication Date) - Packt Publishing (Publisher)
PostgreSQL has become the most advanced open source database on the market. This book follows a step-by-step approach, guiding you effectively in deploying PostgreSQL in production environments.
The book starts with an introduction to PostgreSQL and its architecture. You’ll cover common and not-so-common challenges faced while designing and managing the database. Next, the book focuses on backup and recovery strategies to ensure your database is steady and achieves optimal performance. Throughout the book, you’ll address key challenges such as maintaining reliability, data integrity, a fault-tolerant environment, a robust feature set, extensibility, consistency, and authentication. Moving ahead, you’ll learn how to manage a PostgreSQL cluster and explore replication features for high availability. Later chapters will assist you in building a secure PostgreSQL server, along with covering recipes for encrypting data in motion and data at rest. Finally, you’ll not only discover how to tune your database for optimal performance but also understand ways to monitor and manage maintenance activities, before learning how to perform PostgreSQL upgrades during downtime. By the end of this book, you’ll be well-versed with the essential PostgreSQL 13 features to build enterprise relational databases.
What you will learn:
Understand logical and physical backups in Postgres
Demonstrate the different types of replication methods possible with PostgreSQL today
Set up a high availability cluster that provides seamless automatic failover for applications
Secure a PostgreSQL encryption through authentication, authorization, and auditing
Analyze the live and historic activity of a PostgreSQL server
Understand how to monitor critical services in Postgres 13
Manage maintenance activities and performance tuning of a PostgreSQL cluster
This PostgreSQL book is for database architects, database developers and administrators, or anyone who wants to become well-versed with PostgreSQL 13 features to plan, manage, and design efficient database solutions. Prior experience with the PostgreSQL database and SQL language is expected.