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. 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-postRunning 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. authorIdpostId 11 22 23 34 35 36 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. The post API with NestJS #106. Improving performance through indexes with Prisma appeared first on Marcin Wanago Blog - JavaScript, both frontend and backend.