Database Performance Crisis: The 2.3 Second Problem
Six months into LitReview-AI's growth, our database became the bottleneck. Users reported 2-3 second delays during literature searches, and our analytics showed average query times of 2.3 seconds. We needed to optimize our PostgreSQL database without rewriting our entire application.
Performance Analysis: Finding the Bottlenecks
Initial Performance Metrics
| Metric | Value | Target |
|---|---|---|
| Average query time | 2.3s | < 500ms |
| Database connections | 85% pool usage | < 70% |
| Query throughput | 45 queries/sec | 100+ queries/sec |
| Index hit rate | 76% | > 95% |
Query Analysis with EXPLAIN ANALYZE
-- Problem query: Literature search with filters
EXPLAIN ANALYZE
SELECT a.*, u.email, u.profile->>'firstName' as author_name
FROM analyses a
JOIN users u ON a.user_id = u.id
WHERE a.status = 'completed'
AND (a.title ILIKE '%machine learning%' OR a.content ILIKE '%machine learning%')
AND a.created_at >= '2024-01-01'
ORDER BY a.created_at DESC
LIMIT 20;
-- Result: Seq Scan on analyses (cost=0.00..18425.00 rows=1 width=1024)
-- Actual time: 2340.123..2345.456 rows=20 loops=1
Problems identified:
- Sequential scan on analyses table
- ILIKE operations preventing index usage
- Missing composite indexes
- Inefficient join operations
Optimization Strategy 1: Smart Indexing
Problematic Query Patterns
-- ❌ Full table scan queries
SELECT * FROM analyses WHERE title ILIKE '%keyword%';
SELECT * FROM analyses WHERE content LIKE '%search term%';
SELECT * FROM analyses WHERE EXTRACT(MONTH FROM created_at) = 1;
Solution: Multi-column Indexes with GIN
-- ✅ Optimized indexing strategy
-- 1. GIN index for text search
CREATE INDEX idx_analyses_title_gin ON analyses USING GIN (to_tsvector('english', title));
CREATE INDEX idx_analyses_content_gin ON analyses USING GIN (to_tsvector('english', content));
-- 2. Composite indexes for common query patterns
CREATE INDEX idx_analyses_status_created ON analyses(status, created_at DESC);
CREATE INDEX idx_analyses_user_status ON analyses(user_id, status, created_at DESC);
-- 3. Partial indexes for filtered queries
CREATE INDEX idx_analyses_completed ON analyses(created_at DESC) WHERE status = 'completed';
CREATE INDEX idx_analyses_active ON analyses(user_id, updated_at DESC) WHERE status IN ('processing', 'pending');
Text Search Optimization
-- ✅ Full-text search implementation
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Add trigram index for partial matches
CREATE INDEX idx_analyses_title_trgm ON analyses USING GIN (title gin_trgm_ops);
CREATE INDEX idx_analyses_content_trgm ON analyses USING GIN (content gin_trgm_ops);
-- Optimized search query
SELECT a.*, u.email
FROM analyses a
JOIN users u ON a.user_id = u.id
WHERE
a.status = 'completed'
AND (
to_tsvector('english', a.title || ' ' || a.content) @@ plainto_tsquery('english', 'machine & learning')
OR a.title ILIKE '%machine learning%' -- Fallback for partial matches
)
AND a.created_at >= '2024-01-01'
ORDER BY a.created_at DESC
LIMIT 20;
-- Result: Index Scan using idx_analyses_status_created (cost=0.42..8.24 rows=1 width=1024)
-- Actual time: 12.456..15.789 rows=20 loops=1
Performance improvement: Query time reduced from 2.3s to 450ms (80% improvement).
Optimization Strategy 2: Query Rewriting
Inefficient Subquery Patterns
-- ❌ Subquery in WHERE clause (N+1 problem)
SELECT * FROM analyses
WHERE user_id IN (SELECT id FROM users WHERE institution = 'MIT University');
-- ✅ JOIN instead of subquery
SELECT a.* FROM analyses a
JOIN users u ON a.user_id = u.id
WHERE u.institution = 'MIT University';
Window Function Optimization
-- ❌ Multiple queries for ranking
SELECT *,
(SELECT COUNT(*) FROM analyses a2 WHERE a2.user_id = a.user_id) as total_analyses
FROM analyses a;
-- ✅ Window function for ranking
SELECT a.*,
COUNT(*) OVER (PARTITION BY a.user_id) as total_analyses,
ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY a.created_at DESC) as ranking
FROM analyses a;
CTE (Common Table Expression) Optimization
-- ✅ Complex query分解为CTEs
WITH user_analyses AS (
SELECT
a.id,
a.title,
a.created_at,
u.email,
ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY a.created_at DESC) as user_rank
FROM analyses a
JOIN users u ON a.user_id = u.id
WHERE a.status = 'completed'
),
latest_analyses AS (
SELECT * FROM user_analyses WHERE user_rank = 1
)
SELECT * FROM latest_analyses
ORDER BY created_at DESC
LIMIT 20;
Optimization Strategy 3: Connection Pooling
Connection Pool Configuration
// ✅ Optimized connection pool
import { Pool } from 'pg';
const pool = new Pool({
host: process.env.DB_HOST,
port: parseInt(process.env.DB_PORT || '5432'),
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
// Connection pool settings
max: 20, // Maximum connections
min: 5, // Minimum connections
idleTimeoutMillis: 30000, // Close idle connections after 30s
connectionTimeoutMillis: 10000, // Connection timeout
statement_timeout: 30000, // Query timeout
// Performance optimizations
application_name: 'litreview-ai',
statement_timeout: 15000,
query_timeout: 10000,
});
// Connection monitoring
pool.on('connect', (client) => {
console.log('New database connection established');
});
pool.on('error', (err, client) => {
console.error('Database connection error:', err);
});
Prepared Statements
// ✅ Prepared statement for repeated queries
class AnalysisRepository {
private readonly findByUserQuery: string;
private readonly findByUserStmt: Promise<any>;
constructor(private readonly pool: Pool) {
this.findByUserQuery = `
SELECT a.*, u.email, u.profile->>'firstName' as author_name
FROM analyses a
JOIN users u ON a.user_id = u.id
WHERE a.user_id = $1
AND a.status = $2
ORDER BY a.created_at DESC
LIMIT $3
`;
this.findByUserStmt = pool.prepare(this.findByUserQuery);
}
async findByUser(userId: string, status: string, limit: number): Promise<Analysis[]> {
const stmt = await this.findByUserStmt;
const result = await stmt.execute([userId, status, limit]);
return result.rows;
}
}
Optimization Strategy 4: Partitioning Large Tables
Table Partitioning Strategy
-- ✅ Partition large tables by date
CREATE TABLE analyses (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL,
content TEXT,
user_id UUID NOT NULL REFERENCES users(id),
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- Create partitions
CREATE TABLE analyses_2024_q1 PARTITION OF analyses
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE analyses_2024_q2 PARTITION OF analyses
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE analyses_2024_q3 PARTITION OF analyses
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE analyses_2024_q4 PARTITION OF analyses
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
-- Indexes on partitions
CREATE INDEX idx_analyses_2024_q1_status ON analyses_2024_q1(status, created_at);
CREATE INDEX idx_analyses_2024_q2_status ON analyses_2024_q2(status, created_at);
Automated Partition Management
// ✅ Automated partition creation
class PartitionManager {
constructor(private readonly pool: Pool) {}
async createNextQuarterPartition(): Promise<void> {
const nextQuarter = this.getNextQuarter();
const tableName = `analyses_${nextQuarter.year}_q${nextQuarter.quarter}`;
const startDate = this.getQuarterStart(nextQuarter);
const endDate = this.getQuarterEnd(nextQuarter);
const query = `
CREATE TABLE ${tableName} PARTITION OF analyses
FOR VALUES FROM ('${startDate}') TO ('${endDate}');
`;
await this.pool.query(query);
// Create indexes on new partition
await this.createPartitionIndexes(tableName);
}
private getNextQuarter(): { year: number; quarter: number } {
const now = new Date();
const year = now.getFullYear();
const month = now.getMonth() + 1;
const quarter = Math.ceil(month / 3);
if (quarter === 4) {
return { year: year + 1, quarter: 1 };
}
return { year, quarter: quarter + 1 };
}
}
Monitoring and Maintenance
Performance Monitoring Query
-- ✅ Database performance monitoring
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query LIKE '%analyses%'
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Index usage monitoring
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
AND tablename = 'analyses'
ORDER BY idx_scan DESC;
-- Table bloat monitoring
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS external_size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size
FROM pg_tables
WHERE schemaname = 'public'
AND tablename = 'analyses';
Automated Vacuum and Analyze
// ✅ Automated maintenance
class DatabaseMaintenance {
constructor(private readonly pool: Pool) {}
async performMaintenance(): Promise<void> {
// VACUUM ANALYZE for updated statistics
await this.pool.query('VACUUM ANALYZE analyses;');
await this.pool.query('VACUUM ANALYZE users;');
// Reindex fragmented indexes
await this.pool.query('REINDEX INDEX CONCURRENTLY idx_analyses_title_gin;');
await this.pool.query('REINDEX INDEX CONCURRENTLY idx_analyses_content_gin;');
// Update table statistics
await this.pool.query('ANALYZE analyses;');
await this.pool.query('ANALYZE users;');
}
async scheduleMaintenance(): Promise<void> {
// Run maintenance daily at 2 AM
cron.schedule('0 2 * * *', async () => {
console.log('Starting database maintenance...');
try {
await this.performMaintenance();
console.log('Database maintenance completed successfully');
} catch (error) {
console.error('Database maintenance failed:', error);
}
});
}
}
Results: Performance Improvements
Before vs After Optimization
| Metric | Before | After | Improvement |
|---|---|---|---|
| Average query time | 2.3s | 450ms | 80% ⬇️ |
| Database connections | 85% usage | 45% usage | 47% ⬇️ |
| Query throughput | 45 q/s | 120 q/s | 167% ⬆️ |
| Index hit rate | 76% | 97% | 28% ⬆️ |
| Memory usage | 8.2GB | 4.1GB | 50% ⬇️ |
User Experience Improvements
- Search response time: 2.8s → 0.6s
- Literature list loading: 3.1s → 0.8s
- User dashboard: 2.2s → 0.7s
- Analysis processing: 1.9s → 0.9s
Common Pitfalls and Solutions
Pitfall 1: Over-indexing
-- ❌ Too many indexes hurt write performance
CREATE INDEX idx_analyses_title ON analyses(title);
CREATE INDEX idx_analyses_content ON analyses(content);
CREATE INDEX idx_analyses_status ON analyses(status);
CREATE INDEX idx_analyses_user_id ON analyses(user_id);
CREATE INDEX idx_analyses_created_at ON analyses(created_at);
-- ✅ Strategic composite indexes
CREATE INDEX idx_analyses_user_status_created ON analyses(user_id, status, created_at DESC);
CREATE INDEX idx_analyses_status_created ON analyses(status, created_at DESC);
Pitfall 2: Ignoring Connection Pooling
// ❌ New connection for every query
async function getAnalysis(id: string): Promise<Analysis> {
const pool = new Pool(connectionConfig); // Creates new pool every time
const result = await pool.query('SELECT * FROM analyses WHERE id = $1', [id]);
await pool.end(); // Closes connection
return result.rows[0];
}
// ✅ Reuse connection pool
const pool = new Pool(connectionConfig); // Single pool instance
async function getAnalysisOptimized(id: string): Promise<Analysis> {
const result = await pool.query('SELECT * FROM analyses WHERE id = $1', [id]);
return result.rows[0];
}
Conclusion: Database Optimization is Continuous
Database optimization isn't a one-time fix—it's an ongoing process of monitoring, analyzing, and improving. Our 80% query time reduction came from:
- Strategic indexing based on actual query patterns
- Query rewriting to use efficient PostgreSQL features
- Connection pooling to manage database resources
- Regular maintenance to keep statistics current
💡 Final Advice: Monitor before optimizing, test after optimizing, and always measure the impact. Database optimization should be driven by real usage patterns, not assumptions.
This article covers real PostgreSQL optimization work that improved performance by 80% while maintaining data integrity and supporting thousands of concurrent users. All techniques have been tested in production with measurable improvements in user experience.