Database Indexing - maps for faster searching

Cartographers! Consul Caesar.js has noticed that searching for tributes in our logs takes longer and longer: with twenty thousand entries the database reads the whole table to find a single one. Time to build indexes - special maps that let us find any tribute in an instant.

What are indexes in the legionaries' world?

Imagine thousands of tribute maps in a big chest. Without any order:

• every search means going through all the maps one by one,
• finding a tribute can take hours,
• the more maps, the longer the search.

An index works like a library catalogue. It has a price, though: it speeds up reads but slows down writes, because on every INSERT and UPDATE the database also updates the catalogue, which takes up disk space. So you index the columns you actually search by.

Basic indexes in TypeORM

In TypeORM indexes are declared with the @Index() decorator: on a field it creates a single-column index, on the class a composite index of several columns. Here is an entity for PostgreSQL:

1// tribute.entity.ts
2import { Entity, Column, Index, PrimaryGeneratedColumn } from 'typeorm';
3import type { Point } from 'typeorm';
4
5@Entity('tributes')
6@Index(['location', 'collectedAt']) // Composite index
7@Index(['value'], { where: 'value > 1000' }) // Partial index
8export class Tribute {
9  @PrimaryGeneratedColumn()
10  id: number;
11
12  @Column()
13  @Index() // Simple index on name
14  name: string;
15
16  @Column('decimal')
17  @Index('idx_tribute_value') // Named index
18  value: number;
19
20  @Column()
21  @Index() // Index on location for filtering by province
22  location: string;
23
24  @Column({ type: 'enum', enum: ['GOLD', 'SILVER', 'GEMS'] })
25  @Index() // Index on type for filtering
26  type: string;
27
28  @Column({ name: 'collected_at', type: 'timestamp' })
29  @Index() // Index on the timestamp for date ranges
30  collectedAt: Date;
31
32  @Column({ unique: true })
33  serialNumber: string; // Automatic unique index
34
35  @Column('text', { nullable: true })
36  description: string;
37
38  @Column({ type: 'geography', spatialFeatureType: 'Point', srid: 4326 })
39  @Index({ spatial: true }) // GiST index for geographic queries (PostGIS)
40  coordinates: Point;
41}

A partial index with the where option covers only expensive tributes, so it is smaller. unique: true creates a uniqueness constraint backed by an index. The collected_at column has an explicit name so that raw SQL and the entity talk about the same thing. Point is a GeoJSON type from TypeORM, and geography requires the PostGIS extension. Note that the PostgreSQL driver returns decimal as text so as not to lose precision.

Indexes in other databases

The first version of this entity also had a fulltext index, but TypeORM supports it only in MySQL. There it looks like this:

1// MySQL variant - a full-text index
2@Column('text')
3@Index({ fulltext: true })
4description: string;

In PostgreSQL full-text search works differently - you will see it below. In MongoDB with Mongoose, as in the editor next to the lesson, you describe the index in the schema:

1// soldier.schema.ts - MongoDB (Mongoose)
2@Schema()
3export class Soldier {
4  @Prop({ index: true })
5  rank: string;
6
7  @Prop({ unique: true })
8  registrationNumber: string;
9}
10
11export const SoldierSchema = SchemaFactory.createForClass(Soldier);
12SoldierSchema.index({ legionId: 1, enrolledAt: -1 }); // compound index

The idea does not change: a single field, uniqueness and a compound index, only the notation differs.

The execution plan

Whether the database uses an index is revealed by EXPLAIN, which shows the query plan. EXPLAIN ANALYZE also runs the query and reports real timings:

1// indexing-strategy.service.ts
2@Injectable()
3export class IndexingStrategyService {
4  constructor(
5    @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
6    @InjectDataSource() private dataSource: DataSource,
7  ) {}
8
9  // Checking index usage
10  async analyzeIndexUsage(): Promise<any> {
11    const queries = [
12      // Check the query execution plan
13      `EXPLAIN SELECT * FROM tributes WHERE type = 'GOLD' AND value > 1000`,
14      `EXPLAIN SELECT * FROM tributes WHERE location LIKE 'Hispania%'`,
15      `EXPLAIN ANALYZE SELECT * FROM tributes WHERE collected_at BETWEEN '2023-01-01' AND '2023-12-31'`,
16    ];
17
18    const results = [];
19    for (const query of queries) {
20      const plan = await this.dataSource.query(query);
21      results.push({ query, plan });
22    }
23
24    return results;
25  }

On 20,000 rows the first query got a Bitmap Index Scan, but the second a Seq Scan: with a locale other than C a B-tree index serves LIKE 'Hispania%' only with the text_pattern_ops operator class. The third also read the whole table, because a full year was half of the data, and then scanning everything is cheaper.

Indexes created deliberately

Indexes for specific queries can be created by hand:

1  // Creating indexes dynamically
2  async createPerformanceIndexes(): Promise<void> {
3    const queryRunner = this.dataSource.createQueryRunner();
4
5    try {
6      // Index for frequent searches by value and type
7      await queryRunner.query(`
8        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_type_value
9        ON tributes(type, value DESC)
10      `);
11
12      // Partial index only for expensive tributes
13      await queryRunner.query(`
14        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_expensive_tributes
15        ON tributes(collected_at)
16        WHERE value > 10000
17      `);
18
19      // Composite index for queries by province and time
20      await queryRunner.query(`
21        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_location_time
22        ON tributes(location, collected_at)
23      `);
24
25      // Covering index (contains all the needed columns)
26      await queryRunner.query(`
27        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_summary
28        ON tributes(type, location)
29        INCLUDE (name, value, collected_at)
30      `);
31
32      console.log('Performance indexes created!');
33    } catch (error) {
34      console.error('Error while creating indexes:', error);
35    } finally {
36      await queryRunner.release();
37    }
38  }

A plain CREATE INDEX blocks writes until the build finishes; CONCURRENTLY does not block them, but it does not work inside a transaction. INCLUDE adds columns so that the query can read everything from the index alone. In production keep such commands in a TypeORM migration with transaction = false (it requires migrationsTransactionMode: 'each'), not in a service.

Queries that hit the index

An index only helps a query that matches its columns:

1  // Optimizing queries that use indexes
2  async getOptimizedTributeSearches() {
3    // Uses the (type, value) index and fetches only the needed columns
4    const goldTributes = await this.tributeRepo
5      .createQueryBuilder('tribute')
6      .select(['tribute.id', 'tribute.name', 'tribute.value'])
7      .where('tribute.type = :type AND tribute.value > :minValue', {
8        type: 'GOLD',
9        minValue: 1000
10      })
11      .orderBy('tribute.value', 'DESC')
12      .limit(100)
13      .getMany();
14
15    // Uses the partial index on expensive tributes
16    const expensiveTributes = await this.tributeRepo
17      .createQueryBuilder('tribute')
18      .where('tribute.value > 10000')
19      .andWhere('tribute.collectedAt > :date', {
20        date: new Date('2023-01-01')
21      })
22      .getMany();
23
24    // Uses the spatial index: tributes within 1000 m of the Forum Romanum
25    const nearbyTributes = await this.dataSource.query(`
26      SELECT * FROM tributes
27      WHERE ST_DWithin(
28        coordinates,
29        ST_GeogFromText('SRID=4326;POINT(12.4853 41.8925)'),
30        1000
31      )
32    `);
33
34    return { goldTributes, expensiveTributes, nearbyTributes };
35  }
36}

select() in the QueryBuilder takes alias-qualified columns (tribute.name) and replaces SELECT *. The number 10000 is written literally because the PostgreSQL documentation warns that a condition with a parameter will not match a partial index; in the test the plan used idx_expensive_tributes. For the geography type the distance in ST_DWithin is given in metres.

A simple process keeps index work in order:

1. find slow queries in the slow query log (in PostgreSQL: log_min_duration_statement),
2. analyse the plan with EXPLAIN,
3. add an index or rewrite the query,
4. measure the improvement.

Full-text search for tribute descriptions

