PostgreSQL: Index Tuning and Performance Optimization

Data is critical to any application, and database performance goes hand-in-hand with it. In this course, you'll get to see some ways to maximize database performance with PostgreSQL, covering indexes, best practices, and more.
Course info
Rating
(43)
Level
Intermediate
Updated
Jul 28, 2016
Duration
3h 6m
Table of contents
Course Overview
PostgreSQL: Introduction
PostgreSQL: Understanding Significance of EXPLAIN Keyword
PostgreSQL: Improving Query Performance with Indexes
PostgreSQL: Index Tuning for Complex Queries
PostgreSQL: Best Practices to Populate Large Database
PostgreSQL: Resources and Summary
Description
Course info
Rating
(43)
Level
Intermediate
Updated
Jul 28, 2016
Duration
3h 6m
Description

PostgreSQL is often called the world's most advanced open source database. In this course, PostgreSQL: Index Tuning and Performance Optimization, you will get a look at index tuning and some performance optimization methods. You'll start with an introduction to tuning and optimization in PostgreSQL and how the EXPLAIN keyword works. Next, you'll see how indexes improve performance and move into some advanced concepts for indexes. Then, you'll get to learn about some best practices for populating large databases and be shown some important resources for PostgreSQL. By the end of this course, you'll be better prepared to tackle PostgreSQL databases challenges with performance in mind.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
Intermediate
1h 59m
Apr 28, 2017
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi everyone. My name is Pinal Dave, Welcome to my course, PostgreSQL: Index Tuning and Performance Optimization. I am a database professional at sqlauthority. com. Data is very important for any application, but Data's performance is even more important. Everybody wants maximum performance for their application. Indexes are considered as a silver bullet for performance. It is true to a certain extent, however, not every index is impactful for queries. There are various logical reasoning are associated with creating an optimal index. Some of the major topics in this course we will cover include understanding query execution plan and its performance, creating optimal indexes for complex queries, and finally, identifying best practices for different workload. By the end of this course, you will know how to create the best index for your query, and what are the best practices you should implement for optimal performance. I hope you will join me on this journey to learn PostgreSQL: Index Tuning and Performance Optimization.

PostgreSQL: Understanding Significance of EXPLAIN Keyword
Hi, this is Pinal Dave, and I welcome you to the module, PostgreSQL: Understanding the Significance of EXPLAIN keyword. EXPLAIN keyword is one of the most important keyword any Postgres user should know. EXPLAIN describes execution plan of any query. Let's see what we are going to cover in this module now. First we will see what EXPLAIN is, and right after that we will understand syntax of EXPLAIN. While we learn syntax of EXPLAIN, we will see there are various different options available for EXPLAIN. We will briefly understand what each of these options do with the help of demonstration. Next, we will spend more time on EXPLAIN ANALYZE option. We can go further deep to understand how each of the queries are executed with the perspective of Postgres. Finally, we will see a few of the use case scenarios. Well, that was agenda of what we are going to cover in this course. Let's understand what is EXPLAIN in the next clip.

PostgreSQL: Improving Query Performance with Indexes
Hi, this is Pinal Dave, and I welcome all of you to module PostgreSQL: Improving Query Performance with Indexes. Indexes are considered as silver bullet for performance. Well, it is true to a certain extent as well. In this module, we are going to see how we can create effective Indexes for queries and maintain them. Let's see our agenda a little bit in detail. First we will understand what is Index, and right after that we will talk about types of Indexes. Next we will talk about B-tree Indexes, and its advantages, as well as disadvantages. It is critical that we understand syntax of Index, before we learn anything else. We will also understand how we can maintain our Indexes in great health in this module as well, and trust me, everything which we will see in this module, we will learn with the help of use case scenarios. And if you attended my earlier courses, you must be waiting for two of our favorite characters, Beth and Troy. We will definitely see them again in this module. Now let's start understanding what is Index in the next clip.

PostgreSQL: Index Tuning for Complex Queries
Hi, this is Pinal Dave, and welcome to the new module of PostgreSQL: Index Tuning for Complex Queries. In previous modules, we have seen how we can take a query and create index on it to improve its performance. However, the queries were straightforward, and index was also very simple. We explored in previous module how to create index and maintain it. In this module, we are going to take a little bit different query and tune it with advanced concept of indexing. Let's see what we are going to see in this module in the agenda next. Here is our agenda. First we will talk about Unique Index. This is very interesting concept, and in the industry I see a lot of confusion for this index. Lots of people have no idea how it is compared with Primary Key Constraint and Unique Constraint. Let me ask you this. What is the difference between Unique Index, Primary Key constraint, and Unique Constraint? Trust me, when we learn about all these three, I am very confident that you will discover something new about all three of these concepts. Next we will talk about Case Insensitive Search, and after that we will talk about Partial Index. The name itself is very interesting. We will not create Complete Index. Instead of that we will create Partial Index. Why would we create partial index? Well, you have to see that later on in this module. And as usual, we will learn all of this concept with the help of real world scenarios. Well, that's it, let's start learning about Unique Index, Primary Key Constraint, and Unique Constraint.

PostgreSQL: Best Practices to Populate Large Database
Hi, this is Pinal Dave, and welcome to PostgreSQL: Best Practices to Populate Large Database. When you have a small database, you do not have to worry about performance. However, when your database grows large, at that time you will have problems with performance. In this module, we will specifically focus on various details related to populating a large amount of data in your table. Let's see our agenda very quickly. First we will talk about how we can optimize multiple INSERT commands with the help of disable Autocommit. Right after that, we will see how we can import or export lots of data with the help of Copy command. We will also explore how we can get our data in CSV format directly from Postgres. Next, we will talk about one of the most popular best practices related to indexes. We will see how removing indexes help performance of INSERT queries. Right after that, we will talk about how to maintain our database tables in optimal condition with the help of ANALYZE command. Of course, we will take the help of real world scenarios to understand each of these best practices. Well, let's start this module with our real world scenario, and we will touch each of the topics in our agenda in the next clip.

PostgreSQL: Resources and Summary
Hi, this is Pinal Dave, and I welcome all of you to this very last module of fifth and final course in this Postgres series. In this course, we focused on index tuning and performance optimization. This is an amazing journey so far. We started this five-course series from installing Postgres, and now we are at the end of this series where we are discussing how to tune queries with indexes. In this final module, we will cover a few very important resources related to Postgres. You will need them to do various exercises in this course, as well as in the future. We will also talk about some of the best practices related to Postgres, and we will also see a very small demonstration in this module as well. Before we end this module, we will also conclude our story line of real world scenario. We will know if Beth and Troy will be deserving a promotion or not. Let us quickly discuss in next clip what we have learned in this course so far.