Enrico Pirozzi

This new edition will help you learn PostgreSQL from scratch with the latest version, providing a complete focused view on aspects like configuration, high performance, partitioning, backup, server-side programming and replication. Purchase of the print or Kindle book includes a free eBook in PDF format. Key Features Learn the fundamentals of PostgreSQL 16, including SQL statements, replication, and security Enhance your learning journey with the provided Docker images for practical hands-on exercises and tests at the end of each chapter Get new and improved examples, use-cases, and scenarios specifically for concepts like partitioning, replication, back-up and restore, cluster configuration, monitoring and others Book DescriptionThe latest edition of this PostgreSQL book will help you to start using PostgreSQL from absolute scratch, helping you to quickly understand the internal workings of the database. With a structured approach and practical examples, go on a journey that covers the basics, from SQL statements and how to run server-side programs, to configuring, managing, securing, and optimizing database performance. This new edition will not only help you get to grips with all the recent changes within the PostgreSQL ecosystem but will also dig deeper into concepts like partitioning and replication with a fresh set of examples. The book is also equipped with Docker images for each chapter which makes the learning experience faster and easier. Starting with the absolute basics of databases, the book sails through to advanced concepts like window functions, logging, auditing, extending the database, configuration, partitioning, and replication. It will also help you seamlessly migrate your existing database system to PostgreSQL and contains a dedicated chapter on disaster recovery. Each chapter ends with practice questions to test your learning at regular intervals. By the end of this book, you will be able to install, configure, manage, and develop applications against a PostgreSQL database. What you will learn Gain a deeper understanding of PostgreSQL internals like transactions, MVCC, security and replication Enhance data management with PostgreSQL’s latest partitioning features Choose the right replication strategy for your database See concrete examples of how to migrate data from another database, perform backups and restores, monitor your PostgreSQL installation and more Ensure security and compliance with schemas and user privileges Create customized database functions and extensions Get to grips with server-side programming, window functions, and triggers Who this book is forLearning PostgresSQL 16 book is for anyone interested in learning about the PostgreSQL database from scratch. Anyone looking to build robust data warehousing applications and scale the database for high-availability and performance using the latest features of PostgreSQL will also find this book useful. Although prior knowledge of PostgreSQL is not required, familiarity with databases is expected.

A comprehensive guide to building, managing, and securing scalable and reliable database and data warehousing applications using Postgres 12 and 13Key FeaturesSet up your database cluster and monitor, secure, and fine-tune it for optimal performanceLearn the fundamentals of database management and implement client- and server-side programming using SQL and PL/pgSQLExplore useful tips to develop efficient PostgreSQL database solutions from scratchBook DescriptionPostgreSQL 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 learnUnderstand how users and connections are managed by running a PostgreSQL instanceInteract with transaction boundaries using server-side programmingIdentify bottlenecks to maintain your database efficientlyCreate and manage extensions to add new functionalities to your clusterChoose the best index type for each situationUse online tools to set up a memory configuration that will suit most databasesExplore how Postgres can be used in multi-instance environments to provide high-availability, redundancy, and scalabilityWho this book is forThis Postgres book is for anyone interested in learning about the PostgreSQL database from scratch. Anyone looking to build robust data warehousing applications and scale the database for high-availability and performance using the latest features of PostgreSQL will also find this book useful. Although prior knowledge of PostgreSQL is not required, familiarity with databases is expected.

Leverage the power of PostgreSQL 10 to design, administer and maintain a high-performance database solution About This Book • Obtain optimal PostgreSQL 10 database performance, ranging from initial design to routine maintenance • Fine tune the performance of your queries and avoid the common pitfalls that can slow your system down • Contains tips and tricks on scaling successful database installations, and ensuring a highly available PostgreSQL solution Who This Book Is For This book is designed for database administrators and PostgreSQL architects who already use or plan to exploit the features of PostgreSQL 10 to design and maintain a high-performance PostgreSQL database. A working knowledge of SQL, and some experience with PostgreSQL will be helpful in getting the most out of this book. What You Will Learn • Learn best practices for scaling PostgreSQL 10 installations • Discover the best hardware for developing high-performance PostgreSQL applications • Benchmark your whole system – from hardware to application • Learn by real examples how server parameters impact performance • Discover PostgreSQL 10 features for partitioning and parallel query • Monitor your server, both inside and outside the database • Design and implement a good replication system on PostgreSQL 10 In Detail PostgreSQL database servers have a common set of problems that they encounter as their usage gets heavier and requirements get more demanding. Peek into the future of your PostgreSQL 10 database's problems today. Know the warning signs to look for and how to avoid the most common issues before they even happen. Surprisingly, most PostgreSQL database applications evolve in the same way—choose the right hardware, tune the operating system and server memory use, optimize queries against the database and CPUs with the right indexes, and monitor every layer, from hardware to queries, using tools from inside and outside PostgreSQL. Also, using monitoring insight, PostgreSQL database applications continuously rework the design and configuration. On reaching the limits of a single server, they break things up; connection pooling, caching, partitioning, replication, and parallel queries can all help handle increasing database workloads. By the end of this book, you will have all the knowledge you need to design, run, and manage your PostgreSQL solution while ensuring high performance and high availability Style and approach This book has been organized in such a manner that will help you understand basic PostgreSQL 10 performance tuning to an advanced-level configuration.