Henrietta Dombrovskaya - PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries

Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the books product page, located at www.apress.com/9781484268841 . For more detailed information, please visit http://www.apress.com/source-code .

This Apress imprint is published by the registered company APress Media, LLC part of Springer Nature.

The registered company address is: 1 New York Plaza, New York, NY 10004, U.S.A.

Optimization is a broad enough term to encompass performance tuning, personal improvement, and marketing via social engine and invariably evinces high hopes and expectations from readers. As such, we find it prudent to begin this book not with an introduction to what is covered, but rather, why this book exists and what will not be covered, to avoid disappointing any readers who approach it with inappropriate expectations. Then, we proceed with what this book is about, the target audience, what is covered, and how to get the most use out of it.

Like many authors, we wrote this book because we felt we could not not write it. We are both educators and practitioners; hence, we see both how and what computer science students are taught in class and what knowledge they lack when they enter the workforce. We do not like what we see and hope this book will help bridge this gap.

When learning about data management, most students never see a real production database, and even more alarming, many of their professors never see one, either. While lack of exposure to real-life systems affects all computer science students, the education of future database developers and database administrators (DBAs) suffers the most. Using a small training database, one can learn how to write syntactically correct SQL and even write a SELECT statement that properly asks for the desired result. However, learning to write performant queries requires a production-sized dataset. Moreover, it might not be evident that performance might present a problem if a student is operating on a dataset that can easily fit into the computers main memory and return a result in milliseconds regardless of the complexity of the query.

In addition to lacking exposure to realistic datasets, students often dont use DBMSs that are widely used in industry. While the preceding statement is true in relation to many DBMSs, in the case of PostgreSQL, it is even more frustrating. PostgreSQL originated in an academic environment and is maintained as an open source project, making it an ideal database for teaching relational theory and demonstrating database internals. However, so far, few academic institutions have adopted PostgreSQL for their educational needs.

While PostgreSQL is rapidly developing and becoming a more powerful tool, more and more businesses favor it over proprietary DBMSs in an attempt to reduce costs. More and more IT managers are looking for employees who are familiar with PostgreSQL. More and more potential candidates learn to use PostgreSQL on their own and miss opportunities to get the most out of it.

We hope that this book will help all interested parties: candidates, hiring managers, database developers, and organizations that are switching to PostgreSQL for their data needs.

Many people believe that optimization is a sort of magic possessed by an elite circle of wizards. They believe that they can be admitted into this circle if they receive tokens of sacred knowledge from their elders. And as soon as they have the keys, their abilities will have no limits.

There are plenty of books available that cover all of the topics listed in the preceding list, except probably black magic, but this book is not one of them. Instead, we focus on everyday challenges database developers face: when that one application page keeps timing out, when a customer is kicked out of the application just before the Contract Signed page, when the CEO dashboard is showing an hourglass instead of yesterdays product KPI, and when procuring more hardware is not an option.

Everything we present in this book has been tested and implemented in an industrial environment, and though it may look like black magic, we will explain any query performance improvement or lack thereof.

Most of the time, a book about optimization is viewed as a book for DBAs. Since our goal is to prove that optimization is more than just building indexes, we hope that this book will be beneficial for a broader audience.