API with NestJS #106. Improving performance through indexes with Prisma

May 1, 2023

This entry is part 106 of 180 in the API with NestJS

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.

postSchema.prisma

model Post {

id Int @default(autoincrement()) @id

title String

content String

author User @relation(fields: [authorId], references: [id])

authorId Int

categories Category[]

}

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

posts.service.ts

import { Injectable } from '@nestjs/common';

import { PrismaService } from '../prisma/prisma.service';

@Injectable()

export class PostsService {

constructor(private readonly prismaService: PrismaService) {}

getPostsByAuthor(authorId: number) {

return this.prismaService.post.findMany({

where: {

authorId,

});

}

// ...

}

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.

EXPLAIN ANALYZE

SELECT * FROM "Post"

WHERE "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.

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

title String

content String

author User @relation(fields: [authorId], references: [id])

authorId Int

categories Category[]

@@index([authorId])

}

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

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

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

migrations/20230428222734_add_author_index_to_post/migration.sql

1 2	-- CreateIndex CREATE 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.

authorId	postId
1	1
2	2
2	3
3	4
3	5
3	6

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.

1 2	SELECT * FROM "Post" WHERE "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.

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

title String

content String

author User @relation(fields: [authorId], references: [id])

authorId Int

categories Category[]

@@index([authorId])

@@index([authorId, title])

}

Unique indexes

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

userSchema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

address Address? @relation(fields: [addressId], references: [id])

addressId Int? @unique

posts Post[]

}

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.

userSchema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

address Address? @relation(fields: [addressId], references: [id])

addressId Int? @unique

posts Post[]

@@index(fields: [name], type: Hash)

}

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.

userSchema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

address Address? @relation(fields: [addressId], references: [id])

addressId Int? @unique

posts Post[]

@@index(fields: [name], type: Gin)

}

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

1 2	CREATE EXTENSION btree_gin; CREATE EXTENSION pg_trgm;

Block Range Indexes (BRIN)

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

userSchema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

address Address? @relation(fields: [addressId], references: [id])

addressId Int? @unique

posts Post[]

@@index(fields: [name], type: Brin)

}

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.

userSchema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

address Address? @relation(fields: [addressId], references: [id])

addressId Int? @unique

posts Post[]

@@index(fields: [name], type: Gin)

}

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

1	CREATE 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.

Series Navigation<< API with NestJS #105. Implementing soft deletes with Prisma and middlewareAPI with NestJS #107. Offset and keyset pagination with Prisma >>