REST API Development
API Performance Optimization
Understanding API Performance
API performance directly impacts user experience, system scalability, and operational costs. Slow APIs lead to frustrated users, higher infrastructure costs, and reduced system capacity. This lesson covers essential techniques to identify performance bottlenecks and optimize API response times.
The N+1 Query Problem
The N+1 query problem is one of the most common performance killers in APIs. It occurs when you fetch a list of N items, then make an additional database query for each item to fetch related data, resulting in N+1 total queries.
Example of N+1 Problem
// BAD: N+1 Query Problem
app.get('/api/posts', async (req, res) => {
// 1 query to fetch all posts
const posts = await db.query('SELECT * FROM posts LIMIT 10');
// For each post, fetch the author (10 additional queries)
for (let post of posts) {
// N queries (one per post)
post.author = await db.query(
'SELECT * FROM users WHERE id = ?',
[post.user_id]
);
}
res.json(posts);
});
// Result: 1 + 10 = 11 database queries
// If you fetch 100 posts, you'll make 101 queries!
Performance Impact: With 100 posts and 50ms average query time, the N+1 problem results in 101 queries × 50ms = 5,050ms (5 seconds) total query time. This is unacceptable for API performance.
Solution 1: Eager Loading (JOIN)
// GOOD: Using SQL JOIN to fetch related data in one query
app.get('/api/posts', async (req, res) => {
const query = `
SELECT
posts.*,
users.id as author_id,
users.name as author_name,
users.email as author_email,
users.avatar as author_avatar
FROM posts
JOIN users ON posts.user_id = users.id
LIMIT 10
`;
const rows = await db.query(query);
// Transform flat rows into nested objects
const posts = rows.map(row => ({
id: row.id,
title: row.title,
content: row.content,
created_at: row.created_at,
author: {
id: row.author_id,
name: row.author_name,
email: row.author_email,
avatar: row.author_avatar
}
}));
res.json(posts);
});
// Result: Only 1 database query, regardless of number of posts
Solution 2: Data Loader Pattern (for GraphQL/complex scenarios)
// Using DataLoader to batch and cache database queries
const DataLoader = require('dataloader');
// Create a DataLoader for batching user queries
const userLoader = new DataLoader(async (userIds) => {
// This function receives an array of user IDs
// and returns users in the same order
const users = await db.query(
'SELECT * FROM users WHERE id IN (?)',
[userIds]
);
// Create a map for O(1) lookup
const userMap = {};
users.forEach(user => userMap[user.id] = user);
// Return users in the same order as requested IDs
return userIds.map(id => userMap[id] || null);
});
app.get('/api/posts', async (req, res) => {
// Fetch posts
const posts = await db.query('SELECT * FROM posts LIMIT 10');
// Load all authors at once (batched into single query)
const postsWithAuthors = await Promise.all(
posts.map(async (post) => ({
...post,
author: await userLoader.load(post.user_id)
}))
);
res.json(postsWithAuthors);
});
// Result: 2 queries total (1 for posts, 1 for all unique authors)
Best Practice: Always use eager loading (JOINs) or batching when fetching related data. Profile your API endpoints to identify N+1 problems using database query logs or APM tools.
Database Indexing for APIs
Proper database indexing is crucial for API performance. Indexes speed up data retrieval but slow down writes, so strategic indexing is essential.
When to Add Indexes
- Foreign key columns (user_id, post_id, etc.)
- Columns frequently used in WHERE clauses
- Columns used in JOIN conditions
- Columns used in ORDER BY clauses
- Columns used for filtering/searching
Index Examples
-- Single column index for user lookups
CREATE INDEX idx_posts_user_id ON posts(user_id);
-- Composite index for filtering by user and status
CREATE INDEX idx_posts_user_status ON posts(user_id, status);
-- Index for search functionality
CREATE INDEX idx_posts_title ON posts(title);
-- Full-text index for content search
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);
-- Index for date-based queries
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
-- Unique index for email lookups
CREATE UNIQUE INDEX idx_users_email ON users(email);
Analyzing Query Performance
-- Check if query uses indexes (MySQL)
EXPLAIN SELECT * FROM posts WHERE user_id = 123;
-- Example output:
-- +----+-------------+-------+------+------------------+---------+---------+-------+------+-------+
-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-- +----+-------------+-------+------+------------------+---------+---------+-------+------+-------+
-- | 1 | SIMPLE | posts | ref | idx_posts_user_id| idx_... | 4 | const | 10 | NULL |
-- +----+-------------+-------+------+------------------+---------+---------+-------+------+-------+
-- type = 'ref' is good (using index)
-- type = 'ALL' is bad (full table scan)
-- PostgreSQL version
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 123;
// Monitor slow queries in your API
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'myapp',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
// Wrapper to log slow queries
async function executeQuery(sql, params) {
const startTime = Date.now();
try {
const [rows] = await pool.execute(sql, params);
const duration = Date.now() - startTime;
// Log queries taking longer than 100ms
if (duration > 100) {
console.warn(`Slow query (${duration}ms): ${sql}`, params);
}
return rows;
} catch (error) {
console.error('Query error:', sql, params, error);
throw error;
}
}
Index Strategies:
- Composite indexes: Order matters! Put the most selective column first
- Covering indexes: Include all columns needed in the query to avoid table lookups
- Index cardinality: High cardinality (many unique values) = good for indexing
- Index maintenance: Regularly analyze and optimize indexes; remove unused ones
Response Compression
Compressing API responses reduces bandwidth usage and improves response times, especially for clients on slow networks.
Implementing Gzip Compression in Express.js
const express = require('express');
const compression = require('compression');
const app = express();
// Enable gzip compression for all responses
app.use(compression({
// Compression level (0-9, higher = better compression but slower)
level: 6,
// Minimum response size to compress (bytes)
threshold: 1024, // Only compress responses > 1KB
// Filter which responses to compress
filter: (req, res) => {
// Don't compress responses with this request header
if (req.headers['x-no-compression']) {
return false;
}
// Use compression filter function
return compression.filter(req, res);
}
}));
app.get('/api/posts', async (req, res) => {
const posts = await fetchPosts();
res.json(posts);
// Response is automatically compressed if > 1KB
});
Compression Best Practices
// Custom compression configuration
const compression = require('compression');
app.use(compression({
level: 6,
threshold: 1024,
filter: (req, res) => {
const contentType = res.getHeader('Content-Type');
// Compress JSON and text responses
if (contentType && (
contentType.includes('application/json') ||
contentType.includes('text/')
)) {
return true;
}
// Don't compress images (already compressed)
if (contentType && contentType.includes('image/')) {
return false;
}
return compression.filter(req, res);
}
}));
// Measure compression savings
app.use((req, res, next) => {
const oldWrite = res.write;
const oldEnd = res.end;
const chunks = [];
res.write = function(chunk) {
chunks.push(Buffer.from(chunk));
return oldWrite.apply(res, arguments);
};
res.end = function(chunk) {
if (chunk) {
chunks.push(Buffer.from(chunk));
}
const uncompressedSize = Buffer.concat(chunks).length;
const compressedSize = parseInt(res.getHeader('Content-Length') || 0);
if (compressedSize > 0) {
const savings = ((1 - compressedSize / uncompressedSize) * 100).toFixed(1);
console.log(`Compression: ${uncompressedSize}B → ${compressedSize}B (${savings}% saved)`);
}
return oldEnd.apply(res, arguments);
};
next();
});
Compression Results: Typical compression ratios for JSON:
- JSON with repetitive data: 70-90% reduction
- Average JSON responses: 60-70% reduction
- Small responses (<1KB): Compression overhead not worth it
Response Caching Strategies
Caching is one of the most effective ways to improve API performance by avoiding redundant computations and database queries.
HTTP Cache Headers
// Set cache headers for static data
app.get('/api/countries', (req, res) => {
// Cache for 1 hour (3600 seconds)
res.set({
'Cache-Control': 'public, max-age=3600',
'Expires': new Date(Date.now() + 3600000).toUTCString()
});
res.json(countries);
});
// ETags for conditional requests
app.get('/api/posts/:id', async (req, res) => {
const post = await fetchPost(req.params.id);
// Generate ETag from post data
const etag = generateETag(post);
res.set('ETag', etag);
// Check if client has cached version
if (req.headers['if-none-match'] === etag) {
return res.status(304).end(); // Not Modified
}
res.json(post);
});
function generateETag(data) {
const crypto = require('crypto');
return crypto
.createHash('md5')
.update(JSON.stringify(data))
.digest('hex');
}
Redis Caching Layer
const Redis = require('ioredis');
const redis = new Redis({
host: 'localhost',
port: 6379,
maxRetriesPerRequest: 3
});
// Cache middleware
function cacheMiddleware(duration) {
return async (req, res, next) => {
// Generate cache key from URL and query params
const cacheKey = `cache:${req.originalUrl}`;
try {
// Check cache
const cachedData = await redis.get(cacheKey);
if (cachedData) {
console.log('Cache hit:', cacheKey);
res.set('X-Cache', 'HIT');
return res.json(JSON.parse(cachedData));
}
console.log('Cache miss:', cacheKey);
res.set('X-Cache', 'MISS');
// Override res.json to cache response
const originalJson = res.json.bind(res);
res.json = function(data) {
// Cache the response
redis.setex(cacheKey, duration, JSON.stringify(data))
.catch(err => console.error('Cache set error:', err));
return originalJson(data);
};
next();
} catch (error) {
console.error('Cache error:', error);
next(); // Continue without cache on error
}
};
}
// Usage: Cache posts for 5 minutes (300 seconds)
app.get('/api/posts', cacheMiddleware(300), async (req, res) => {
const posts = await fetchPosts();
res.json(posts);
});
// Invalidate cache when data changes
app.post('/api/posts', async (req, res) => {
const post = await createPost(req.body);
// Clear related caches
await redis.del('cache:/api/posts');
await redis.del(`cache:/api/posts/${post.id}`);
res.status(201).json(post);
});
Advanced Cache Invalidation
// Tag-based cache invalidation
class CacheManager {
constructor(redis) {
this.redis = redis;
}
// Store data with tags
async set(key, data, ttl, tags = []) {
const cacheKey = `cache:${key}`;
// Store the data
await this.redis.setex(cacheKey, ttl, JSON.stringify(data));
// Associate tags with this key
for (const tag of tags) {
await this.redis.sadd(`tag:${tag}`, cacheKey);
}
}
// Get cached data
async get(key) {
const cacheKey = `cache:${key}`;
const data = await this.redis.get(cacheKey);
return data ? JSON.parse(data) : null;
}
// Invalidate by tag
async invalidateTag(tag) {
const tagKey = `tag:${tag}`;
// Get all keys with this tag
const keys = await this.redis.smembers(tagKey);
if (keys.length > 0) {
// Delete all keys
await this.redis.del(...keys);
// Delete the tag set
await this.redis.del(tagKey);
}
console.log(`Invalidated ${keys.length} cache entries for tag: ${tag}`);
}
}
const cacheManager = new CacheManager(redis);
// Cache with tags
app.get('/api/posts', async (req, res) => {
const cacheKey = 'posts:list';
const cached = await cacheManager.get(cacheKey);
if (cached) {
return res.json(cached);
}
const posts = await fetchPosts();
// Cache with tags
await cacheManager.set(cacheKey, posts, 300, ['posts', 'listings']);
res.json(posts);
});
// Invalidate all post-related caches
app.post('/api/posts', async (req, res) => {
const post = await createPost(req.body);
// Invalidate all caches tagged with 'posts'
await cacheManager.invalidateTag('posts');
res.status(201).json(post);
});
Cache Invalidation Challenges: Cache invalidation is notoriously difficult. Be conservative with cache TTLs for frequently changing data. Consider using shorter TTLs with background refresh for better user experience.
Database Connection Pooling
Connection pooling reuses database connections instead of creating new ones for each request, significantly improving performance.
// MySQL connection pooling
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'myapp',
// Pool configuration
waitForConnections: true, // Wait if no connections available
connectionLimit: 10, // Maximum 10 concurrent connections
maxIdle: 10, // Maximum idle connections
idleTimeout: 60000, // Close idle connections after 1 minute
queueLimit: 0, // No limit on queued requests
enableKeepAlive: true, // Keep connections alive
keepAliveInitialDelay: 0
});
// Middleware to add pool to req object
app.use((req, res, next) => {
req.db = pool;
next();
});
// Use pooled connections
app.get('/api/posts', async (req, res) => {
try {
const [rows] = await req.db.query('SELECT * FROM posts LIMIT 10');
res.json(rows);
} catch (error) {
console.error('Database error:', error);
res.status(500).json({ error: 'Database error' });
}
});
// Monitor pool statistics
setInterval(() => {
console.log('Connection pool stats:', {
total: pool.pool._allConnections.length,
active: pool.pool._allConnections.length - pool.pool._freeConnections.length,
idle: pool.pool._freeConnections.length
});
}, 30000); // Every 30 seconds
Pagination and Limiting Results
Always paginate large result sets to avoid memory issues and improve response times.
// Efficient pagination with limit/offset
app.get('/api/posts', async (req, res) => {
const page = parseInt(req.query.page) || 1;
const limit = Math.min(parseInt(req.query.limit) || 10, 100); // Max 100 per page
const offset = (page - 1) * limit;
try {
// Get total count
const [countResult] = await db.query('SELECT COUNT(*) as total FROM posts');
const total = countResult[0].total;
// Get paginated data
const [posts] = await db.query(
'SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?',
[limit, offset]
);
res.json({
data: posts,
pagination: {
page,
limit,
total,
totalPages: Math.ceil(total / limit),
hasNext: page * limit < total,
hasPrev: page > 1
}
});
} catch (error) {
res.status(500).json({ error: 'Database error' });
}
});
Cursor-based Pagination (for large datasets)
// More efficient for large datasets
app.get('/api/posts', async (req, res) => {
const limit = Math.min(parseInt(req.query.limit) || 10, 100);
const cursor = req.query.cursor; // Last item's ID from previous page
let query;
let params;
if (cursor) {
// Fetch items after cursor
query = 'SELECT * FROM posts WHERE id < ? ORDER BY id DESC LIMIT ?';
params = [cursor, limit + 1]; // Fetch one extra to check if there's a next page
} else {
// First page
query = 'SELECT * FROM posts ORDER BY id DESC LIMIT ?';
params = [limit + 1];
}
const [posts] = await db.query(query, params);
const hasNext = posts.length > limit;
const data = hasNext ? posts.slice(0, -1) : posts;
res.json({
data,
pagination: {
limit,
nextCursor: hasNext ? data[data.length - 1].id : null,
hasNext
}
});
});
Pagination Performance: Cursor-based pagination performs better than offset-based for large datasets because it doesn't require counting all rows and uses indexed columns (typically primary key) for efficient queries.
Async Processing for Heavy Operations
Move time-consuming tasks to background jobs to keep API responses fast.
// Using Bull queue for background jobs
const Queue = require('bull');
const emailQueue = new Queue('email', 'redis://localhost:6379');
// API endpoint returns immediately
app.post('/api/users', async (req, res) => {
const user = await createUser(req.body);
// Queue welcome email (processed in background)
await emailQueue.add({
type: 'welcome',
userId: user.id,
email: user.email
});
res.status(201).json(user);
});
// Background worker processes jobs
emailQueue.process(async (job) => {
const { type, userId, email } = job.data;
if (type === 'welcome') {
await sendWelcomeEmail(email);
console.log(`Welcome email sent to ${email}`);
}
});
Exercise: Optimize a slow API endpoint with the following characteristics:
- Endpoint:
GET /api/users/:id/dashboard - Current response time: 3.5 seconds
- Fetches: user profile, recent orders (last 10), order statistics, product recommendations
- Has N+1 query problem when loading order items
- No caching implemented
- No database indexes on foreign keys
- Returns 500KB uncompressed JSON
- Fix the N+1 query problem using JOINs or eager loading
- Add appropriate database indexes (user_id, order_id, created_at)
- Implement Redis caching with 5-minute TTL
- Enable gzip compression
- Move recommendation generation to background job
- Add response time logging