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.
1import { Generated } from 'kysely';
2
3export interface ArticlesTable {
4 id: Generated<number>;
5 title: string;
6 article_content: string;
7 author_id: number;
8}In our repository, we implemented a method to get a list of articles written by an author with a particular id.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import { Injectable } from '@nestjs/common';
4
5@Injectable()
6export class ArticlesRepository {
7 constructor(private readonly database: Database) {}
8
9 async getByAuthorId(
10 authorId: number,
11 offset: number,
12 limit: number | null,
13 idsToSkip: number,
14 ) {
15 const { data, count } = await this.database
16 .transaction()
17 .execute(async (transaction) => {
18 let articlesQuery = transaction
19 .selectFrom('articles')
20 .where('author_id', '=', authorId)
21 .where('id', '>', idsToSkip)
22 .orderBy('id')
23 .offset(offset)
24 .selectAll();
25
26 if (limit !== null) {
27 articlesQuery = articlesQuery.limit(limit);
28 }
29
30 const articlesResponse = await articlesQuery.execute();
31
32 const { count } = await transaction
33 .selectFrom('articles')
34 .where('author_id', '=', authorId)
35 .select((expressionBuilder) => {
36 return expressionBuilder.fn.countAll().as('count');
37 })
38 .executeTakeFirstOrThrow();
39
40 return {
41 data: articlesResponse,
42 count,
43 };
44 });
45
46 const items = data.map((articleData) => new Article(articleData));
47
48 return {
49 items,
50 count,
51 };
52 }
53
54 // ...
55}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 getByAuthorId method, we use where('author_id', '=', authorId). PostgreSQL needs to scan the entire articles table to find the matching records. Let’s visualize that using the EXPLAIN ANALYZE query.
1EXPLAIN ANALYZE
2SELECT * FROM articles
3WHERE author_id = 1In 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 author_id column to make the above query faster faster.
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createIndex('article_author_id')
6 .on('articles')
7 .column('author_id')
8 .execute();
9}
10
11export async function down(database: Kysely<unknown>): Promise<void> {
12 await database.schema.dropIndex('article_author_id').execute();
13}Let’s run the above migration and try to analyze our SELECT query.
1EXPLAIN ANALYZE
2SELECT * FROM articles
3WHERE author_id = 1PostgreSQL considers quite a few factors when deciding whether to use an index. If it does not work for you, check out the VACUUM 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 users table with the email column.
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createTable('users')
6 .addColumn('id', 'serial', (column) => {
7 return column.primaryKey();
8 })
9 .addColumn('email', 'text', (column) => {
10 return column.notNull().unique();
11 })
12 .addColumn('name', 'text', (column) => {
13 return column.notNull();
14 })
15 .addColumn('password', 'text', (column) => {
16 return column.notNull();
17 })
18 .execute();
19}
20
21export async function down(database: Kysely<unknown>): Promise<void> {
22 await database.schema.dropTable('users').execute();
23}By marking the email column as unique, we tell PostgreSQL to look for email duplicates every time we insert or modify records in the articles 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.
1SELECT tablename, indexname
2FROM pg_indexes
3WHERE tablename='articles';Defining a primary key also creates a unique index. Because of that, the above screenshot also contains the articles_pkey 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 SELECT queries and give them a performance boost.
1EXPLAIN ANALYZE
2SELECT * FROM users
3WHERE email = 'john@smith.com'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.
1const articles = await this.database
2 .selectFrom('articles')
3 .where('author_id', '=', 1)
4 .where('title', '=', 'Hello world!')
5 .selectAll()
6 .execute();If we create an index on the author_id or the title columns, we would speed up the above query. However, we can move it up a notch and create a multi-column index.
1export async function up(database: Kysely<unknown>): Promise<void> {
2 await database.schema
3 .createIndex('article_author_id_title')
4 .on('articles')
5 .columns(['author_id', 'title'])
6 .execute();
7}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.
1await database.schema
2 .createIndex('article_title')
3 .on('articles')
4 .using('gin')
5 .column('title')
6 .execute();To ensure that the GIN index is available in our database, we might need to enable the btree_gin and pg_trim extensions.
1CREATE EXTENSION btree_gin;
2CREATE EXTENSION pg_trgm;Hash indexes
The hash SQL index uses hashes to locate specific values quickly. It might be a good fit for some use cases.
1await database.schema
2 .createIndex('article_title')
3 .on('articles')
4 .using('hash')
5 .column('title')
6 .execute();Block Range Indexes (BRIN)
The Block Range Indexes (BRIN) are designed to handle very large tables.
1await database.schema
2 .createIndex('article_title')
3 .on('articles')
4 .using('brin')
5 .column('title')
6 .execute();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.
1await database.schema
2 .createIndex('article_title')
3 .on('articles')
4 .using('gist')
5 .column('title')
6 .execute();For them to work, we might need to enable the btree_gist extension.
1CREATE EXTENSION btree_gist;Summary
In this article, we’ve covered the basic principles behind indexes and implemented examples that improved the performance of our SELECT 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.