Improving performance through indexes with Prisma

The bigger our database, the more we need to care about its performance. A common way of improving it is through indexes. Therefore, this article introduces the idea of indexes and implements them through Prisma.

You can find the code from this article in this repository.

Introduction to indexes

In one of the recent articles, we’ve created a posts table.

1model Post {
2  id        Int      @default(autoincrement()) @id
3  title     String
4  content   String
5  author    User  @relation(fields: [authorId], references: [id])
6  authorId  Int
7  categories Category[]
8}

At some point, we might want to allow querying for all posts written by a particular author.

1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../prisma/prisma.service';
3 
4@Injectable()
5export class PostsService {
6  constructor(private readonly prismaService: PrismaService) {}
7 
8  getPostsByAuthor(authorId: number) {
9    return this.prismaService.post.findMany({
10      where: {
11        authorId,
12      },
13    });
14  }
15 
16  // ...
17}

The crucial thing we need to realize is that the above query has to scan the entire posts table to find the matching records. Let’s run a query that helps us to visualize that.

1EXPLAIN ANALYZE
2SELECT * FROM "Post"
3WHERE "authorId" = 1

In the above result, we can see that PostgreSQL performed the sequential scan. If our database is extensive, iterating through it from cover to cover might cause performance issues. To deal with that, we can create an index.

Adding an index

By adding an index, we can organize our table using a particular column. For example, to make the above query faster, let’s add an index on the authorId column by using the @@index keyword.

1model Post {
2  id         Int        @id @default(autoincrement())
3  title      String
4  content    String
5  author     User       @relation(fields: [authorId], references: [id])
6  authorId   Int
7  categories Category[]
8 
9  @@index([authorId])
10}

Now, we need to use the Prisma CLI to generate a migration.

1npx prisma migrate dev --name add-author-index-to-post

Running the above command creates a new file in the migrations directory.

1-- CreateIndex
2CREATE INDEX "Post_authorId_idx" ON "Post"("authorId");

When we add an index, PostgreSQL maintains a data structure organized by a particular column. Let’s imagine the index as key and value pairs.

In our example, the keys are author ids, and the values point to posts.

The actual data structures used by PostgreSQL are more complex. By default, PostgreSQL implements the B-tree data structure where every leaf points to a table row.

Since PostgreSQL now maintains a data structure sorted by the author’s id, it can quickly find all posts written by a particular author. However, indexes have some important downsides.

While indexes can speed up fetching data with the SELECT queries, they make inserts and updates slower. This is because PostgreSQL needs to update the indexes each time we modify our table. Also, indexes take up additional space in our database.

Multi-column indexes

Some of our queries might have multiple conditions. For example, we might want to find a post written by a particular author with a specific title.

1SELECT * FROM "Post"
2WHERE "authorId" = 1 AND title = 'Hello world!'

Creating an index either for the authorId or the title columns would speed up the above query. However, if we want to take it a step further, we can create a multi-column index. To do that, we need to provide two column names for the @@index operator.

1model Post {
2  id         Int        @id @default(autoincrement())
3  title      String
4  content    String
5  author     User       @relation(fields: [authorId], references: [id])
6  authorId   Int
7  categories Category[]
8 
9  @@index([authorId])
10  @@index([authorId, title])
11}

Unique indexes

In this series of articles, we’ve defined a schema for the user.

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String
5  password  String
6  address   Address? @relation(fields: [addressId], references: [id])
7  addressId Int?     @unique
8  posts     Post[]
9}

When doing so, we marked the email column with the @unique keyword. Because of that, each time we insert a new record to the above table, PostgreSQL checks if the new email is unique.

The important thing is that adding a unique constraint causes PostgreSQL to create an index. Because of that, PostgreSQL can quickly search the existing emails to determine if the new value is unique. This index can also benefit the SELECT queries and give them a performance boost.

Types of indexes

So far, our indexes have used the B-tree structure under the hood. It fits most use cases, but we have other options.

Hash indexes

Using the hash table through the hash index might be beneficial for some uses.

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String
5  password  String
6  address   Address? @relation(fields: [addressId], references: [id])
7  addressId Int?     @unique
8  posts     Post[]
9 
10  @@index(fields: [name], type: Hash)
11}

Generalized Inverted Indexes (GIN)

The GIN index can come in handy when the value contains more than one key. An example would be the array data type. They can also be helpful when implementing text searching.

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String
5  password  String
6  address   Address? @relation(fields: [addressId], references: [id])
7  addressId Int?     @unique
8  posts     Post[]
9 
10  @@index(fields: [name], type: Gin)
11}

To make the GIN index work, we might need to enable the bree_gin and pg_trim extensions first.

1CREATE EXTENSION btree_gin;
2CREATE EXTENSION pg_trgm;

Block Range Indexes (BRIN)

The Block Range Indexes might be helpful when dealing with data types with linear sort order.

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String
5  password  String
6  address   Address? @relation(fields: [addressId], references: [id])
7  addressId Int?     @unique
8  posts     Post[]
9 
10  @@index(fields: [name], type: Brin)
11}

Generalized Search Tree (GIST)

The GIST indexes can be useful when indexing geometric data and implementing text search. In some cases, they might be preferable over GIN.

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String
5  password  String
6  address   Address? @relation(fields: [addressId], references: [id])
7  addressId Int?     @unique
8  posts     Post[]
9 
10  @@index(fields: [name], type: Gin)
11}

For the GIST indexes to work, we might need to enable the btree_gist extension.

1CREATE EXTENSION btree_gist;

Summary

This article covered the basics of indexes by implementing examples that improve the performance of various SELECT queries. It also considered both advantages and disadvantages of indexes.

Besides the most basic indexes, we’ve also mentioned indexes that use data structures different than B-tree and multi-column indexes. All of the above serves as an introduction to how to create indexes in Prisma and how indexes work in general.