/PostgreSQL Indexes

PostgreSQL Indexes

Summary: in this tutorial, you will learn how to use PostgreSQL indexes to enhance the data retrieval speed and various index types.

Introduction to PostgreSQL indexes

In PostgreSQL, anindex is a data structure that speeds up data retrieval. It gives PostgreSQL a quick way to locate rows inside a table without scanning the entire table.

You can think of an index like theindex pages of a book.Instead of reading every page to find a topic, you simply check the index, which points you directly to the page you need. PostgreSQL uses indexes in the same way to find data quickly.

Suppose you have a table calledcontacts with the following structure:

CREATE TABLE contacts (    idINT PRIMARY KEY,    name VARCHAR(255)NOT NULL,    phoneVARCHAR(10)NOT NULL);

And you issue the following query to find a contact by name:

SELECT * FROM contactsWHERE name = 'John Doe';

Without an index, PostgreSQL must scan the entirecontacts table to find"John Doe".If the table contains many rows, this becomes slow — just like reading a whole book to find a single line.

However, with an index on thename column, PostgreSQL can locate the matching rows much faster.

To create an index, you use theCREATE INDEX statement:

CREATE INDEX contacts_nameON contacts(name);

This creates an index namedcontacts_name on thename column of thecontacts table.

After creating the index, PostgreSQL extracts all values from thename column and stores them in the index data structure.This process may take some time if the table contains many rows — similar to how building a book’s index takes longer for a thicker book.

By default, PostgreSQL allowsSELECT operations while creating the index, but it blocksINSERT,UPDATE, andDELETEoperations during that time for safety.

As an alternative, if you cannot block INSERT, UPDATE, or DELETE operations during index creation, you can use the syntaxCREATE INDEX CONCURRENTLY to create the index without blocking, but at a much slower rate.

When you execute the followingSELECT statement, PostgreSQL can use thecontacts_name index to quickly find the matching rows:

SELECT * FROM contactsWHERE name = 'John Doe';

After the index is created, PostgreSQL must keep it synchronized with the table.

For example, when inserting, updating, or deleting rows in thecontacts table, PostgreSQL also updates the index so that it remains accurate.

Because of this, indexesimprove read speed but add a bit of overhead to write operations (inserts, updates, deletes) — similar to how updating a book’s content also requires updating its index.

Types of PostgreSQL indexes

PostgreSQL offers various index types, each designed to cater to specific data scenarios and query patterns.

By understanding these index types, you can enhance the query performance more effectively.

B-tree index

B-tree is the default index type in PostgreSQL. B-tree stands for balanced tree. B-tree indexes maintain the sorted values, making them efficient for exact matches and range queries.

Hash index

Hash indexes maintain 32-bit hash code created from values of the indexed columns.

Therefore, hash indexes can only handle simple equality comparisons (=).

GIN index

GIN indexes are inverted indexes that are suitable for composite values such asarrays,JSONB data, andfull-text search.

Since a GIN index stores a separate entry for each component, it can handle queries that check for the existence of a specific component.

GiST index

GiST indexes are versatile and support a wide range ofdata types, including geometric and full-text data.

GiST indexes allow various search strategies such as nearest-neighbor and partial match searches, making them useful for specialized applications.

SP-GiST index

SP-GiST indexes are useful for indexing data with hierarchical structures or complex data types.

SP-GiST indexes partition the index space into non-overlapping regions, offering efficient search capabilities for specialized data structures.

BRIN (Block Range Index) index

BRIN indexes are designed for very large tables where indexing every row is impractical.

A BRIN index divides the table into ranges of pages and stores summarized information about each range, making them efficient for range queries on large datasets while using minimal space.