Power of Indexing in PostgreSQL [Webinar Follow-up]

Given the massive size, complexity, and performance requirements of most databases, having an optimized database is critical for many organizations. A variety of different strategies are used for database performance enhancement – indexing being one of them.

Since indexing is such a key component to database performance, 2ndQuadrant chose to highlight the power of indexing in the third instalment of their PostgreSQL webinar series.

Power of Indexing in PostgreSQL was conducted by Pavan Deolasee, PostgreSQL contributor and developer at 2ndQuadrant. He covered various topics such as accessing data in PostgreSQL and types of indexes supported as well as their internals.

If you weren’t able to make it to the live session, you can now view the recording here.

Questions that Pavan couldn’t respond to during the live webinar have been answered below.

Q1: How do I choose between a multi-column index and multiple indexes on individual columns?

A: Multi-column indexes are good when you have queries which search for a range on the first column OR equality on the first column and range on the second column and so on. Where as multiple indexes are better when you run range or equality queries on a set of columns (e.g. when the first column is not always involved in the query)

Q2: Can you please help me understand how auto vacuuming/Vacuum affects indexes?

A: Autovacuum or vacuum collect dead heap tuples and remove the corresponding index pointers. Note that during normal DELETE or UPDATE, we don’t actively delete index pointers and so vacuum takes care of that (though there is a provision to “kill” dead index pointers during regular scan, we can’t rely on it completely)

Q3: If I want to clear bloat on Index, is it good practise to create concurrent index then drop the bloated index?

A: Yes, that’s good practice. One must keep in mind, though, that some free space in the index is not bad; especially if the table receives INSERTs/UPDATEs.

For any questions, comments, or feedback, please visit our website or send an email to [email protected].