API with NestJS #82. Introduction to indexes with raw SQL queries

JavaScript NestJS SQL

This entry is part 82 of 172 in the API with NestJS

As our database grows, we need to put more and more emphasis on performance. A popular approach to dealing with this problem is by implementing indexes. In this article, we look into doing that with raw SQL in a project that does not use ORM.

Introducing indexes

So far, in this series, we’ve created a table for storing posts.

We’ve also implemented getting a list of posts written by a particular author.

posts.repository.ts

The above method also implements pagination. If you want to know more, check out API with NestJS #77. Offset and keyset pagination with raw SQL queries

In our SQL query, we add . Because of that, PostgreSQL needs to scan the entire table to find matching records. The bigger the table, the more it affects the performance. Let’s run a simple query and see how fast it is

The crucial part of the above query plan is that PostgreSQL uses the parallel sequential scan that takes quite a lot of time. We can deal with this problem by adding an index.

Adding an index

An index can make some of our queries faster by organizing a table using a particular column. Let’s create a migration that adds an index using the column.

20221105181728_add_post_author_id_index.ts

After running the command, our database is ready to go. Let’s rerun the same query and compare.

As we can see above, the execution time of our query dropped drastically. Now, PostgreSQL can run an index scan instead of a parallel sequential one.

When we create an index, PostgreSQL maintains a data structure organized using a certain column. We can imagine the index as key and value pairs. In the case of the above example, the keys are author ids, and the values point to particular posts.

author_idpost_id
11
22
23
24
35

To be more precise, the actual data structures PostgreSQL uses for indexing are more intricate. By default, PostgreSQL implements the B-tree data structure with each leaf poiting to a particular table row.

Thanks to having a data structure sorted by the author id, PostgreSQL can quickly find all posts written by a particular author. However, besides the obvious advantages when fetching data, indexes have some important downsides.

Indexes can speed up our queries but make our inserts and updates slower. This is because PostgreSQL needs to update the indexes each time we modify the data. Indexes also require additional space in our database.

Multi-column indexes

Making queries with multiple conditions is a popular case. Let’s take a look at our table:

A common query would be selecting comments that are not deleted and are related to a particular post.

Creating an index just on the would speed up the above query. However, if we want to take it even further, we could create a multi-column index.

Unique indexes

In one of the previous articles in this series, we’ve defined a table of users.

We’ve decided to mark the column as . Because of that, every time we insert a new row into the table, PostgreSQL checks if the given email is already in the table.

The important thing is that creating a unique constraint causes PostgreSQL to create an index. Thanks to that, the database can quickly search through all existing records to determine if a given value is unique.

The index created through the keyword can also be helpful in the queries if the database decides it might cause a performance boost.

Types of indexes

All of the above indexes so far have used the B-tree structure. This is because it fits most use cases, but there are other options too.

Hash indexes

The hash indexes use the hash table under the hood, which might prove to be beneficial in some use cases.

Generalized Inverted Indexes (GIN)

For the GIN indexes to work, we might need to enable the and extensions.

Once we do the above, we can use the keyword to create the index.

Generalized Search Tree (GIST)

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

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

We can now use the keyword.

Block Range Indexes (BRIN)

When dealing with data types with a linear sort order, the Block Range Indexes might prove to be useful.

Summary

In this article, we’ve gone through the basics of indexes and considered their advantages and disadvantages. Then, we implemented an example that improves the performance of our queries.

Besides the regular indexes, we’ve also mentioned multi-column indexes and indexes using data structures different from the B-tree. Some of them are worth a separate article, so stay tuned!

Series Navigation<< API with NestJS #81. Soft deletes with raw SQL queriesAPI with NestJS #83. Text search with tsvector and raw SQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments