NestJS course ยท Module 8: Caching and Performance
Database Indexing - maps for faster searching
In this lesson9
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:
// tribute.entity.ts
import { Entity, Column, Index, PrimaryGeneratedColumn } from 'typeorm';
import type { Point } from 'typeorm';
@Entity('tributes')
@Index(['location', 'collectedAt']) // Composite index
@Index(['value'], { where: 'value > 1000' }) // Partial index
export class Tribute {
@PrimaryGeneratedColumn()
id: number;
@Column()
@Index() // Simple index on name
name: string;
@Column('decimal')
@Index('idx_tribute_value') // Named index
value: number;
@Column()
@Index() // Index on location for filtering by province
location: string;
@Column({ type: 'enum', enum: ['GOLD', 'SILVER', 'GEMS'] })
@Index() // Index on type for filtering
type: string;
@Column({ name: 'collected_at', type: 'timestamp' })
@Index() // Index on the timestamp for date ranges
collectedAt: Date;
@Column({ unique: true })
serialNumber: string; // Automatic unique index
@Column('text', { nullable: true })
description: string;
@Column({ type: 'geography', spatialFeatureType: 'Point', srid: 4326 })
@Index({ spatial: true }) // GiST index for geographic queries (PostGIS)
coordinates: Point;
}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:
// MySQL variant - a full-text index
@Column('text')
@Index({ fulltext: true })
description: 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:
// soldier.schema.ts - MongoDB (Mongoose)
@Schema()
export class Soldier {
@Prop({ index: true })
rank: string;
@Prop({ unique: true })
registrationNumber: string;
}
export const SoldierSchema = SchemaFactory.createForClass(Soldier);
SoldierSchema.index({ legionId: 1, enrolledAt: -1 }); // compound indexThe 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:
// indexing-strategy.service.ts
@Injectable()
export class IndexingStrategyService {
constructor(
@InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
@InjectDataSource() private dataSource: DataSource,
) {}
// Checking index usage
async analyzeIndexUsage(): Promise<any> {
const queries = [
// Check the query execution plan
`EXPLAIN SELECT * FROM tributes WHERE type = 'GOLD' AND value > 1000`,
`EXPLAIN SELECT * FROM tributes WHERE location LIKE 'Hispania%'`,
`EXPLAIN ANALYZE SELECT * FROM tributes WHERE collected_at BETWEEN '2023-01-01' AND '2023-12-31'`,
];
const results = [];
for (const query of queries) {
const plan = await this.dataSource.query(query);
results.push({ query, plan });
}
return results;
}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:
// Creating indexes dynamically
async createPerformanceIndexes(): Promise<void> {
const queryRunner = this.dataSource.createQueryRunner();
try {
// Index for frequent searches by value and type
await queryRunner.query(`
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_type_value
ON tributes(type, value DESC)
`);
// Partial index only for expensive tributes
await queryRunner.query(`
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_expensive_tributes
ON tributes(collected_at)
WHERE value > 10000
`);
// Composite index for queries by province and time
await queryRunner.query(`
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_location_time
ON tributes(location, collected_at)
`);
// Covering index (contains all the needed columns)
await queryRunner.query(`
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_summary
ON tributes(type, location)
INCLUDE (name, value, collected_at)
`);
console.log('Performance indexes created!');
} catch (error) {
console.error('Error while creating indexes:', error);
} finally {
await queryRunner.release();
}
}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:
// Optimizing queries that use indexes
async getOptimizedTributeSearches() {
// Uses the (type, value) index and fetches only the needed columns
const goldTributes = await this.tributeRepo
.createQueryBuilder('tribute')
.select(['tribute.id', 'tribute.name', 'tribute.value'])
.where('tribute.type = :type AND tribute.value > :minValue', {
type: 'GOLD',
minValue: 1000
})
.orderBy('tribute.value', 'DESC')
.limit(100)
.getMany();
// Uses the partial index on expensive tributes
const expensiveTributes = await this.tributeRepo
.createQueryBuilder('tribute')
.where('tribute.value > 10000')
.andWhere('tribute.collectedAt > :date', {
date: new Date('2023-01-01')
})
.getMany();
// Uses the spatial index: tributes within 1000 m of the Forum Romanum
const nearbyTributes = await this.dataSource.query(`
SELECT * FROM tributes
WHERE ST_DWithin(
coordinates,
ST_GeogFromText('SRID=4326;POINT(12.4853 41.8925)'),
1000
)
`);
return { goldTributes, expensiveTributes, nearbyTributes };
}
}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:
- find slow queries in the slow query log (in PostgreSQL:
log_min_duration_statement), - analyse the plan with
EXPLAIN, - add an index or rewrite the query,
- 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:
// full-text-search.service.ts
@Injectable()
export class FullTextSearchService {
constructor(
@InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
@InjectDataSource() private dataSource: DataSource,
) {}
async setupFullTextSearch(): Promise<void> {
const queryRunner = this.dataSource.createQueryRunner();
try {
// PostgreSQL full-text search
await queryRunner.query(`
ALTER TABLE tributes
ADD COLUMN IF NOT EXISTS search_vector tsvector
`);
await queryRunner.query(`
UPDATE tributes
SET search_vector = to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
`);
await queryRunner.query(`
CREATE INDEX IF NOT EXISTS idx_tribute_search
ON tributes USING gin(search_vector)
`);
// Trigger for automatic updates
await queryRunner.query(`
CREATE OR REPLACE FUNCTION update_tribute_search_vector()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector := to_tsvector('english', coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, ''));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
`);
await queryRunner.query(`
DROP TRIGGER IF EXISTS tribute_search_vector_update ON tributes;
CREATE TRIGGER tribute_search_vector_update
BEFORE INSERT OR UPDATE ON tributes
FOR EACH ROW EXECUTE FUNCTION update_tribute_search_vector();
`);
console.log('Full-text search configured!');
} catch (error) {
console.error('Full-text search configuration error:', error);
} finally {
await queryRunner.release();
}
}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:
async searchTributes(searchTerm: string): Promise<any[]> {
// PostgreSQL full-text search with ranking
const results = await this.dataSource.query(`
SELECT
t.*,
ts_rank(search_vector, plainto_tsquery('english', $1)) as rank
FROM tributes t
WHERE search_vector @@ plainto_tsquery('english', $1)
ORDER BY rank DESC, value DESC
LIMIT 50
`, [searchTerm]);
return results;
}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:
async advancedSearch(query: {
text?: string;
type?: string;
minValue?: number;
location?: string;
dateRange?: { start: Date; end: Date };
}): Promise<{ entities: Tribute[]; raw: any[] }> {
const qb = this.tributeRepo.createQueryBuilder('tribute');
// Full-text search condition
if (query.text) {
qb.addSelect(`ts_rank(tribute.search_vector, plainto_tsquery('english', :searchText))`, 'rank')
.where("tribute.search_vector @@ plainto_tsquery('english', :searchText)", {
searchText: query.text
});
}
// Filters on indexed columns
if (query.type) {
qb.andWhere('tribute.type = :type', { type: query.type });
}
if (query.minValue) {
qb.andWhere('tribute.value >= :minValue', { minValue: query.minValue });
}
// LIKE with a leading % cannot use an ordinary B-tree index
if (query.location) {
qb.andWhere('tribute.location LIKE :location', {
location: `%${query.location}%`
});
}
if (query.dateRange) {
qb.andWhere('tribute.collectedAt BETWEEN :start AND :end', {
start: query.dateRange.start,
end: query.dateRange.end
});
}
// Sorting: relevance first, then value
if (query.text) {
qb.orderBy('rank', 'DESC').addOrderBy('tribute.value', 'DESC');
} else {
qb.orderBy('tribute.value', 'DESC');
}
return await qb.limit(100).getRawAndEntities();
}
}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:
// app.module.ts - the connection pool
TypeOrmModule.forRoot({
type: 'postgres',
url: process.env.DATABASE_URL,
autoLoadEntities: true,
poolSize: 20, // maximum number of connections in the pool
}),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:
- indexes on the most frequently searched columns - one line in the entity,
selectinstead ofSELECT *- a change in the queries,- pagination and lazy loading - a change of the API contract,
- 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.
Code for this lesson: src/database-indexing.ts
// Database Indexing - Maps for Faster Searching
// Indexes speed up database queries
// 1. Indexes in Mongoose (MongoDB)
import { Schema, Prop, SchemaFactory } from '@nestjs/mongoose';
import { Document } from 'mongoose';
@Schema()
export class Soldier extends Document {
// Index on a single field
@Prop({ index: true })
rank: string;
@Prop()
name: string;
// Index on a field with uniqueness
@Prop({ unique: true })
registrationNumber: string;
// Index on a frequently searched field
@Prop({ index: true })
legionId: string;
@Prop()
province: string;
@Prop({ default: Date.now })
enrolledAt: Date;
}
export const SoldierSchema = SchemaFactory.createForClass(Soldier);
// 2. Compound index
SoldierSchema.index({ legionId: 1, rank: 1 });
// 3. Text index (for full-text search)
SoldierSchema.index({ name: 'text', province: 'text' });
// 4. TTL index (automatic removal after a time)
// SoldierSchema.index({ enrolledAt: 1 }, { expireAfterSeconds: 86400 });
// 5. Query optimization
// WITHOUT an index (Collection Scan - O(n)):
// db.soldiers.find({ rank: 'centurion' })
// Scans EVERY document in the collection!
// WITH an index (Index Scan - O(log n)):
// db.soldiers.find({ rank: 'centurion' })
// Uses the index to find it quickly!
// 6. Explain - query analysis
// const result = await SoldierModel
// .find({ rank: 'centurion' })
// .explain('executionStats');
// console.log(result.executionStats.totalDocsExamined);
// console.log(result.executionStats.executionTimeMillis);
// 7. Projection - select only the fields you need
// SLOW (loads the whole document):
// await SoldierModel.find({ legionId: '1' });
//
// FAST (loads only the needed fields):
// await SoldierModel
// .find({ legionId: '1' })
// .select('name rank -_id');
// 8. Indexing rules:
// - Index fields used in WHERE/find
// - Index fields used in ORDER BY/sort
// - Don't index rarely searched fields
// - Indexes take up memory - don't overdo it!
// - Monitor performance with explain()
Docs entries used in this lesson
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. Database indexes:
2. Connection pool size in a database should:
Hands-on tasks in the game
- Code editor
Add indexes and use select() instead of fetching all columns
- Click in order
Arrange the database query optimization process:
- Vertical ordering
Arrange techniques from simplest to most complex DB optimization: