3 Strategies to Improve PostgreSQL Performance: Indexes

By: Cameron Jones

Update: Google Stackdriver is now Google Cloud Logging and Google Cloud Monitoring. BindPlane will continue to integrate and support both of these products.

Last week, I kicked off our PostgreSQL performance blog series with a focus on replication. Replication helps ensure high availability and with the right configuration, can ensure optimal performance of your PostgreSQL workloads. Indexes, on the other hand, focus on bits of data — but can have just as big of an impact on your PostgreSQL performance.

What are indexes?
There are many different type of indexes that are supported by PostgreSQL. In short, a database index enables you to quickly search for crucial information by allowing you to view data in a few columns of your choice.

Some of the most popular PostgreSQL index types include:

• B-Tree: When you create an index, the B-Tree serves as the default. The B stands for balanced, meaning that the tree-shape of the index focuses on having the same amount of data on both sides.
• Generalized Inverted Indexes (GIN): The generalized inverted indexes, or GIN, work best for array values, as it organizes many values to one row. Because of this, it is not suitable for all types of data.
• Generalized Search Tree (GiST): On the other hand, the generalized search tree or GiST, focuses on indexing via a balanced tree structure, much like the B-Tree. As a result, you can leverage this type of index for geometric data types.
• Partial Indexes: With a partial index, you can reduce the size of your index — it has a “where” clause so that it only covers a small amount of data in your table. As a result, you can scan it faster and improve overall maintenance.
• Expression Indexes: When your queries match a modification or function of your data, expression indexes are the way to go. These types of indexes allow to search your data as if they are raw data values. For instance, if you want to find data based on date, you can leverage an expression index to mine through the datetime field.
• Unique Indexes: These type of indexes are useful when you are focused on driving performance and data integrity. With a unique index, you ensure that your table has unique values for each row. It won’t capture rows with the same values. As a result, you can look up your data very quickly.

Using Indexes in PostgreSQL
Because PostgreSQL indexes do not hold all of your row data, they do require some maintenance to ensure that they are working appropriately for your needs. Indexes can also be short-lived; for instance, you can remove an index once you’ve found the data that you need — e.g., email addresses for a group of people who registered on your site between a set of dates.

Before you even get to the point of setting up indexes, think about whether they make sense — you need the index to be specific and still have enough data to search through in order for it to pay off. Otherwise, you can leverage a search without an index.

Some tips for using indexes in PostgreSQL:

• Create a database as close to what will exist in production as possible. If you have too small of a dataset, it doesn’t make sense to run it because of the resources that it consumes (and, your database may not even allow it to run). By ensuring it’s close to your production database, you can also see how the index operates in an environment as close to yours as possible.
• Remember that an index will lock future writes. You can add “create index concurrently” that will not take your system down from writes, but if you don’t use this, you may take your site down for a lengthy period of time.
• Indexes require ongoing maintenance. Because your data shifts and changes, your indexes will become inaccurate and unusable. Luckily, you can use “reindex” to keep them operating at peak efficiency — but again, remember that as you do this, it will impact your writes.

Using a query to maintain your indexes
To maintain your indexes, I like this suggestion from the PostgreSQL Wiki to set up a query to monitor how your index is performing.

From there, use a monitoring data integration platform like BindPlane to collect data on query performance — including the one tracking your indexes. Garner insight into how the query performs over time so you can immediately pinpoint when your index may not be performing as designed.

Interested in monitoring your index performance via query? Try a free trial of BindPlane.