Back to Tutorials
6 min read

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…

May 1, 2023

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.

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#
-- 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.

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.

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.

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.

Improving performance through indexes with Prisma | NestJS.io