Book Image

Mastering PostgreSQL 9.6

By : Hans-Jürgen Schönig
Book Image

Mastering PostgreSQL 9.6

By: Hans-Jürgen Schönig

Overview of this book

PostgreSQL is an open source database used for handling large datasets (Big Data) and as a JSON document database. It also has applications in the software and web domains. This book will enable you to build better PostgreSQL applications and administer databases more efficiently. We begin by explaining the advanced database design concepts in PostgreSQL 9.6, along with indexing and query optimization. You will also see how to work with event triggers and perform concurrent transactions and table partitioning, along with exploring SQL and server tuning. We will walk you through implementing advanced administrative tasks such as server maintenance and monitoring, replication, recovery and high availability, and much more. You will understand the common and not-so-common troubleshooting problems and how you can overcome them. By the end of this book, you will have an expert-level command of the advanced database functionalities and will be able to implement advanced administrative tasks with PostgreSQL.
Table of Contents (14 chapters)
Free Chapter
PostgreSQL Overview

Making Use of Indexes

In the previous chapter, you learned about concurrency and locking. In this chapter, it is time to attack indexing head on. The importance of this topic cannot be stressed enough—indexing is (and will most likely stay as) one of the most important topics in the life of every database engineer.

After 17 years of professional, full-time PostgreSQL consulting and PostgreSQL 24x7 support, I can say one thing for sure. Bad indexing is the main source of bad performance. Of course, it is important to adjust memory parameters and all that. However, it is all in vain if indexes are not used properly. There is simply no replacement for a missing index.

Therefore, I have dedicated an entire chapter to indexing alone to give you as many insights as possible.

In this chapter, you will learn these topics:

  • When does PostgreSQL use indexes?
  • How does the optimizer handle things?
  • What types of indexes...