PostgreSQL stores searchable text in the tsvector type, and a GIN index finds words in it:

1// full-text-search.service.ts
2@Injectable()
3export class FullTextSearchService {
4  constructor(
5    @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
6    @InjectDataSource() private dataSource: DataSource,
7  ) {}
8
9  async setupFullTextSearch(): Promise<void> {
10    const queryRunner = this.dataSource.createQueryRunner();
11
12    try {
13      // PostgreSQL full-text search
14      await queryRunner.query(`
15        ALTER TABLE tributes
16        ADD COLUMN IF NOT EXISTS search_vector tsvector
17      `);
18
19      await queryRunner.query(`
20        UPDATE tributes
21        SET search_vector = to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
22      `);
23
24      await queryRunner.query(`
25        CREATE INDEX IF NOT EXISTS idx_tribute_search
26        ON tributes USING gin(search_vector)
27      `);
28
29      // Trigger for automatic updates
30      await queryRunner.query(`
31        CREATE OR REPLACE FUNCTION update_tribute_search_vector()
32        RETURNS TRIGGER AS $$
33        BEGIN
34          NEW.search_vector := to_tsvector('english', coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, ''));
35          RETURN NEW;
36        END;
37        $$ LANGUAGE plpgsql;
38      `);
39
40      await queryRunner.query(`
41        DROP TRIGGER IF EXISTS tribute_search_vector_update ON tributes;
42        CREATE TRIGGER tribute_search_vector_update
43        BEFORE INSERT OR UPDATE ON tributes
44        FOR EACH ROW EXECUTE FUNCTION update_tribute_search_vector();
45      `);
46
47      console.log('Full-text search configured!');
48    } catch (error) {
49      console.error('Full-text search configuration error:', error);
50    } finally {
51      await queryRunner.release();
52    }
53  }

coalesce() is a fix: concatenating text with NULL gives NULL, so without it 21 tributes with no description dropped out of the search in the test. Since PostgreSQL 12 you can use a generated column instead of a trigger, and TypeORM 1.x declares a GIN index in the entity with @Index({ type: 'gin' }).

The search itself sorts results by relevance:

1  async searchTributes(searchTerm: string): Promise<any[]> {
2    // PostgreSQL full-text search with ranking
3    const results = await this.dataSource.query(`
4      SELECT
5        t.*,
6        ts_rank(search_vector, plainto_tsquery('english', $1)) as rank
7      FROM tributes t
8      WHERE search_vector @@ plainto_tsquery('english', $1)
9      ORDER BY rank DESC, value DESC
10      LIMIT 50
11    `, [searchTerm]);
12
13    return results;
14  }

plainto_tsquery() turns plain words into a query, the @@ operator checks the match, and ts_rank() computes relevance. The user's text goes in as the $1 parameter, never through concatenation.

The QueryBuilder version combines text with filters:

1  async advancedSearch(query: {
2    text?: string;
3    type?: string;
4    minValue?: number;
5    location?: string;
6    dateRange?: { start: Date; end: Date };
7  }): Promise<{ entities: Tribute[]; raw: any[] }> {
8    const qb = this.tributeRepo.createQueryBuilder('tribute');
9
10    // Full-text search condition
11    if (query.text) {
12      qb.addSelect(`ts_rank(tribute.search_vector, plainto_tsquery('english', :searchText))`, 'rank')
13        .where("tribute.search_vector @@ plainto_tsquery('english', :searchText)", {
14          searchText: query.text
15        });
16    }
17
18    // Filters on indexed columns
19    if (query.type) {
20      qb.andWhere('tribute.type = :type', { type: query.type });
21    }
22
23    if (query.minValue) {
24      qb.andWhere('tribute.value >= :minValue', { minValue: query.minValue });
25    }
26
27    // LIKE with a leading % cannot use an ordinary B-tree index
28    if (query.location) {
29      qb.andWhere('tribute.location LIKE :location', {
30        location: `%${query.location}%`
31      });
32    }
33
34    if (query.dateRange) {
35      qb.andWhere('tribute.collectedAt BETWEEN :start AND :end', {
36        start: query.dateRange.start,
37        end: query.dateRange.end
38      });
39    }
40
41    // Sorting: relevance first, then value
42    if (query.text) {
43      qb.orderBy('rank', 'DESC').addOrderBy('tribute.value', 'DESC');
44    } else {
45      qb.orderBy('tribute.value', 'DESC');
46    }
47
48    return await qb.limit(100).getRawAndEntities();
49  }
50}

