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:
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:
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.