Now More People Can Optimize PostgreSQL Queries

I am excited to tell you about a forthcoming book, entitled “Postgres Query Optimization: The Ultimate Guide to Building Efficient Queries,” being published by Apress and will be available in the spring of 2021. I have had the honor of reviewing this book for the authors who are: Henrietta Dombrovskaya, Boris Novikov, and Anna Bailliekova. 

I worked with Henrietta and Boris prior to reviewing this book for them. I met Anna during the review process. They are exactly the right people to write this book. A great combination of people who are both database practitioners and database academics.

But wait, there is more...

The book is only ½ the story. It comes with what I feel PostgreSQL has needed for a very long time. A fantastic sample database where detailed examples are from the book can be tested out and experimented with. 

So why do I think this book is important?

Reason One:  It is all about step six.

Let me explain the usual interactions I have had over the past decade when helping somebody with PostgreSQL performance issues.

Person with a performance issue:

“I need you to help me tune my queries.”

Me and or somebody I manage:

OK, here are the steps we are going to follow:

1. Tune your operating system for Postgres (which is effectively tuning your OS for just about any database.
2. Tune your Postgres server parameters for your hardware and OS.
3. Ensure there is not excessive table bloat involved with the queries you are concerned about.
4. Evaluate if you have any query operations that spilling over to the disk
5. Check to ensure your table statistics are up to date.
6. We will evaluate how to tune your queries if steps 1-5 didn’t fix your performance issues.

I have seen a number of books and resources for explaining steps 1-5 in great detail that touch on step six. However, this is the first book of its kind that I have seen that gives people all the resources they need to address step 6. 

Many times the process doesn't get to step six, the performance issue is gone as a result of actions taken in steps 1-5.  However, if things do progress to step 6 the number of people who can help you and the resources you have access to gets significantly smaller. In addition, trying to explain to somebody what steps need to be taken is very challenging. There are many resources and books that can provide in-depth information about how to handle steps 1-5 and touch on step 6. No book that I am aware of adequately captures how to properly deal with step 6.

This book fixes that issue. Within a year of this book being published, I predict the number of people who can participate in step 6 will grow by an order of magnitude.

Don’t get me wrong, the PostgreSQL Query optimizer is fantastic. Over the years, I have had discussions with many community members of the Postgres community about how they chose PostgreSQL to be the database they spend their time on. Many prominent community members, including Simon Riggs, Bruce Momjian and Robert Haas have told me that the elegance of the query planner/optimizer is why they chose PostgreSQL. In fact, because it is so good, oftentimes step 6 is not needed. Unfortunately, when it is, there is often not enough expertise immediately available.

Reason 2: There is a great sample database

In the process of writing this book, the authors developed a great sample database that can be used for practicing query development and query optimization. It is an airline reservation system database that I can’t wait to try out all sorts of things with. It will be a great tool for wanna be query optimizers to refine and perfect their skills. In addition, it will be a wonderful source for developing Postgres training materials. 

Great news, this database is available now! You can find it here.

While I am at it, here is a plug for a great new feature in pgAdmin. A reverse engineering ERD tool. It is currently in Alpha but I was able to try the reverse engineering function out with this sample database. It worked great. See below:

Reason 3: NORM (No ORM).

This will be the topic of future blogs for me. I learned a lot in reviewing this book. However, the real “Eureka Moment” occurred when I read the chapter on NORM. NORM stands for No Object Relational Mapping. The chapter describes how to eliminate the use of ORMs from your application. I have experienced first hand how the use of ORMs has led to extremely poor database design and horrific performance results.

This chapter introduces the concept of using JSON documents for application developers to build contracts with the database, database developers, and or DBAs. Call a PL/pgSQL function and get back a JSON Document. This results in a significantly cleaner code that can be reused across a variety of applications. The authors also say they have used the design pattern in production to achieve up to 50x performance improvements as compared with using an ORM.

This approach makes sense for the following reasons:

1. Postgres is the relational database with the best possible support for JSON. See recent blogs and webinars from EDB on this topic, specificallySimon Riggs Webinar from January 20th.
2. All applications that are going to act as a web service or interact with a web or mobile front need to be able to serialize their objects in JSON objects. Why not just do it with the database as well. Yes, you can do this with Mongo but you lose so much (including performance) when you go that route.
3. Most modern programming languages have native support for JSON serialization of objects and object graphs.
4. ORMs are very application and language dependent. The same PL/pgSQL functions that return a JSON object can be used in many different applications written in many different languages.
5. ORMs are often poorly maintained. Postgres and Programming languages are extremely well maintained and supported.

Conclusion

Postgres had a great 2020. It won DBMS Engines database of the year and is the most loved relational database by developers according to Stack Overflow. With this book and many other things to come in Postgres 14, I believe it will have an even better year in 2021.

Congratulations to Henrietta, Anna, Boris and Apress publishing for this fantastic piece of work.