The previous version had the condition in single quotes that the word 'english' cut in half, so the code did not compile - hence the double quotes. getRawAndEntities() returns an object { entities, raw }, and the relevance lives in raw.

Connection pool

Opening a database connection is expensive, so TypeORM keeps a pool of ready connections:

1// app.module.ts - the connection pool
2TypeOrmModule.forRoot({
3  type: 'postgres',
4  url: process.env.DATABASE_URL,
5  autoLoadEntities: true,
6  poolSize: 20, // maximum number of connections in the pool
7}),

By default the pg driver opens up to 10 connections. You tune the size to the number of concurrent users and the database's capacity: PostgreSQL accepts 100 connections by default, and five instances with a pool of 20 take all of them.

The optimization ladder

From the simplest to the most complex:

1. indexes on the most frequently searched columns - one line in the entity,
2. select instead of SELECT * - a change in the queries,
3. pagination and lazy loading - a change of the API contract,
4. read replicas and sharding - a change of infrastructure.

Climb to the next rung only when the previous one is not enough - I recommend that patience. In the next lesson we will spread traffic across several application instances.

Remember: an index is a map - without it you search every chest for a tribute, with it you know at once which one to open.

1// Database Indexing - Maps for Faster Searching
2// Indexes speed up database queries
3
4// 1. Indexes in Mongoose (MongoDB)
5import { Schema, Prop, SchemaFactory } from '@nestjs/mongoose';
6import { Document } from 'mongoose';
7
8@Schema()
9export class Soldier extends Document {
10  // Index on a single field
11  @Prop({ index: true })
12  rank: string;
13
14  @Prop()
15  name: string;
16
17  // Index on a field with uniqueness
18  @Prop({ unique: true })
19  registrationNumber: string;
20
21  // Index on a frequently searched field
22  @Prop({ index: true })
23  legionId: string;
24
25  @Prop()
26  province: string;
27
28  @Prop({ default: Date.now })
29  enrolledAt: Date;
30}
31
32export const SoldierSchema = SchemaFactory.createForClass(Soldier);
33
34// 2. Compound index
35SoldierSchema.index({ legionId: 1, rank: 1 });
36
37// 3. Text index (for full-text search)
38SoldierSchema.index({ name: 'text', province: 'text' });
39
40// 4. TTL index (automatic removal after a time)
41// SoldierSchema.index({ enrolledAt: 1 }, { expireAfterSeconds: 86400 });
42
43// 5. Query optimization
44
45// WITHOUT an index (Collection Scan - O(n)):
46// db.soldiers.find({ rank: 'centurion' })
47// Scans EVERY document in the collection!
48
49// WITH an index (Index Scan - O(log n)):
50// db.soldiers.find({ rank: 'centurion' })
51// Uses the index to find it quickly!
52
53// 6. Explain - query analysis
54// const result = await SoldierModel
55//   .find({ rank: 'centurion' })
56//   .explain('executionStats');
57// console.log(result.executionStats.totalDocsExamined);
58// console.log(result.executionStats.executionTimeMillis);
59
60// 7. Projection - select only the fields you need
61// SLOW (loads the whole document):
62// await SoldierModel.find({ legionId: '1' });
63//
64// FAST (loads only the needed fields):
65// await SoldierModel
66//   .find({ legionId: '1' })
67//   .select('name rank -_id');
68
69// 8. Indexing rules:
70// - Index fields used in WHERE/find
71// - Index fields used in ORDER BY/sort
72// - Don't index rarely searched fields
73// - Indexes take up memory - don't overdo it!
74// - Monitor performance with explain()
75