Version: v2.x

Postgres indexes

Introduction

Postgres indexes are a way of increasing query performance based on columns that are queried frequently. The concept is similar to the one of an index in a book. It helps accessing the data you're looking for more quickly by maintaining additional metadata.

Note

Learn more about indexes in the Postgres documentation.

Example

Create an index on the column name in the table authors:

Let's say the database receives a large number of requests of authors being queried by their name, for example:

SELECT * FROM authors WHERE name = 'J.K. Rowling';

We can now create an index on the name column of the authors table:

CREATE INDEX author_name_index ON authors (name);

Since the database is now able to look up the result of these queries more quickly, the performance of these queries will increase significantly.