API with NestJS #126. Improving the database performance with indexes and Kysely


This entry is part 126 of 136 in the API with NestJS

SQL indexes act like guides in our database and help us retrieve the data faster. The bigger our database is, the more emphasis we need to put on its performance. By using indexes, we can help our PostgreSQL database retrieve the data faster.

In this article, we learn what indexes are and how to create them with PostgreSQL, Kysely, and NestJS.

The idea behind indexes

Recently, we’ve added the articles table to our project.


In our repository, we implemented a method to get a list of articles written by an author with a particular id.


Above, we use pagination. If you want to learn more about it, check out API with NestJS #125. Offset and keyset pagination with Kysely

In the method, we use . PostgreSQL needs to scan the entire table to find the matching records. Let’s visualize that using the query.

In the query plan above, we can see that PostgreSQL does the sequential scan. While performing the sequential scan, the database reads all rows in the table one by one to find the data that matches the criteria. Sequential scans can be slow and resource-intensive, especially on large data sets. We can improve this situation by adding an index.

Introducing indexes

The SQL index acts similarly to a book’s index and helps the database find the information quickly. Let’s add an index on the column to make the above query faster faster.


Let’s run the above migration and try to analyze our SELECT query.


PostgreSQL considers quite a few factors when deciding whether to use an index. If it does not work for you, check out the command.

The moment we create an index, PostgreSQL starts maintaining a data structure organized around a particular column. We can think of it as key and value pairs.


Under the hood, PostgreSQL uses a B-tree data structure where each leaf points to a particular row.

Now, PostgreSQL can quickly find all articles written by a particular author thanks to having a structure sorted by the author’s id. Unfortunately, indexes have some disadvantages, too.

Having to maintain an additional data structure takes extra space in our database. While it speeds up the queries that fetch data, maintaining indexes includes additional work for the database when inserting, updating, or deleting records from our database. Therefore, we must think our indexes through to avoid hurting the overall performance due to increased overhead.

Unique index

When working with Kysely, we’ve created the table with the column.


By marking the column as unique, we tell PostgreSQL to look for email duplicates every time we insert or modify records in the table.

Since going through all elements in the table might be time-consuming, PostgreSQL creates indexes whenever we create a unique constraint. We can verify that with a simple SQL query.


Defining a primary key also creates a unique index. Because of that, the above screenshot also contains the index.

Thanks to that, the database can quickly search the existing users to determine if a particular email is unique. This can also benefit various queries and give them a performance boost.

Multi-column indexes

We can create queries that include multiple conditions. A good example is finding an article written by a particular user and containing a specific title.

If we create an index on the or the columns, we would speed up the above query. However, we can move it up a notch and create a multi-column index.

By creating a multi-column index, we can improve the performance of queries that use a specific combination of column values.

Index types

All of the indexes we mentioned so far used the B-tree structure. While it works fine for most cases, we also have other options.

Generalized Inverted Indexes (GIN)

The GIN indexes can help us when querying complex data types such as arrays or JSON. It might also come in handy when implementing text searching.

To ensure that the GIN index is available in our database, we might need to enable the and extensions.

Hash indexes

The hash SQL index uses hashes to locate specific values quickly. It might be a good fit for some use cases.

Block Range Indexes (BRIN)

The Block Range Indexes (BRIN) are designed to handle very large tables.

Generalized Search Tree (GIST)

The GIST indexes can be helpful for indexing geometric data and implementing text search. They might be preferable over GIN indexes in some cases.

For them to work, we might need to enable the extension.


In this article, we’ve covered the basic principles behind indexes and implemented examples that improved the performance of our queries. We also considered the disadvantages that come with an incorrect use of indexes.

The default type of index in PostgreSQL is a B-tree index. It works well for a wide range of queries and is the most commonly used index type. While that’s the case, we also mentioned other types of indexes, such as GIN and hash indexes.

Thanks to the above, we now know how to handle indexes when working with Kysely.

Series Navigation<< API with NestJS #125. Offset and keyset pagination with KyselyAPI with NestJS #127. Arrays with PostgreSQL and Kysely >>
Notify of
Inline Feedbacks
View all comments