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

NestJS SQL

This entry is part 106 of 158 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

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

posts.service.ts

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

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 column by using the keyword.

postSchema.prisma

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

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

migrations/20230428222734_add_author_index_to_post/migration.sql

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

Creating an index either for the or the 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 operator.

postSchema.prisma

Unique indexes

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

userSchema.prisma

When doing so, we marked the column with the 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 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

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

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

Block Range Indexes (BRIN)

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

userSchema.prisma

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

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

Summary

This article covered the basics of indexes by implementing examples that improve the performance of various 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 >>
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jean
Jean
1 year ago

Hi, nice article! Can you also add the result after adding the authorId index? How fast is it after?

Thai Tran
Thai Tran
1 year ago

Your tutorial was excellent and has been tremendously helpful to me. I express my gratitude and appreciation for your assistance.