SE Radio 583: Lukas Fittl on Postgres Performance

Index Types

Lukas Fittl explains the various index types available in Postgres, emphasizing the default use of B-tree indexes. He notes that while B-tree is optimal for most cases, other types like GIN and GiST are better suited for specific data types and operations, such as JSONB and geospatial data 1. Choosing the right index is crucial for performance, and Fittl stresses that this responsibility lies with application developers rather than DBAs or DevOps teams 2.

A B-tree index is essentially a cached data structure that optimizes that lookup so that you don't have to manually go through each row.

--- Lukas Fittl

Understanding the specific queries and their operators is key to selecting the appropriate index type.

   

Index Selection

Fittl discusses the complexities of index selection, highlighting the lack of built-in recommendations in Postgres compared to SQL Server. He introduces the HypoPG extension, which helps simulate the impact of hypothetical indexes on query costs 3. Additionally, he addresses common query performance issues, such as inefficient pagination using limit and offset, suggesting keyset-based pagination as a more efficient alternative 4.

Sometimes it actually makes sense to rewrite the query so that you are matching your existing indexes.

--- Lukas Fittl

These insights underscore the importance of aligning query structures with existing indexes for optimal performance.

   

Expression Indexes

Expression indexes in Postgres allow developers to index specific query expressions, such as functions applied to columns, which can significantly enhance query performance 5. Fittl shares an example where proper indexing led to a 414x performance improvement, transforming a sluggish query into an interactive experience 6.

The database will not look at these where clauses and say, oh, they're always querying for this set of where clauses. I'll create index.

--- Lukas Fittl

This highlights the critical role of developers in understanding and implementing effective indexing strategies to ensure efficient database operations.